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.
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