Shulou(Shulou.com)06/01 Report--

This article is to share with you about the use of JOIN in Mysq. The editor thought it was very practical, so I shared it with you as a reference. Let's follow the editor and have a look.

0 Index JOIN statement execution order INNER/LEFT/RIGHT/FULL JOIN difference between ON and WHERE 1 Overview

A complete SQL statement will be split into multiple clauses. During the execution of the clause, a virtual table (vt) will be generated, but the result will only return the last virtual table. With this in mind, let's try to understand the execution of JOIN queries and answer some common questions.

If you have no idea of the implementation results of different JOIN before, you can read on in conjunction with this article.

2 order of execution of JOIN

The following is the general structure of the JOIN query

SELECT FROM JOIN ON WHERE

It is executed in the following order (the first execution in the SQL statement is always the FROM clause):

FROM: performs a Cartesian product on the left and right tables to produce the first table vt1. The number of rows is ON (n is the number of rows of the left table, m is the number of rows of the right table ON: filter vt1 row by row according to the condition of ON, insert the result into vt2 JOIN: add external rows, if LEFT JOIN (LEFT OUTER JOIN) is specified, then traverse each row of the left table first, in which the rows that are not in vt2 will be inserted into vt2, and the remaining fields of the row will be filled with NULL to form vt3; if RIGHT JOIN is specified is the same. However, if INNER JOIN is specified, no external rows will be added, and the above insertion process is ignored. Vt2=vt3 (so there is no difference between the results of INNER JOIN filtering conditions in ON or WHERE, which will be described below) WHERE: conditional filtering is performed on vt3, and rows that meet the criteria are output to vt4SELECT: fetch the specified fields of vt4 to vt5

Let's use an example to introduce the process of the above joined table (this example is not a good practice, just to illustrate the join syntax)

3 examples

Create a user information table:

CREATE TABLE `userid` (`userid` int (11) NOT NULL, `name` varchar (255) NOT NULL, UNIQUE `userid` (`userid`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Create another user balance table:

CREATE TABLE `userid` (`userid` int (11) NOT NULL, `money` bigint (20) NOT NULL, UNIQUE `userid` (`userid`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Casually import some data:

Select * from user_info;+-+-+ | userid | name | +-+-+ | 1001 | x | | 1002 | y | 1003 | z | 1004 | a | 1005 | b | 1006 | c | 1007 | d | 1008 | e | +-+-+ 8 rows in set (sec) select * from user_account +-+-+ | userid | money | +-+-+ | 1001 | 22 | 1002 | 30 | 1003 | 8 | 1009 | 11 | +-+-+ 4 rows in set (1009 sec)

A total of 8 users have user names and 4 users have balances in their accounts.

Withdraw the name and balance of the user with a userid of 1003. The SQL is as follows:

SELECT i.name, a.money FROM user_info as i LEFT JOIN user_account as an ON i.userid = a.userid WHERE a.userid = 1003; the first step: execute the FROM clause to perform Cartesian product operations on two tables

After the Cartesian product operation, the combination of all rows in the two tables is returned. The left table user_info has 8 rows and the right table user_account has 4 rows. The resulting virtual table vt1 is 8 rows 4 rows 32 rows:

SELECT * FROM user_info as i LEFT JOIN user_account as an ON 1 +-+ | userid | name | userid | money | +-+ | 1001 | x | 1001 | 22 | 1002 | y | 1001 | 22 | 1003 | z | 1001 | 22 | 1004 | a | 1001 | | | 1005 | b | 1001 | 22 | 1006 | c | 1001 | 22 | 1007 | d | 1001 | 22 | 1008 | e | 1001 | 22 | | 1001 | x | 1002 | 30 | 1002 | z | 1002 | 30 | 1002 | a | 1002 | 30 | 1005 | b | 1002 | 30 | 1006 | c | 1002 | | 30 | | 1007 | d | 1002 | 30 | 1008 | e | 1002 | 30 | 1001 | x | 1003 | 8 | 1002 | y | 8 | 1003 | z | 1003 | 8 | 1004 | a | 1003 | 8 | b | 1003 | 8 | 1006 | c | 1003 | 8 | 1007 | d | 1003 | 8 | 1003 | 8 | 1008 | | | e | 1003 | 8 | | 1001 | x | 1009 | 11 | 1002 | y | 1009 | 11 | 1003 | z | 1009 | 11 | 1004 | a | 1009 | 11 | 1005 | b | 1009 | 11 | 1006 | c | 1009 | 11 | d | 1009 | 11 | 1008 | e | 1009 | 11 | +-| -- + 32 rows in set (0.00 sec) step 2: execute the ON clause to filter out rows that do not meet the criteria

ON i.userid = a.userid after filtering, the vt2 is as follows:

+-+ | userid | name | userid | money | +-+ | 1001 | x | 1001 | 22 | 1002 | y | 1002 | 30 | 1003 | z | 1003 | 8 | +- +-+ step 3: JOIN add external lines

LEFT JOIN will insert rows in the left table that do not appear in vt2 into vt2, and the remaining fields of each row will be populated as NULL,RIGHT JOIN.

In this example, LEFT JOIN is used, so the remaining rows of the left table user_info are added to the generation table vt3:

+-+ | userid | name | userid | money | +-+ | 1001 | x | 1001 | 22 | 1002 | y | 1002 | 30 | 1003 | z | 1003 | 8 | 1004 | a | NULL | | NULL | | 1005 | b | NULL | NULL | | 1006 | c | NULL | NULL | | 1007 | d | NULL | NULL | | 1008 | e | NULL | NULL | +-+ step 4: WHERE conditional filtering |

WHERE a.userid = 1003 generate table vt4:

+-+ | userid | name | userid | money | +-+ | 1003 | z | 1003 | 8 | +-+ step 5: SELECT

SELECT i.name, a.money generates vt5:

+-+ | name | money | +-+-+ | z | 8 | +-+-+

The virtual table vt5 is returned to the client as the final result

After introducing the process of join tables, let's take a look at the differences between commonly used JOIN

4 the difference between INNER/LEFT/RIGHT/FULL JOIN INNER JOIN...ON...: returns all rows that match each other in the left and right tables (because only the second step ON filtering above is performed, not step 3 adding external rows) LEFT JOIN...ON...: returns all rows in the left table, if some rows do not have corresponding matching rows in the right table Set the column of the right table to NULLRIGHT JOIN...ON...: in the new table and return all rows of the right table. If some rows do not match in the left table, set the column of the left table to NULLINNER JOIN in the new table.

Take the third step above to add an external line as an example. If LEFT JOIN is replaced with INNER JOIN, this step will be skipped and the resulting table vt3 is exactly the same as vt2:

+-+ | userid | name | userid | money | +-+ | 1001 | x | 1001 | 22 | 1002 | y | 1002 | 30 | 1003 | z | 1003 | 8 | +- +-+ RIGHT JOIN

If LEFT JOIN is replaced with RIGHT JOIN, the resulting table vt3 is as follows:

+-+ | userid | name | userid | money | +-+ | 1001 | x | 1001 | 22 | 1002 | y | 1002 | 30 | 1003 | z | 1003 | 8 | NULL | NULL | 1009 | 11 | +-+

Because the row userid=1009 exists in user_account (right table), but cannot be found in user_info (left table), the following line is inserted in step 3:

| | NULL | NULL | 1009 | 11 | FULL JOIN |

The FULL JOIN defined by the standard SQL is mentioned in the article cited above, which is not supported in mysql, but we can implement FULL JOIN through LEFT JOIN + UNION + RIGHT JOIN:

SELECT * FROM user_info as i RIGHT JOIN user_account as an ON a.userid=i.useridunion SELECT * FROM user_info as i LEFT JOIN user_account as an ON a.userid=i.userid

He will return the following result:

+-+ | userid | name | userid | money | +-+ | 1001 | x | 1001 | 22 | 1002 | y | 1002 | 30 | 1003 | z | 1003 | 8 | NULL | NULL | 1009 | 11 | | 1004 | a | NULL | NULL | | 1005 | b | NULL | NULL | | 1006 | c | NULL | NULL | | 1007 | d | NULL | NULL | | 1008 | e | NULL | NULL | +-+

Ps: in fact, we can see that there is no difference between LEFT JOIN and RIGHT JOIN semantically. The difference between the two results depends on the order in which the left and right tables are placed. The following is extracted from the official mysql documentation:

RIGHT JOIN works analogously to LEFT JOIN. To keep code portable across databases, it is recommended that you use LEFT JOIN instead of RIGHT JOIN.

So when you're struggling with whether to use LEFT JOIN or RIGHT JOIN, try to use only LEFT JOIN.

5 the difference between ON and WHERE

Once you know the order in which JOIN is executed, the difference between ON and WHERE is easy to understand.

Examples are as follows:

SELECT * FROM user_info as i LEFT JOIN user_account as an ON i.userid = a.userid and i.userid = 1003 bot select * FROM user_info as i LEFT JOIN user_account as an ON i.userid = a.userid where i.userid = 1003

In the first case, after executing the second step ON clause, LEFT JOIN filters out the rows that satisfy i.userid = a.userid and i.userid = 1003, generates the table vt2, and then executes the third step JOIN clause to add external rows to the virtual table to generate vt3:

Vt2:+-+ | userid | name | userid | money | +-+ | 1003 | z | 1003 | 8 | +- -- + vt3:+-+ | userid | name | userid | money | +-+ | 1001 | x | NULL | NULL | | 1002 | y | NULL | NULL | | 1003 | z | 1003 | 8 | 1004 | a | NULL | NULL | | 1005 | b | NULL | NULL | | 1006 | c | NULL | NULL | | 1007 | d | NULL | NULL | 1008 | e | NULL | NULL | +-+

In the second case, after executing the second step ON clause, LEFT JOIN filters out the rows that satisfy i.userid = a.userid, generates the table vt2;, then executes the third step JOIN clause to add the external row to generate the table vt3;, then executes the fourth step WHERE clause, and then filters the vt3 table to generate vt4. The final result:

Vt2:+-+ | userid | name | userid | money | +-+ | 1001 | x | 1001 | 22 | 1002 | y | 1002 | 30 | 1003 | z | 1003 | 8 | +- -- + vt3:+-+ | userid | name | userid | money | +-+ | 1001 | x | 1001 | 22 | | 1002 | y | | 1002 | 30 | | 1003 | z | 1003 | 8 | 1004 | a | NULL | NULL | 1005 | b | NULL | NULL | 1006 | c | NULL | NULL | 1007 | d | NULL | NULL | 1008 | e | NULL | NULL | +-+ vt4:+-+- | -+ | userid | name | userid | money | +-+ | 1003 | z | 1003 | 8 | +-+

If you replace the LEFT JOIN in the above example with INNER JOIN, the result will be the same regardless of whether you put conditional filtering into ON or WHERE, because INNER JOIN will not perform step 3 to add external lines

SELECT * FROM user_info as i INNER JOIN user_account as an ON i.userid = a.userid and i.userid = 1003 bot select * FROM user_info as i INNER JOIN user_account as an ON i.userid = a.userid where i.userid = 1003

The returned results are as follows:

+-+ | userid | name | userid | money | +-+ | 1003 | z | 1003 | 8 | +-+ Thank you for your reading! About the use of JOIN in Mysq to share here, I hope the above content can be of some help to you, so that you can learn more knowledge. If you think the article is good, you can share it and let more people see it.

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.