mysql multi-table connection query operation example

In actual projects, there are relationships between multiple tables. It is impossible to retrieve all data in one table. If there is no table connection, then we will 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 it is also not very efficient. For example, the example in the book:

The 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 it can be entered in T_Order Retrieve records with FCustomerId equal to 2:

The code is as follows:

SELECT FNumber,FPrice
FROM T_Order
WHERE FCustomerId=2

Let’s take a look at the table connection in detail. There are many different types of table joins, including cross joins (CROSS JOIN), inner joins (INNER JOIN), and outer joins (OUTTER JOIN).

(1) INNER JOIN: Inner join combines two tables and only obtains data that meets the connection 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 situations where many tables need to be contacted. For example, the T_Order table also needs to be connected to the T_Customer and T_OrderType tables to retrieve the required information. Just 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 (CROSS JOIN): All records in all tables involved in the cross join are included in the result set. Cross-connections can be defined in two ways, namely implicit and explicit connections.

Let’s take a look at the 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

If you use explicit connection, you need to use CROSS JOIN. The example is as follows:

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) OUTTER JOIN: Internal connections only obtain data that meets the connection conditions, while external connections mainly solve such a scenario. There is no doubt that the data that meets the conditions is retrieved. The external 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 (LEFT OUTER JOIN), right outer join (RIGHT OUTER JOIN) and full outer join (FULLOUTER JOIN).

I. LEFT OUTER JOIN: As mentioned before, data that does not meet the conditions are filled with NULL. So what specifically needs to be filled with NULL? For left outer joins, among the connection conditions, 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 field needs to be filled with NULL. value. That is to say, the main body of the left outer connection is the left table, and the right table matches 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 connection, you can filter 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. In other words, the subject of the right outer connection is the right table, and the left table matches 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. Full outer join (FULLOUTER JOIN): The full outer join is the left A collection of outer joins and right outer joins. That is, it includes both the result set of the left outer connection and the result set of the right outer connection.

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

====================== ================================================== ===============

Various sql writing methods for multi-table query: (The following is query from two tablesead4289a0bcf5b0b7bc4a03c1e30f4bd, display all fields in the table v_goods, display the name field in the admin2 table as the adder, display the name field in the admin2 table as the operator) Query of multiple tables can be written according to the following three example sentences sql

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 ;

The above is the detailed content of mysql multi-table connection query operation example. For more information, please follow other related articles on the PHP Chinese website!