Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to achieve multi-table join query in mysql. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

The code is as follows:

SELECT FIdFROM T_CustomerWHERE 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:

The code is as follows:

SELECT FNumber,FPriceFROM T_OrderWHERE FCustomerId=2

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

(1) INNER JOIN: the inner join combines two tables and only obtains the data that meets the conditions for joining the two tables.

The code is as follows:

SELECT o. FID T_Order o. FNumberCode o. FPriceDie c. FID direction c. FNameDie c. FAgeFROM T_Order 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 can not be limited to joining only two tables, because there are many cases where you need to contact many tables. For example, the T_Order table also needs to join two tables, T_Customer and T_OrderType, to retrieve the required information. Write the following SQL statement:

The code is as follows:

SELECT o. FID T_Order o. FNumberJ o. FPriceLegend c. FID T_Order c. FNamePape c. FAgeFROM Die o JOIN T_Customer cON o. FCustomerId = c.FIdINNER JOIN T_OrderTypeON titled Order.FTypeId = T_OrderType.FId

(2) 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, implicit and explicit.

Let's take a 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.FPriceFROM T_Customer, T_Order

Using explicit connections requires the use of CROSS JOIN, as shown in the following example:

The code is as follows:

SELECT T_Customer.FId, T_Customer.FName, T_Customer.FAge,T_Order.FId, T_Order.FNumber, T_Order.FPriceFROM T_CustomerCROSS JOIN T_Order

(3) external connection (OUTTER JOIN): the internal connection only obtains the data that meets the connection conditions, while for the external connection, it mainly solves such a scenario. There is no doubt that the data that meets the criteria will be retrieved, and the external connection will also retrieve another part of the data, that is, the data that does not meet the criteria will be populated with NULL. Let's first take a look at the categories of external connections: left external connections (LEFT OUTER JOIN), right external connections (RIGHT OUTER JOIN), and full external connections (FULLOUTER JOIN).

I, left external connection (LEFT OUTER JOIN): as mentioned earlier, the data that does not meet the conditions will be populated with NULL. So what exactly needs to be filled with NULL? for the left outer join, if the data of the left table that meets the conditions does not match in the right table, you need to fill the corresponding right table fields with null values. That is to say, the main body of the left outer connection is the left table and the right table is used to cooperate.

The code is as follows:

SELECT o.FNumber,o.FPrice,o.FCustomerId,c.FName,c.FAgeFROM T_Order oLEFT OUTER JOIN T_Customer cON o.FCustomerId=c.FId

Note: if the left outer connection is used, the data that does not match can be filtered through the where statement.

The code is as follows:

SELECT o.FNumberMoreo.FPriceDiffero.FCustomerIdDirec. FNamerec.FAgeFRom T_Order oLEFT OUTER JOIN T_Customer cON o.FCustomerId=c.FIdWHERE o.FPrice > = 150

II, right outer join (RIGHT OUTER JOIN): the right outer join is the opposite of the left outer join, and the fields in the left table that will be filled with null values. In other words, the main body of the right external connection is the right table, which is matched by the left table.

The code is as follows:

SELECT o.FNumber,o.FPrice,o.FCustomerId,c.FName,c.FAgeFROM T_Order oRIGHT OUTER JOIN T_Customer cON o.FCustomerId=c.FId

Note: like the left outer connection, you can use the where statement to filter

III, full external connection (FULLOUTER JOIN): full external connection is a collection of left external connection and right external connection. 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.FAgeFROM T_Order oFULL OUTER JOIN T_Customer cON o.FCustomerId=c.FId

The result is equivalent to:

SELECT o.FNumber,o.FPrice,o.FCustomerId,c.FName,c.FAgeFROM T_Order oLEFT OUTER JOIN T_Customer cON o.FCustomerId=c.FIdUNIONSELECT o.FNumber,o.FPrice,o.FCustomerId,c.FName,c.FAgeFROM T_Order oRIGHT OUTER JOIN T_Customer cON o.FCustomerId=c.FId

There are several sql ways to write a multi-table query: (the following is a query from two tables, showing all the fields in the table v_goods, showing the name field in the admin2 table as the add, and the name field in the table admin2 as the operator.) queries with multiple tables can be written according to the following three example sentences

SELECT v. Aname,b.name uname FROM v_goods, (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 1century 1itSelect v.SELECT aname,b.name uname FROM v_goods v.name aname,b.name uname FROM v_goods v LEFT JOIN admin2 v ON a.adminId=v.loadInId LEFT JOIN admin2 b select v.name aname,b.name uname FROM v_goods v LEFT JOIN admin2 v ON a.adminId=v.loadInId LEFT JOIN admin2 b ON b.adminId=v.operatorId On how to achieve multi-table join query in mysql to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.