开发者社区> sansi_dba> 正文

正确理解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 条件放在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)

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

可以看到,我们使用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的执行效率。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
使用SSH远程登录阿里云ECS服务器
远程连接服务器以及配置环境
11611 0
如何设置阿里云服务器安全组?阿里云安全组规则详细解说
阿里云安全组设置详细图文教程(收藏起来) 阿里云服务器安全组设置规则分享,阿里云服务器安全组如何放行端口设置教程。阿里云会要求客户设置安全组,如果不设置,阿里云会指定默认的安全组。那么,这个安全组是什么呢?顾名思义,就是为了服务器安全设置的。安全组其实就是一个虚拟的防火墙,可以让用户从端口、IP的维度来筛选对应服务器的访问者,从而形成一个云上的安全域。
16820 0
阿里云服务器怎么设置密码?怎么停机?怎么重启服务器?
如果在创建实例时没有设置密码,或者密码丢失,您可以在控制台上重新设置实例的登录密码。本文仅描述如何在 ECS 管理控制台上修改实例登录密码。
19111 0
windows server 2008阿里云ECS服务器安全设置
最近我们Sinesafe安全公司在为客户使用阿里云ecs服务器做安全的过程中,发现服务器基础安全性都没有做。为了为站长们提供更加有效的安全基础解决方案,我们Sinesafe将对阿里云服务器win2008 系统进行基础安全部署实战过程! 比较重要的几部分 1.
11594 0
使用NAT网关轻松为单台云服务器设置多个公网IP
在应用中,有时会遇到用户询问如何使单台云服务器具备多个公网IP的问题。 具体如何操作呢,有了NAT网关这个也不是难题。
34110 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,阿里云优惠总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系.
23936 0
阿里云ECS云服务器初始化设置教程方法
阿里云ECS云服务器初始化是指将云服务器系统恢复到最初状态的过程,阿里云的服务器初始化是通过更换系统盘来实现的,是免费的,阿里云百科网分享服务器初始化教程: 服务器初始化教程方法 本文的服务器初始化是指将ECS云服务器系统恢复到最初状态,服务器中的数据也会被清空,所以初始化之前一定要先备份好。
14095 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,云吞铺子总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系统盘、创建快照、配置安全组等操作如何登录ECS云服务器控制台? 1、先登录到阿里云ECS服务器控制台 2、点击顶部的“控制台” 3、通过左侧栏,切换到“云服务器ECS”即可,如下图所示 通过ECS控制台的远程连接来登录到云服务器 阿里云ECS云服务器自带远程连接功能,使用该功能可以登录到云服务器,简单且方便,如下图:点击“远程连接”,第一次连接会自动生成6位数字密码,输入密码即可登录到云服务器上。
32576 0
+关注
17
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
OceanBase 入门到实战教程
立即下载
阿里云图数据库GDB,加速开启“图智”未来.ppt
立即下载
实时数仓Hologres技术实战一本通2.0版(下)
立即下载