MySQL multi-table join query example explanation

In actual projects, there are relationships between multiple tables. It is impossible to retrieve all the data in one table. If there is no table connection, then we need a lot of operations. For example, you need to find restrictive conditions from table A to retrieve data from table B. Not only does it require multiple tables to operate, but the efficiency is also not high. For example, in the book:

The code is as follows:

SELECT FId
FROM T_Customer
WHERE FName='MIKE'

This SQL statement returns 2, which means the FId value of the customer named MIKE is 2. In this way, we can retrieve the record with FCustomerId equal to 2 in T_Order:

The code is as follows:

SELECT FNumber,FPrice
FROM T_Order
WHERE FCustomerId=2

Let's take a closer look at table joins. There are many different types of table joins, including cross join (CROSS JOIN), inner join (INNER JOIN), and outer join (OUTTER JOIN).

(1) INNER JOIN: An inner join combines two tables and only retrieves data that meets the join conditions of the two tables.

The 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 in INNER JOIN is optional and INNER JOIN is the default connection method.

When using table joins, you are not limited to joining only two tables, because there are many cases where you need to connect many tables. For example, the T_Order table also needs to connect to the T_Customer and T_OrderType tables to retrieve the required information. You can write the following SQL statement:

The 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 a cross join are included in the result set. There are two ways to define cross joins: implicit and explicit.

Let's look at an implicit example:

The 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

To use an explicit join, you need to use CROSS JOIN, as shown below:

The 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 join only obtains data that meets the join conditions, while an external join is mainly used to solve such a scenario. The data that meets the conditions is retrieved, there is no doubt about this, the outer connection will also retrieve another part of the data, that is, the data that does not meet the conditions will be filled with NULL. Let's first look at the classification of outer joins: LEFT OUTER JOIN, RIGHT OUTER JOIN and FULL OUTER JOIN.

I. LEFT OUTER JOIN: As mentioned above, data that does not meet the conditions will be filled with NULL. So which ones need to be filled with NULL? For the left outer join, if the data in the left table that meets the conditions does not have a corresponding match in the right table, the corresponding right table fields need to be filled with NULL values. That is to say, the main body of the left outer join is the left table, and the right table cooperates with it.

The 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 you use a left outer join, you can filter out the data that does not match the where statement.

The 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>=150

II. RIGHT OUTER JOIN: The right outer join is the opposite of the left outer join. The fields of the left table will be filled with NULL values. That is to say, the subject of the right outer join is the right table, and the left table cooperates with it.

The 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 to filter

III. FULLOUTER JOIN: A full outer join is a combination of a left outer join and a right outer join. That is, it includes both the result set of the left outer join and the result set of the right outer join.

The 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 to:

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

Multiple SQL statements for multi-table queries: (The following is a query from two tables <can also be seen as a query from three tables>, displaying all fields in the v_goods table, displaying the name field in the admin2 table as the added person, and displaying the name field in the admin2 table as the operator) Multiple table queries can be written in SQL according to the following three examples

SELECT v.*,(SELECT a.name FROM admin2 a WHERE a.adminId=v.loadInId) AS aname,(SELECT a.name FROM admin2 a WHERE a.adminId=v.operatorId) AS uname FROM v_goods v where 1=1;
SELECT v.*,a.name aname,b.name uname FROM v_goods v,admin2 a,admin2 b WHERE a.adminId=v.loadInId AND b.adminId=v.operatorId;
SELECT v.*,a.name aname,b.name uname FROM v_goods v LEFT JOIN admin2 a ON a.adminId=v.loadInId LEFT JOIN admin2 b ON b.adminId=v.operatorId;