Mysql's detailed explanation of JOIN

mysql video tutorialDetailed column explanationjoin

##Related free learning recommendations:

mysql video tutorial

0 Index

• JOINExecution order of statements
• INNER/LEFT/RIGHT/FULL JOIN## The difference between
#ON
• and WHERE

1 Overview

A complete SQL The statement will be split into multiple clauses. During the execution of the clauses, a virtual table (vt) will be generated, but the result will only return the last virtual table. Starting from this idea, we try to understand the execution process of JOIN query and answer some common questions.

If you have no idea about the execution results of different JOINs before, you can read this article below

2 JOIN execution sequence

The following is the general structure of JOIN query

SELECT  
  FROM  
     JOIN  
      ON  
        WHERE 

Its execution sequence is as follows

(The first one executed in the SQL statement is always the FROM clause)

:

FROM
• : Right and left The Cartesian product of the two tables is performed to produce the first table vt1. The number of rows is n*m (n is the number of rows in the left table, m is the number of rows in the right table
ON
• : Filter vt1 row by row according to the ON condition, and insert the result into vt2
JOIN
• : Add outer rows if LEFT JOIN(LEFT OUTER JOIN## is specified #), then first traverse each row of left table, and the rows that are not in vt2 will be inserted into vt2, and the remaining fields of the row will be filled with NULL, forming vt3; the same is true if RIGHT JOIN is specified. But if INNER JOIN is specified, it will not Add external rows, the above insertion process is ignored, vt2=vt3 (so the filter condition of INNER JOIN is placed in ON or ## There is no difference in the execution results in #WHERE, which will be explained in detail below) WHERE: Perform conditional filtering on vt3, and rows that meet the conditions are output Go to vt4
• SELECT: Take out the specified fields of vt4 to vt5
• Let’s use an example to introduce the process of joining the above tables (this example is not a good one) Practice, just to illustrate the join syntax)
3 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

Create a 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

Whatever 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 (0.00 sec)

select * from user_account;
+--------+-------+
| userid | money |
+--------+-------+
|   1001 |    22 |
|   1002 |    30 |
|   1003 |     8 |
|   1009 |    11 |
+--------+-------+
4 rows in set (0.00 sec)

A total of 8 users have user names, and 4 users have account balances.

Get the user name and balance with userid 1003, the SQL is as follows

:

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;

Step 1: Execute the FROM clause to perform a Cartesian product operation on the two tablesAfter the Cartesian product operation, the combination of all rows in the two tables will be returned. The left table user_info has 8 rows. The right table user_account has 4 rows, and 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)

Step 2: Execute the ON clause to filter out rows that do not meet the conditions

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

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

Step 3: JOIN to add external rows

LEFT JOIN

will add rows from the left table that do not appear in vt2 Insert into vt2, the remaining fields of each row will be filled with NULL,

RIGHT JOIN

SimilarlyIn this example, LEFT JOIN is used, so the left tableuser_info
Add the remaining rows to generate 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 condition filteringWHERE a.userid = 1003 Generate table vt4:

+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1003 | z    |   1003 |     8 |
+--------+------+--------+-------+

Step 5: SELECT

SELECT i.name, a.money Generate vt5:

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

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

Introduction to the end of the connection After the table process, let’s look at the differences between commonly used

JOIN

4 The differences 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 of ON filtering above is performed, and the third step of adding external rows is not performed)

• 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 columns of the right table to NULL
in the new table.
• RIGHT JOIN...ON...: Returns all rows in the right table. If some rows do not have corresponding matching rows in the left table, add the columns of the left table to the new table. The center is NULL
• INNER JOIN
Take the third step

Add external row

as an example, if

LEFT JOIN

is replaced If it becomes INNER JOIN, this step will be skipped, and the generated table vt3 will be exactly the same as vt2:

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

RIGHT JOINIf

LEFT JOIN

is replaced Into

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 |
+--------+------+--------+-------+

Because the row userid=1009 exists in user_account (right table), but it is not found in user_info (left table) There is no record of this row, so the following row will be inserted in the third step:

|   NULL | NULL |   1009 |    11 |

FULL JOIN

上文引用的文章中提到了标准SQL定义的FULL JOIN，这在mysql里是不支持的，不过我们可以通过LEFT JOIN + UNION + RIGHT JOIN 来实现FULL 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;

他会返回如下结果：

+--------+------+--------+-------+
| 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：其实我们从语义上就能看出LEFT JOIN和RIGHT JOIN没什么差别，两者的结果差异取决于左右表的放置顺序，以下内容摘自mysql官方文档：

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.

所以当你纠结使用LEFT JOIN还是RIGHT JOIN时，尽可能只使用LEFT JOIN吧

5 ON和WHERE的区别

上文把JOIN的执行顺序了解清楚之后，ON和WHERE的区别也就很好理解了。
举例说明:

SELECT * 
  FROM user_info as i
    LEFT JOIN user_account as a
      ON i.userid = a.userid and i.userid = 1003;

SELECT * 
  FROM user_info as i
    LEFT JOIN user_account as a
      ON i.userid = a.userid where i.userid = 1003;

第一种情况LEFT JOIN在执行完第二步ON子句后，筛选出满足i.userid = a.userid and i.userid = 1003的行，生成表vt2，然后执行第三步JOIN子句，将外部行添加进虚拟表生成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 |
+--------+------+--------+-------+

而第二种情况LEFT JOIN在执行完第二步ON子句后，筛选出满足i.userid = a.userid的行，生成表vt2；再执行第三步JOIN子句添加外部行生成表vt3；然后执行第四步WHERE子句，再对vt3表进行过滤生成vt4，得的最终结果：

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 |
+--------+------+--------+-------+

如果将上例的LEFT JOIN替换成INNER JOIN，不论将条件过滤放到ON还是WHERE里，结果都是一样的，因为INNER JOIN不会执行第三步添加外部行

SELECT * 
  FROM user_info as i
    INNER JOIN user_account as a
      ON i.userid = a.userid and i.userid = 1003;

SELECT * 
  FROM user_info as i
    INNER JOIN user_account as a
      ON i.userid = a.userid where i.userid = 1003;

返回结果都是:

+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1003 | z    |   1003 |     8 |
+--------+------+--------+-------+

想了解更多编程学习，敬请关注php培训栏目！

The above is the detailed content of Mysql's detailed explanation of JOIN. For more information, please follow other related articles on the PHP Chinese website!