Objective: To learn what a join is, why a join is used, and how to compile a SELECT statement using the join statement. Join: One of the most powerful functions of SQL is to join tables in the execution of data search and query. Relational Tables: the design of Relational Tables is to ensure that information is divided into multiple tables, one type of data is a table. Each table is associated with each other through some common values (relational in relational design. A foreign key (foreign key) is a column in a table. It contains the primary key values of zero tables and defines the relationship between two tables. Scalability can adapt to the increasing workload without failure. Well-designed databases or applications are called good scalability ). Why join: To solve this problem, use a single SELECT statement to retrieve data stored in multiple tables. Maintain reference integrity: the join is not a physical entity and does not exist in the actual database table. The connection is established by MySQL as needed and exists in the execution of the query. When using a relational table, it is very important to insert valid data only in the relational column. If a product is inserted into the Products table that has an invalid supplier ID (that is, it is not displayed in the venders table), these products cannot be accessed because they are not associated with a vendor. To prevent this, You can instruct MySQL to only allow valid values (and vendors in the venders table) in the supplier ID column of the Products table ). This is to maintain the integrity of the reference, which is achieved by specifying the primary key and foreign key in the table definition. Create a join: it is very simple to create a join, specifying all the tables to join and how they are associated. Select vend_name, prod_name, prod_pricefrom vendors, productswhere vendors. vend_id = products. vend_id (fully qualified column name) order by vend_name, prod_name; Importance of the WHERE clause: The WHERE clause acts as a filter condition to indicate how MySQL joins tables. Cartesian Product: The result returned by the table relation without join conditions is Cartesian product. The number of rows retrieved is the number of rows in the first table multiplied by the number of rows in the second table. Select vend_name, prod_name, prod_pricefrom vendors, productsorder by vend_name, prod_name; Do not forget the wnere clause. Ensure that all joins have a where clause, otherwise, MySQL returns much more data than the expected data. Similarly, the correctness of the WHERE clause should be ensured. Incorrect filtering conditions will cause MySQL to return incorrect data. Cross join: The join used so far is called equijoin. It is based on the equality test between two tables. This kind of join is also called an internal join. Select vend_name, prod_name, prod_pricefrom vendors inner join productson vendors. vend_id = products. vend_id; the inner join syntax is preferred for ansi SQL specifications. Join multiple tables: SQL has no limit on the number of tables that can be joined in a select statement. The basic rules for creating connections are the same. Q: an item in an order numbered 20005 is displayed. Select 'prod _ name', vend_name, prod_price, quantityfrom orderitems, products, vendorswhere products. vend_id = vendors. vend_idand orderitems. 'prod _ id' = products. 'prod _ id' and order_num = 20005; performance considerations: MySQL associates a specified table at runtime to process joins. This kind of processing may be very resource-consuming, so be careful not to join unnecessary tables. The more joined tables, the worse the performance. # Q: return the list of customers who subscribe to the product tnt2. The writing level of the statement, from the outside to the inside. Select cust_name, cust_contactfrom MERs, orders, orderitemswhere customers. cust_id = orders. cust_idand orderitems. order_num = orders. order_numand prod_id = 'tnt2 ';
Chapter 4 join table
