How to use SQL table connection query (SQL multi-Table connection query)

There are associations between multiple tables in the actual project. It is impossible to retrieve all data in a table. If there is no table connection, we need a lot of operations. For example, you need to find restrictive conditions from Table A to retrieve data from Table B. It is not only necessary to split multiple tables, but also inefficient. For example, the example in the book:
Copy codeThe Code is as follows:
SELECT FId
FROM T_Customer
WHERE FName = 'Mike'

This SQL statement returns 2, that is, the FId value of the customer named MIKE is 2, so that the record with FCustomerId equal to 2 can be retrieved in T_Order:

Copy codeThe Code is as follows:
SELECT FNumber, FPrice
FROM T_Order
WHERE FCustomerId = 2

Next we will look at table join in detail. Table JOIN supports multiple types, including cross join, inner join, and outer JOIN ).

(1) inner join: An inner join combines two tables and obtains only data that meets the two table JOIN conditions.
Copy codeThe Code is as follows:
SELECT o. FId, o. FNumber, o. FPrice,
C. FId, c. FName, c. FAge
FROM T_Order o JOIN T_Customer c
ON o. FCustomerId = c. FId

Note: In most database systems, inner join is optional, and inner join is the default connection method.

When using table join, you can connect only two tables. In many cases, you need to contact many tables. For example, the T_Order table must be connected to both T_Customer and T_OrderType tables to retrieve the required information. Compile the following SQL statement:
Copy codeThe Code is as follows:
SELECT o. FId, o. FNumber, o. FPrice,
C. FId, c. FName, c. FAge
FROM T_Order o JOIN T_Customer c
ON o. FCustomerId = c. FId
Inner join T_OrderType
ON T_Order.FTypeId = T_OrderType.FId

(2) cross join: all records in all tables involved in the cross join are included in the result set. You can use two methods to define cross-join: implicit and explicit connections.

The following is an implicit example:
Copy codeThe Code is as follows:
SELECT T_Customer.FId, T_Customer.FName, T_Customer.FAge,
T_Order.FId, T_Order.FNumber, T_Order.FPrice
FROM T_Customer, T_Order

Cross join is required to use explicit connections. The example is as follows:

Copy codeThe Code is as follows:
SELECT T_Customer.FId, T_Customer.FName, T_Customer.FAge,
T_Order.FId, T_Order.FNumber, T_Order.FPrice
FROM T_Customer
Cross join T_Order

(3) Outer JOIN: an internal connection only obtains data that meets the connection conditions. However, an external connection mainly solves this problem. If the data that meets the condition is retrieved, there is no doubt that the external connection will retrieve another part of the data, that is, it will fill the data that does not meet the condition with NULL. Let's take a look at the classification of external connections: left outer join, right outer join, and full outer join ).

I. LEFT OUTER JOIN (LEFT OUTER JOIN): As mentioned earlier, the data that does not meet the conditions is filled with NULL. Which of the following conditions must be filled with NULL? For the left Outer Join, if the data in the left table that meets the conditions does not match in the right table, you need to fill in the corresponding right table field with a NULL value. That is to say, the subject of the left external connection is the left table and the right table.

Copy codeThe Code is as follows:
SELECT o. FNumber, o. FPrice, o. FCustomerId,
C. FName, c. FAge
FROM T_Order o
Left outer join T_Customer c
ON o. FCustomerId = c. FId

NOTE: If left external connection is used, the where statement can be used to filter out non-conforming data.

Copy codeThe Code is as follows:
SELECT o. FNumber, o. FPrice, o. FCustomerId,
C. FName, c. FAge
FROM T_Order o
Left outer join T_Customer c
ON o. FCustomerId = c. FId
WHERE o. FPrice & gt; = 150

II. right outer join: The right outer join is opposite to the left outer join, and the field in the left table is filled with NULL values. That is to say, the subject of the right external connection is the right table, and the left table is used together.

Copy codeThe Code is as follows:
SELECT o. FNumber, o. FPrice, o. FCustomerId,
C. FName, c. FAge
FROM T_Order o
Right outer join T_Customer c
ON o. FCustomerId = c. FId

Note: like the left outer join, you can use the where statement for filtering.

III. Full outer JOIN: Full outer JOIN is a collection of left Outer JOIN and right outer JOIN. That is, it includes both the result set of the left external connection and the result set of the right external connection.

Copy codeThe Code is as follows:
SELECT o. FNumber, o. FPrice, o. FCustomerId,
C. FName, c. FAge
FROM T_Order o
Full outer join T_Customer c
ON o. FCustomerId = c. FId

The result is equivalent:

Copy codeThe Code is as follows:
SELECT o. FNumber, o. FPrice, o. FCustomerId,
C. FName, c. FAge
FROM T_Order o
Left outer join T_Customer c
ON o. FCustomerId = c. FId
UNION
SELECT o. FNumber, o. FPrice, o. FCustomerId,
C. FName, c. FAge
FROM T_Order o
Right outer join T_Customer c
ON o. FCustomerId = c. FId