MySQL must know reading notes-5 (junction of table)

Join operations are one of the most powerful features of the SQL language. This operation is based on the relational table.

The Relationship table

What is a relational table?

Suppose there is a product table, each product has its own attributes and suppliers, and a supplier may have multiple products.

So where does the vendor's information be stored? If stored directly in the product table, assuming that a supplier has 1000 products,

In the Product table, the supplier's information will be stored 1000 times, the supplier to change the name of the table to change 1000 times,

This is not only slow, but also error-prone.

The key is that the same data appearing multiple times in a table is not a good thing (at least a waste of space). At this point, the role of the relational table

Was revealed. We can build two tables, supplier tables and product tables. Each vendor has a unique ID,

Product table Each product contains a supplier ID, two tables can be linked by an ID, that is, each product can be

Its vendor ID to find the corresponding vendor.

The supplier's ID is the primary key, and it is also a foreign key to the product table, which associates the product table with the Vendor table.

There are many advantages to this: there is no duplication of information, and if supplier information changes, only one of the suppliers ' tables needs to be modified.

Information can be.

2--Coupling (inner coupling)

In the example above, when we need to retrieve the properties of a product and its supplier's properties, we need two tables of information

The connection is then connected.

Create a junction

SELECT Vend_name, Prod_name, Prod_price  from Vendors, products WHERE = products.vend_id

If there is no WHERE clause (that is, there is no join condition), the result of the output will be the Cartesian product of two tables. The number of output results will be the first one

The number of rows in a table multiplied by the number of rows in the second table.

Internal coupling

The above example uses an equivalent junction, also known as an internal junction.

SELECT Vend_name, Prod_name, Prod_price  from INNER JOIN  Products = products.vend_id

3--Referential integrity

4--joins multiple tables

Basic information and vendor information for items in the order numbered 20005 are displayed

SELECT Prod_name, Prod_price, vend_name, quantity  from OrderItems, products, vendors WHERE = 20005        and = products.prod_id        and = vendors.vend_id

Return the information of the customer who ordered the TNT2

SELECT Customers.cust_name, customers.cust_contact, orderitems.quantity  from customers, orders, OrderItems WHERE = ' TNT2 '       and = Orderitems.order_num        and = orders.cust_id

5--aliases for using tables

Using aliases for the previous example can shorten the code length and result in the same

SELECT C.cust_name, c.cust_contact, oi.quantity  from  as  as  as Oi WHERE = ' TNT2 '       and = Oi.order_num        and = o.cust_id

6--Self-coupling

Identify other products produced by a supplier of a product

1. Using sub-queries

Select  from ProductsWHERE=  (                  Select  vend_id                 fromproductsWHERE='dtntr                    '                )

2. Using a self-junction

SELECT p1.prod_id, P1.prod_name  from  as  as P2 WHERE = p2.vend_id    and = ' dtntr '

7--external coupling

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

Retrieving all customers and their orders

1. Use inner coupling

SELECT Cust_name, Order_num  from  as INNER JOIN  as o  on = o.cust_id

Customers without orders are not listed

2. Using an outer junction

SELECT Cust_name, Order_num  from  as  Left OUTER JOIN  as o  on = o.cust_id

List of customers without orders

You must specify the right or left keyword when you use the outer join syntax

Left here indicates all rows in the Customer table

8--using a junction with aggregation functions

Retrieves the number of orders placed by all customers and each customer.

SELECT COUNT  as Num_ord  from  as  Left OUTER JOIN  as o  on = o.cust_id GROUP  by c.cust_id

9--Summary

MySQL must know reading notes-5 (junction of table)