5. Connection query

Sometimes, you need to use a SELECT statement to query data in multiple tables. For this purpose, you can use join queries. Connection query is divided into inner connection, outer connection, cross connection and self connection. Inner connection and outer connection are common connections; The result of cross connection is a Cartesian product, which is a special case of inner connection and outer connection. The internal connection only displays the data that meets the connection conditions in multiple tables. The external connection not only displays the data that meets the conditions, but also displays all the data in a table.

1. Internal connection

The internal operation process of internal connection is to find the Cartesian product of multiple tables, and then output the rows that meet the connection conditions.

If a category table and a toy table are connected through the cCategoryID column, the Cartesian product of the two tables can be obtained by using the following statement

SELECT * FROM Toys, Category

Syntax format for establishing internal connections

SELECT Listing, Listing [, Listing]
FROM  Table name [INNER] JOIN Table name
ON Table name.Reference column name join operator table name.Reference column name


perhaps


SELECT Listing, Listing [, Listing]
FROM　Table name　
WHERE Table name.Reference column name = Table name.Reference column name

  If the column names in the SELECT list are replaced by *, all columns in all tables will be displayed in the relevant rows. After ON is the connection condition. The connection operators can be =, >, <, < =, > =, < >, etc.

Example: display the names of all toys and their category names

SELECT vToyName, vCategory
FROM Toys JION CateGory
ON Toys.cCategoryID = Category.cCategoryID


or


SELECT vToyName, vCategory
FROM Toys, Category
WHERE Toys.cCategoryID = Category.cCategoryID

More than two tables can be connected. Each table is a result set, and the connection result of two tables is a result set. Therefore, no matter how many tables, they can be regarded as Cartesian product operation of two result sets.

Example: display the names, trade names and category names of all toys

SELECT vToyName,vBrandName,vCategory
FROM Toys AS a INNER JOIN Category AS b
ON a.cCategoryID = b.cCategoryID
INNER JOIN ToyBrand AS c
ON a.cBrandID = c.cBrandID

perhaps

SELECT vToyName,vBrandName,vCategory
FROM Toys AS a, cCategory AS b, ToyBrand AS c
WHERE a.cCategoryID = b.cCategoryID
AND a.cBrandID = c.cBrandID

The connection results of multiple tables are a set, so you can group and sort the results after connection, which is the same as that of a single table.

Example: query the different toy quantity of each order, and display the order number, order date and quantity of different toys

SELECT　Orders.cOrderNo,dOrderDate,COUNT(cToyId) AS ToyCount
FROM Orders INNER JOIn Orderdetail
ON OrderDetail.cOrderNo = Orders.cOrderNo
GROUP BY Orders.cOrderNo,dOrderDate

Example: query the order with the order processing status of "Y", display the order number, order date, total price, shopper name, receiver name, shopper telephone, receiver name, receiver address, receiver telephone, and arrange them in descending order according to the order date

SELECT Orders.cOrderNo, dOrderDate, mToytalCost, vShopperName, 
    Shopper.cPhone AS shopperphone, vRecipientName, Recipient.vAddress,
    Recipicent.cPhone AS Recipientphone
FROM orders 
    INNER JOIN Shopper 
        ON Shopper.cShopperID = Orders.cShopperID
    INNER JOIN Recipient 
        ON Recipient.cOrderNo = Orders.cOrderNo
WHERE cOrderProcessed = 'Y'
ORDER BY dOrderDate DESC

Example: find the total sales revenue of each month every year

SELECT YEAR(dOrderDate) iyear, MONTH(dOrderDate) imouth, 
    SUM(mTotalCost) AS TotalCost
FROM Orders 
    INNER JOIN OrderDetail 
        ON Orders.cOrderNo = OrderDetail.cOrderNo
GROUP BY YEAR(dOrderDate), MONTH(dOrderDate)

2. External connection

Sometimes, it may be necessary to display all records of one table and some records of another table. This type of connection is called an external connection. The outer connection can be left outer connection, right outer connection or complete outer connection.

