MySQL LEFT JOIN/ INNER JOIN/RIGHT JOIN

Time:2021-10-13

summary

A complete SQL statement will be split into many sub clauses. During the execution of the clause, multiple temporary tables (VTS) will be generated, but only the last temporary table will be returned. Starting from this idea, we try to understand the execution process of join query and answer some common questions.

Join execution sequence

Common structure of join query:

SELECT <row_list> 
FROM <left_table> <left|inner|right> 
JOIN <right_table> 
ON <join_condition>
WHERE <where_condition>

Its execution sequence is as follows(the first executed SQL statement is always the from clause)

  • From: Cartesian product is performed on the left and right tables to generate the first temporary table VT1 with n * m rows (n is the number of rows in the left table and M is the number of rows in the right table).
  • On: filter the VT1 table according to the on condition, and insert the structure into the vt2 table.
  • Join: adds an external row. If left join (left outer join) is specified, first traverse each row of the left table, where the row not in vt2 will be inserted into vt2, and the remaining fields of the row will be filled with null to form VT3; The same is true if right join is specified. However, if an inner join is specified, no external row will be added, and the above insertion process is ignored, vt2 = VT3 (so there is no difference in the execution results when the filter conditions of the inner join are placed in on or where, which will be described in detail below)
  • Where: filter the VT3 table according to the where criteria to generate the vt4 table.
  • Select to retrieve the specified field of vt4 to vT5

give an 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;

Import some data:

user_info:

|userid  | name |
| -------- | -------- |
| 10001   | x | 
| 10002   | y | 
| 10003   | z | 
| 10004   | a | 
| 10005   | b | 
| 10006   | c | 
| 10007   | d | 
| 10008   | e | 

user_account:

| userid | money |
|  --------  |  --------  |
|   1001 |    22 |
|   1002 |    30 |
|   1003 |     8 |
|   1009 |    11 |

A total of 8 users have user names, and the accounts of 4 users have balances.demand: find 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 and Cartesian product the two tables.

After the Cartesian product operation, the combination of all row pairs of the two tables will be returned, and the left table is user_ Info has 8 rows, and the right table is user_ Account has 4 rows, and the generated virtual table VT1 is 8 * 4 = 32 rows:

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

Step 2: execute on to remove the rows that do not meet the conditions and generate vt2 table:

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

Step 3: execute join to add external rows.

Left join traverses the left table user_ Info, insert all rows in the left table that do not appear in the vt2 table into the vt2 table, and the remaining fields of each row will be filled with null to generate the VT3 table. The same is true for right join.

| userid | name | userid | money |
|  --------  |  --------  | --------  |  --------  |
|  1001  | x |  1001  |  22  |
|  1002  | y |  1002  |  30  |
|  1003  | z |  1003  |  8  |
|  10004  | a | NULL | NULL |
|  10005  | b | NULL | NULL |
|  10006  | c | NULL | NULL |
|  10007  | d | NULL | NULL |
|  10008  | e | NULL | NULL |

Step 4: perform where condition userid = 1003 filtering to generate vt4 table.

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

Step 5: execute select to query specific fields. The generated vT5 will be returned as a result.

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

The difference between inner / left / right / full join.

Inner join... On...: return all the rows associated with the two tables. Do not execute the third join mentioned above to add external rows.
 Left join... On...: returns all rows in the left table. If some rows have no corresponding values in the right table, they will be filled with null.
 Right join... On...: returns all rows in the right table. If some rows have no corresponding values in the left table, they will be filled with null.

INNER JOIN

Take the third step aboveAdd external rowFor example, ifLEFT JOINreplace withINNER JOINAs like as two peas, the 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

ifLEFT JOINreplace withRIGHT 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 user_ There is a row userid = 1009 in account (right table), and user_ Info (left table), but the record of this row cannot be found, so the following row will be inserted in step 3:

| userid | name | userid | money |
|  --------   |  --------  |   --------   |  --------  |
|   NULL | NULL |   1009 |    11 |

We can see that there is no difference between left join and right join, so try to use left join.

Difference between on and where

For example:

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;

First caseLEFT JOINAfter executing the on clause in the second step, filter out the items that meet thei.userid = a.userid and i.userid = 1003Generate table vt2, and then execute the join clause in step 3 to add external rows into the virtual table to generate VT3, that 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 |

In the second caseLEFT JOINAfter executing the on clause in the second step, filter out the items that meet thei.userid = a.useridGenerate a table vt2 based on the row of; Then execute the third step to add external rows to the join clause to generate the table VT3; Then execute the where clause in step 4, and then filter the VT3 table to generate vt4. The final result is as follows:

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 the above exampleLEFT JOINreplace withINNER JOIN, regardless of the filter conditionONstillWHEREIn, the results are the same, becauseInner join does not perform the third step to add external rows

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;

The returned results are:

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

Original address

Original address link

This work adoptsCC agreement, reprint must indicate the author and the link to this article