MySQL must know reading notes chapters 15th and 16 junction tables

Why use junctions?

If the data is stored in more than one table, how can I retrieve the data using a single SELECT statement? The answer is to use junctions. Simply put, you can join multiple tables to return a set of outputs that join the correct rows in the associated table at run time.

1. Create a junction

SELECT Vend_name,prod_name,prod_price from Vendors,products WHERE vendors.vend_id=products.vend_id ORDER by Vend_name, Prod_name;

Note: You must use the fully qualified column name when ambiguity may appear in the Reference column.

The importance of 2.WHERE

It may seem strange to use a WHERE clause to establish a join relationship, but in fact, when you join several tables in a SELECT statement, the corresponding relationship is constructed in the run. There is nothing in the definition of a database table that indicates how MySQL can make table joins. You must do it yourself. When you join two tables, what you actually do is pair each row in the first table with each row in the second table. The WHERE clause is a filter that contains only the rows that match a given condition (here is the join condition). Without a WHERE clause, each row in the first table is paired with each row in the second table, regardless of whether they are logically matched together.

3. Cartesian product

The result of a table relationship without a join condition is a Cartesian product. The number of rows retrieved will be the number of rows in the first table * The number of rows in the second table.

SELECT Vend_name,prod_name,prod_price from Vendors,products ORDER by Vend_name,prod_name;

From the result: do not forget the WHERE clause, you should ensure that all junctions have a WHERE clause.

4. Internal coupling

The junctions used so far are called equivalent junctions. It is based on an equality test between two tables. This connection is also called an internal junction. In fact, for this kind of join you can use a slightly different syntax to explicitly specify the type of junction.

SELECT vend_name,prod_name,prod_price from vendors INNER joins products on vendors.vend_id=products_vend_id;

5. Joining multiple tables

SQL has no limit on the number of tables that can be joined in a SELECT statement. The basic rules for creating junctions are the same.

SELECT prod_name,vend_name,prod_price,quantity from Orderitems,products,vendors WHERE products.vend_id=vendors.vend _id

                                                                                                                                                                                and    orderitems.prod_id=products.prod_id

                                                                                                                                                                                and  order_num= ' 20005 '

Performance considerations: This processing can be very resource-intensive when MySQL associates each of the specified tables at run time to handle junctions. Sometimes it can be tuned in different ways.

For example, return to order product TNT2 customer list.

Select Cust_name,cust_contact from Customers where cust_id in (select cust_id from Orders WHERE order_num in (select O Rder_num from OrderItems WHERE prod_id= ' TNT2 '));

Actually, the sentences above are not the most effective.

SELECT cust_name,cust_contact from Customers,orders,orderitems WHERE customers.cust_id=orders.cust_id and OrderItems . Order_num=orders.order_num and prod_id= ' TNT12 '

6. Self-coupling

For example, knowing that there is a problem with an item with ID DTNTR, so you want to know if other items produced by the supplier that produced the item also exist.

Select prod_id, prod_name from Products where vend_id= (SELECT vend_id from Products where prod_id= ' dtntr ');

It is equivalent to the following statement

SELECT P1.prod_id,p1.prod_name from products as p1products as P2 WHERE p1.vend_id =p2.vend.id and p2.prod_id= ' Dntr ‘;

7. Natural coupling

SELECT c.*, O.reder_num, O.order_date,oi.prod_id,oi.quantity,oi.item_price from customers as c,orders as o,orderitems as Oi WHERE c.cust_id=o.cust_id and Oi.order_num=o.order_num and prod_id= ' FB ';

8. External coupling

Many junctions associate rows in one table with rows in another table. However, there are times when you need to include rows that have no associated rows. For example, you might need to use a junction to do the following:

1. Count the number of orders placed on each customer, including those who have not placed an order so far.

2. List all products and quantity ordered, including products that are not ordered by people.

3. Calculate the average consumption size, including those customers who have not yet placed orders.

In the above example, the junction contains rows that have no associated rows in the related table, and this type of join is called an outer junction.

Retrieve all customers and their orders:

SELECT customers.cust_id, orders.order_num from customers INNER JOIN orders on customers.cust_id=orders.cust_id;

The outer join syntax is similar. In order to retrieve all customers, including those without orders.

SELECT Customers.cust_id,orders.order_num from customers to OUTER JOIN orders on customers.cust_id=orders.cust_id;

Similar to the internal junction seen in the previous chapter, this SELECT statement uses the keyword outer JOIN to specify the type of the junction (not the one specified in the WHERE statement used). However, unlike a row in an inner junction that is associated with two tables, the outer join also includes rows that are not associated. When using the outer join syntax, you must use the rigth or left keyword to specify a table that includes all of its rows (right points to the table to the left of the outer join, and then to the table that the outer join is to).

Type of outer junction: There are two basic forms of outer junction: left Outer Junction and right Outer junction. The only difference between them is the order of the tables that are associated with each other. In other words, a left outer join can be converted to a right outer junction by reversing the order of the tables in the From or WHERE clause.

9. Differences between internal and external linkages

Generally speaking, the left connection, outer connection refers to the left outer connection, right outside the connection. Do a simple test, you see.        First say left outer connection and right outer connection: [test1@orcl#16-12 month -11] sql>select * from T1;        ID NAME------------------------------1 AAA 2 bbb[test1@orcl#16-12 Month -11] sql>select * from T2; ID Age--------------------1 20 3 30 LEFT outer connection: [[email protected] #16-December -11] Sql&gt        ; select * from T1 LEFT join T2 on T1.id=t2.id;                           ID NAME ID Age--------------------------------------------------1 AAA 1-2 BBB right outer connection: [[[Email protected] #16-December -11] Sql>select * from t1 R join T2 o        n T1.id=t2.id;                           ID NAME ID Age--------------------------------------------------1 AAA 1 20 3 30 from the above display you can see that the left outer join is based on the table on the left. Generally speaking, the left table is all displayed, and then the right table ID and the left table ID of the same record is "stitching" up, such as the record ID 1. If there is no matching ID, such as T1 in the ID 2 in the T2. There is a null display. Right outer join process just the opposite。        Look inside the connection again: [[[Email protected] #16-December -11] Sql>select * from t1 inner joins T2 on T1.id=t2.id;                           ID NAME ID Age--------------------------------------------------1 AAA 1 20 There is only one record. The inner connection is only to remove the records that meet the filter criteria, that is t1.id=t2.id. T1.id=t2.id records only id=1 this one, so only one is displayed. Unlike an outer join, all the rows that you use as a datum (the left outer join is the reference to the left table, the right outer join is the datum of the right table) are displayed.

MySQL must know reading notes chapters 15th and 16 junction tables