Syntax:

SELECT Column name [，Listing]
FROM　Table name　LEFT[RIGHT｜FULL] [OUTER] JOIN Table name
ON　Table name．Reference column name　Join operator　Table name．Reference column name

There are three types of external connections:

(1) LEFT OUTER JOIN (LEFT JOIN or LEFT OUTER JOIN)

         The result set of left out join includes all rows of the left table (the first table) specified in the LEFT OUTER clause and all matching rows in another table, not just the rows matched by the join column. If a row of the left table has no matching row in the right table, all the selection list columns of the right table in the relevant result set are null.

(2) RIGHT OUTER JOIN (RIGHT JOIN or RIGHT OUTER JOIN)

        Right out connection is the reverse connection of left out connection, which returns all rows in the right table (the second table) and all matching rows in another table. If a row in the right table has no matching row in the left table, a null value will be returned for the left table.

(3) FULL OUTER JOIN (FULL JOIN or FULL OUTER JOIN)

        A full outer join returns all rows in the left and right tables. When a row does not match in another table, the selection list column of the other table contains null values. If there are matching rows between tables, the result contains data values that match the base table

  In the following figure, you can see that the category with category ID "003" does not have any toys. Comparison shows the difference between internal and external connections.

  In the above figure, all data in the display category table are obtained through external connection

SELECT Category.cCategoryId, vCategory, cToyId, vToyName, Toys.cCategoryId
FROM Category LEFT OUTER JOIN Toys
ON Category.cCategoryId = Toys.cCategoryId
　　--The query result will contain the left table Category All data in

If you use a right out join and swap the positions of the tables, you will get the same result

SELECT Category.cCategoryId, vCategory, cToyId, vToyName, Toys.cCategoryId
FROM Toys RIGHT OUTER JOIN Category
ON Category.cCategoryId = Toys.cCategoryId
　　--The query result will contain the right table Category All data in

In Toys table, although you can group by Category ID column, and then use COUNT function to calculate the number of different Toys in each group, you can only display the existing Category ID in this table. If you want to display how many different Toys there are for all toy categories, you must access the Category table. You can use external join to join two tables, and then group statistics

SELECT Category.cCategoryId, vCategory, COUNT(cToyId) AS ToyCount
FROM Category LEFT OUTER JOIN Toys
ON Cattegory.cCategoryId = Toys.cCategoryId
GROUP BY Category.cCategoryId, vCategory

This statement first executes the external connection, and then executes the group COUNT on the result of the external connection. Note that the aggregate function used is COUNT(cToysId), not COUNT(*), because COUNT(cToyId) is the number of non null values in the cToyId column and COUNT(*) is the number of rows

Example: query the number of toys of each trademark, display the trademark number, trademark name and number of toys, and arrange them according to the number of toys from large to small

SELECT ToyBrand.cBrandId, vBrandName, COUNT(Toys.cBrandId) AS Number of toys
FROM ToyBrand LEFT OUTER JOIN Toys ON Toys.cBrandId = ToyBrand.cBrandId
GROUP BY ToyBrand.cBrandId, vBrandName
ORDER BY COUNT(Toys.cBrandId) DESC

3. Cross connection

A CROSS JOIN without a WHERE clause will produce a Cartesian product of the tables involved in the join. The product of several table rows is equal to the result set obtained by Cartesian product.

The Cartesian product select * from toys and category mentioned earlier can also be rewritten as

SELECT * FROM Toys CROSS JOIN Category

In fact, cross connect has no practical significance. It is usually just a data display used to test all possible situations.

4. Self connection

Self connection is called self connection. Generally, self connection is used only when there is a relationship between the data in each row of the table, which requires an alias for the table.

  5. Update database with linked query

When updating the related data of another table with the data of one table, you can use the join query to UPDATE, that is, use the FROM clause in the UPDATE statement.

Example: Total = number in order details table × mPrice