正确理解left join

通俗来讲，left join就是以左表作为主表，结果返回左表的所有记录，右表满足条件记录正常显示，满足条件记录使用NULL做填充，一般业务中我们需要显示左表全部记录时才会使用left join。另外，某些情况下MySQL优化器会将我们的left join改写为join，什么情况下MySQL会做这样的优化？

1.1 测试数据

root@mysql 11:24:  [db1]> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `uname` varchar(10) DEFAULT NULL,
  `is_delete` int(11) DEFAULT '0',
  PRIMARY KEY (`uid`),
  KEY `idx_uname` (`uname`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

root@mysql 11:24:  [db1]> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uname` varchar(10) DEFAULT NULL,
  `uage` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_uname` (`uname`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

root@mysql 11:24:  [db1]> select * from t1;
+-----+-------+-----------+
| uid | uname | is_delete |
+-----+-------+-----------+
|   1 | aa    |         0 |
|   2 | bb    |         0 |
|   3 | cc    |         0 |
|   4 | dd    |         0 |
|   5 | ee    |         0 |
+-----+-------+-----------+
5 rows in set (0.00 sec)

root@mysql 11:24:  [db1]> select * from t2;
+----+-------+------+
| id | uname | uage |
+----+-------+------+
|  1 | aa    | 12   |
|  2 | aa    | 13   |
|  5 | cc    | 12   |
+----+-------+------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

1.2 条件放在on和where之前的区别

1、从结果显示来看

从结果显示来看两者是完全不同的：

1）当把过滤条件写在and上时，返回结果集中会显示左表全部记录，右表满足条件的记录正常显示，不满足条件的记录显示为NULL，是我们通俗理解上left join应该显示的结果；

2）而当把过滤条件写在where上时，虽然SQL中我们使用了left join去做表关联，但是实际结果集中并不是我们想要的返回，结果只是返回了满足条件的所有记录。

root@mysql 11:24:  [db1]>  select * from t1 left join t2 on t1.uname=t2.uname and t2.uage>12;
+-----+-------+-----------+------+-------+------+
| uid | uname | is_delete | id   | uname | uage |
+-----+-------+-----------+------+-------+------+
|   1 | aa    |         0 |    2 | aa    | 13   |
|   2 | bb    |         0 | NULL | NULL  | NULL |
|   3 | cc    |         0 | NULL | NULL  | NULL |
|   4 | dd    |         0 | NULL | NULL  | NULL |
|   5 | ee    |         0 | NULL | NULL  | NULL |
+-----+-------+-----------+------+-------+------+
5 rows in set (0.00 sec)

root@mysql 11:25:  [db1]>  select * from t1 left join t2 on t1.uname=t2.uname where t2.uage>12;
+-----+-------+-----------+------+-------+------+
| uid | uname | is_delete | id   | uname | uage |
+-----+-------+-----------+------+-------+------+
|   1 | aa    |         0 |    2 | aa    | 13   |
+-----+-------+-----------+------+-------+------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

2、从执行计划来看

1）当把过滤条件写在and上时，执行计划没有做过多的改写，左表t1作为驱动表与t2进行关联查询；

2）当把过滤条件写在where上时，我们发现MySQL对原SQL进行了改写，最重要的一点是将left join改写为join，这个动作就导致SQL在执行计划中会优先选择小表作为驱动表，而并不一定是左表t1作为驱动表。

root@mysql 11:26:  [db1]> explain  select * from t1 left join t2 on t1.uname=t2.uname and t2.uage>12;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | NULL                                               |
|  1 | SIMPLE      | t2    | NULL       | ALL  | idx_uname     | NULL | NULL    | NULL |    3 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

root@mysql 11:26:  [db1]> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `db1`.`t1`.`uid` AS `uid`,`db1`.`t1`.`uname` AS `uname`,`db1`.`t1`.`is_delete` AS `is_delete`,`db1`.`t2`.`id` AS `id`,`db1`.`t2`.`uname` AS `uname`,`db1`.`t2`.`uage` AS `uage` from `db1`.`t1` left join `db1`.`t2` on(((`db1`.`t2`.`uname` = `db1`.`t1`.`uname`) and (`db1`.`t2`.`uage` > 12))) where 1
1 row in set (0.00 sec)

root@mysql 11:26:  [db1]>
root@mysql 11:26:  [db1]>
root@mysql 11:26:  [db1]> explain  select * from t1 left join t2 on t1.uname=t2.uname where t2.uage>12;
+----+-------------+-------+------------+------+---------------+-----------+---------+--------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref          | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-----------+---------+--------------+------+----------+-------------+
|  1 | SIMPLE      | t2    | NULL       | ALL  | idx_uname     | NULL      | NULL    | NULL         |    3 |    33.33 | Using where |
|  1 | SIMPLE      | t1    | NULL       | ref  | idx_uname     | idx_uname | 43      | db1.t2.uname |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+------+---------------+-----------+---------+--------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

root@mysql 11:26:  [db1]> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `db1`.`t1`.`uid` AS `uid`,`db1`.`t1`.`uname` AS `uname`,`db1`.`t1`.`is_delete` AS `is_delete`,`db1`.`t2`.`id` AS `id`,`db1`.`t2`.`uname` AS `uname`,`db1`.`t2`.`uage` AS `uage` from `db1`.`t1` join `db1`.`t2` where ((`db1`.`t1`.`uname` = `db1`.`t2`.`uname`) and (`db1`.`t2`.`uage` > 12))
1 row in set (0.00 sec)


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

1.3 left join … where … is null 的使用

基于以上的案例，我们可以得出以下结论：

1）left join会返回左表所有记录，右表满足过滤条件记录正常反馈，不满足记录返回NULL处理，join只会返回两表均满足过滤条件的记录

2）left join把过滤条件写在on条件上时才是我们通俗理解上的left join，而left join中将表过滤条件写在where上时，MySQL会把left join改写为join。

3）left join关联查询时，表的驱动顺序是确定的，左表作为驱动表与右表进行关联查询，但是若MySQL优化器将left join改写为join的情况下，MySQL就会优先选择小表作为驱动表进行关联查询，一定程度上提升了SQL的执行效率。

但是，对于第一/二条，将过滤条件放在where条件上的时候，MySQL优化器就一定会将left join 改写为join吗？left join一定会返回左表全部记录吗？答案显然是“不一定的”，以下就为大家展示一个特例。

root@mysql 11:42:  [db1]> select * from t1 left join t2 on t1.uname=t2.uname;
+-----+-------+-----------+------+-------+------+
| uid | uname | is_delete | id   | uname | uage |
+-----+-------+-----------+------+-------+------+
|   1 | aa    |         0 |    1 | aa    | 12   |
|   1 | aa    |         0 |    2 | aa    | 13   |
|   3 | cc    |         0 |    5 | cc    | 12   |
|   2 | bb    |         0 | NULL | NULL  | NULL |
|   4 | dd    |         0 | NULL | NULL  | NULL |
|   5 | ee    |         0 | NULL | NULL  | NULL |
+-----+-------+-----------+------+-------+------+
6 rows in set (0.00 sec)

root@mysql 11:41:  [db1]> explain  select * from t1 left join t2 on t1.uname=t2.uname where t2.uage is null;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | NULL                                               |
|  1 | SIMPLE      | t2    | NULL       | ALL  | idx_uname     | NULL | NULL    | NULL |    3 |    33.33 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

root@mysql 11:41:  [db1]> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `db1`.`t1`.`uid` AS `uid`,`db1`.`t1`.`uname` AS `uname`,`db1`.`t1`.`is_delete` AS `is_delete`,`db1`.`t2`.`id` AS `id`,`db1`.`t2`.`uname` AS `uname`,`db1`.`t2`.`uage` AS `uage` from `db1`.`t1` left join `db1`.`t2` on((`db1`.`t2`.`uname` = `db1`.`t1`.`uname`)) where isnull(`db1`.`t2`.`uage`)
1 row in set (0.00 sec)

root@mysql 11:41:  [db1]> select * from t1 left join t2 on t1.uname=t2.uname where t2.uage is null;
+-----+-------+-----------+------+-------+------+
| uid | uname | is_delete | id   | uname | uage |
+-----+-------+-----------+------+-------+------+
|   2 | bb    |         0 | NULL | NULL  | NULL |
|   4 | dd    |         0 | NULL | NULL  | NULL |
|   5 | ee    |         0 | NULL | NULL  | NULL |
+-----+-------+-----------+------+-------+------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

可以看到，我们使用left join，但是where 过滤条件是右表某些字段is null的查询时。首先从执行计划来看，MySQL优化器并没有将left join改写为join；然后从结果返回来看，可以看到该SQL仅仅返回了满足过滤条件的记录，并没有返回左表全部记录。

对于left join，过滤条件是右表某字段is null的情况是一个特例，而且这种写法经常被DBA同学用来做业务上一些not in/not exists的改写优化。

1.4 结论

1）left join会返回左表所有记录，右表满足过滤条件记录正常反馈，不满足记录返回NULL处理，join只会返回两表均满足过滤条件的记录

2）left join把过滤条件写在on条件上时才是我们通俗理解上的left join，而left join中将表过滤条件写在where上时，MySQL会把left join改写为join。

3）left join且where过滤条件为右表某字段is null时属于一个特例，该情况下MySQL不会将left join改写为join，从而其表关联查询的顺序也只能是左表作为驱动表与右表进行关联查询。

4）left join关联查询时，表的驱动顺序是确定的，左表作为驱动表与右表进行关联查询，但是若MySQL优化器将left join改写为join的情况下，MySQL就会优先选择小表作为驱动表进行关联查询，一定程度上提升了SQL的执行效率。