0 index
-
The execution order of the JOIN statement
-
INNER/LEFT/RIGHT/FULL JOIN
-
The difference between ON and WHERE
1 overview
A complete SQL statement is broken into clauses that generate a virtual table (VT), but only the last virtual table is returned as a result. With this in mind, let’s try to understand the execution process of a JOIN query and answer some common questions.
If the execution results of different JOIN before have no idea, can combine this article: https://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins to look down.
2 JOIN execution order
Here is the general structure of a JOIN query:
SELECT <row_list>
FROM <left_table>
<inner|left|right> JOIN <right_table>
ON <join condition>
WHERE <where_condition>
Copy the codeIt is executed in the following order (the first FROM clause is always executed in an SQL statement) :
-
FROM: Perform the Cartesian product on the left and right tables to produce the first table VT1. The number of rows is n*m (n is the number of rows in the left table and m is the number of rows in the right table
-
ON: Filters VT1 line by line based ON the conditions of ON and inserts the result into VT2
-
Select * from vt2 where LEFT OUTER JOIN(LEFT OUTER JOIN); select * from vt3 where LEFT OUTER JOIN(LEFT OUTER JOIN); The same is true if the RIGHT JOIN is specified. If an INNER JOIN is specified, external rows are not added, and the above insert process is ignored, vt2=vt3.
-
WHERE: The rows that meet the conditions are output to VT4
-
SELECT: fetch the specified field from VT4 to vt5
Here is an example to illustrate the above process (this is not a good practice, but just to illustrate the join syntax).
3, for example,
Create a user information table:
CREATE TABLE `user_info` (
`userid` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
UNIQUE `userid` (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
Copy the codeCreate another user balance table:
CREATE TABLE `user_account` (
`userid` int(11) NOT NULL,
`money` bigint(20) NOT NULL,
UNIQUE `userid` (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
Copy the codeImport some random 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 (0.00 sec)
select * from user_account;
+--------+-------+
| userid | money |
+--------+-------+
| | | 22 1001
| | | 30 1002
| | | 8 1003
| 1009 | |
+--------+-------+
4 rows in set (0.00 sec)
Copy the codeA total of 8 users have user names and 4 users have balances in their accounts.
Select * from user where username = ‘1003’ and username = ‘1003’
SELECT i.name, a.money
FROM user_info as i
LEFT JOIN user_account as a
ON i.userid = a.userid
WHERE a.userid = 1003;
Copy the codeStep 1: Execute the FROM clause to perform the Cartesian product on the two tables
Cartesian product operation will return after the combination of two pieces of all rows in the table and left the userinfo table for eight, right table useraccount has four lines, the generated virtual table vt1 is 8 * 4 = 32 rows:
SELECT * FROM user_info as i LEFT JOIN user_account as a ON 1;
+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
| 1001 | x | 1001 | 22 |
| 1002 | y | 1001 | 22 |
| 1003 | z | 1001 | 22 |
| 1004 | a | 1001 | 22 |
| 1005 | b | 1001 | 22 |
| 1006 | c | 1001 | 22 |
| 1007 | d | 1001 | 22 |
| 1008 | e | 1001 | 22 |
| 1001 | x | 1002 | 30 |
| 1002 | y | 1002 | 30 |
| 1003 | z | 1002 | 30 |
| 1004 | 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 | 1003 | 8 |
| 1003 | z | 1003 | 8 |
| 1004 | a | 1003 | 8 |
| 1005 | b | 1003 | 8 |
| 1006 | c | 1003 | 8 |
| 1007 | d | 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 |
| 1007 | d | 1009 | 11 |
| 1008 | e | 1009 | 11 |
+--------+------+--------+-------+
32 rows in set (0.00 sec)
Copy the codeStep 2: Execute the ON clause to filter out the rows that do not meet the criteria
ON i.userid = a.userid Vt2 is filtered as follows:
+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
| 1001 | x | 1001 | 22 |
| 1002 | y | 1002 | 30 |
| 1003 | z | 1003 | 8 |
+--------+------+--------+-------+
Copy the codeStep 3: JOIN adds external rows
LEFT JOIN inserts the rows of the LEFT table that are not present in VT2 into vt2 and fills the remaining fields of each row with NULL, the same as RIGHT JOIN.
In this example, a LEFT JOIN is used, so generate table vt3 will be added to the remaining rows of the LEFT table user_info:
+--------+------+--------+-------+
| 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 |
+--------+------+--------+-------+
Copy the codeStep 4: WHERE condition filtering
WHERE a.user ID = 1003 create table vt4:
+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
| 1003 | z | 1003 | 8 |
+--------+------+--------+-------+
Copy the codeStep 5: SELECT
SELECT i.name., a.money to generate vt5:
+------+-------+
| name | money |
+------+-------+
| z | 8 |
+------+-------+
Copy the codeThe virtual table VT5 is returned to the client as the final result.
Now that we have covered the process of union tables, let’s look at the differences between common joins.
4 INNER/LEFT/RIGHT/FULL JOIN
-
INNER JOIN… ON… : returns all rows that match the left and right tables (because only step 2 ON filtering is performed above, step 3 does not add external rows)
-
LEFT JOIN… ON… : returns all rows in the left table. If there is no matching row in the right table, the right table column is set to NULL in the new table
-
RIGHT JOIN… ON… : returns all rows of the right table. If there is no matching row in the left table, the left table column is set to NULL in the new table
INNER JOIN
Select * from vt3 where LEFT JOIN is replaced by INNER JOIN; select * from vt3 where LEFT JOIN is replaced by INNER JOIN;
-
+--------+------+--------+-------+
-
| userid | name | userid | money |
-
+--------+------+--------+-------+
-
| 1001 | x | 1001 | 22 |
-
| 1002 | y | 1002 | 30 |
-
| 1003 | z | 1003 | 8 |
-
+--------+------+--------+-------+
Copy the codeRIGHT JOIN
If LEFT JOIN is replaced by RIGHT JOIN, the generated 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 |
+--------+------+--------+-------+
Copy the codeBecause userId =1009 exists in userAccount and userInfo does not exist in userInfo, insert the following line in step 3:
| NULL | NULL | 1009 | 11 |
Copy the codeFULL JOIN
LEFT JOIN + UNION + RIGHT JOIN; LEFT JOIN + UNION + RIGHT JOIN;
SELECT *
FROM user_info as i
RIGHT JOIN user_account as a
ON a.userid=i.userid
union
SELECT *
FROM user_info as i
LEFT JOIN user_account as a
ON a.userid=i.userid;
Copy the codeIt returns 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 |
+--------+------+--------+-------+
Copy the codePs: 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.
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 debating whether to use LEFT JOIN or RIGHT JOIN, use only LEFT JOIN when possible.
5 the difference between ON and WHERE
The difference between ON and WHERE is easy to understand now that the order of JOIN execution is clear.
For example:
SELECT *
FROM user_info as i
LEFT JOIN user_account as a
ON i.userid = a.userid and i.userid = 1003;
Copy the code
SELECT *
FROM user_info as i
LEFT JOIN user_account as a
ON i.userid = a.userid where i.userid = 1003;
Copy the codeIn the first case, after executing the second step ON clause, select the rows that meet i.usserid = A.usserid and I.usserid = 1003 to generate table VT2, and then execute the third step JOIN clause to add the external rows to the virtual table to generate vt3, which is the final result:
-
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 |
-
+--------+------+--------+-------+
Copy the codeIn the second case, after executing the second step ON clause, LEFT JOIN selects the rows that meet i.userid = A. user ID and generates table vt2. Add the external row generation table vt3 by executing the third step JOIN clause. Then execute the fourth step WHERE clause, then filter vt3 table to generate vt4, obtain 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 |
-
+--------+------+--------+-------+
Copy the codeIf you replace the LEFT JOIN in the above example with an INNER JOIN, the result is the same whether you place the conditional filter in ON or WHERE, because the INNER JOIN does not perform the third step to add the external row.
SELECT *
FROM user_info as i
INNER JOIN user_account as a
ON i.userid = a.userid and i.userid = 1003;
Copy the code
SELECT *
FROM user_info as i
INNER JOIN user_account as a
ON i.userid = a.userid where i.userid = 1003;
Copy the codeThe return result is:
+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
| 1003 | z | 1003 | 8 |
+--------+------+--------+-------+
Copy the codeThe resources
-
MySQL Technology Insider: SQL Programming
-
SQL Joins – W3Schools
-
SQL – What is the difference between “INNER JOIN” and “OUTER JOIN”?
-
MySQL :: MySQL 8.0 Reference Manual :: 13.2.10.2 JOIN Syntax
-
Visual Representation of SQL Joins
-
Join (SQL) – Wikipedia
Welcome to follow SegmentFault wechat official account 🙂
