MySQL中left join的几个SQL对比

jeanron100

发布于 2020-04-01 02:47:29

1K00

代码可运行

文章被收录于专栏：杨建荣的学习笔记杨建荣的学习笔记

运行总次数：0

代码可运行

读完需要9分钟

速读仅需7分钟

对于很多同学来说，写SQL时的表关联看起来是一件很简单的事情，知道逻辑，有预期的结果，好像没什么特别要注意的，今天在写一条SQL逻辑的时候，觉得对于left join的部分还是存在一些误解。

我举一个小例子来说明。

为了演示方便，我用两张表来说明，test1和test2.

初始化的部分如下：

create table test1(id int primary key,name varchar(30));
create table test2(id int primary key,name varchar(30));

写入一些简单的数据，其中name='bb'的部分需要注意。

insert into test1 values(1,'aa'),(2,'bb'),(3,'cc'),(4,'bb'),(5,'bb');
insert into test2 values(2,'bb'),(3,'cc'),(4,'bb'),(6,'dd');

表中的数据如下：

mysql> select *from test1;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
|  4 | bb   |
|  5 | bb   |
+----+------+
5 rows in set (0.00 sec)


mysql> select *from test2;
+----+------+
| id | name |
+----+------+
|  2 | bb   |
|  3 | cc   |
|  4 | bb   |
|  6 | dd   |
+----+------+
4 rows in set (0.00 sec)

我们写一个条基本的left join的SQL,这个时候我们使用name='bb'来作为过滤条件，id作为关联条件。

如果按照id为关联条件，最后使用了and的方式，查询的结果其实和本来的预期是有差距的，即test1显示了全部的数据记录。

mysql> select t1.id,t1.name from test1 t1 left join test2 t2 on t1.id=t2.id and t1.name='bb'; 
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
|  4 | bb   |
|  5 | bb   |
+----+------+
5 rows in set (0.00 sec)

如果我们把最后的on改为where ,情况就大大不同的，输出了name='bb'的3条记录。

mysql> select t1.id,t1.name from test1 t1 left join test2 t2 on t1.id=t2.id where  t1.name='bb'; 
+----+------+
| id | name |
+----+------+
|  2 | bb   |
|  4 | bb   |
|  5 | bb   |
+----+------+
3 rows in set (0.00 sec)

当然我们需要继续扩展下，开始摆弄这条SQL

基于name列来关联，name='bb'过滤，会发现这种情况看起来有些失控，输出结果是笛卡尔积。

mysql> select t1.id,t1.name from test1 t1 left join test2 t2 on t1.name=t2.name and t1.name='bb'; 
+----+------+
| id | name |
+----+------+
|  2 | bb   |
|  4 | bb   |
|  5 | bb   |
|  2 | bb   |
|  4 | bb   |
|  5 | bb   |
|  1 | aa   |
|  3 | cc   |
+----+------+
8 rows in set (0.00 sec)

如果把原本的on条件改为where,情况有所好转，不过还是对name='bb'的子集重新做了关联。

mysql> select t1.id,t1.name from test1 t1 left join test2 t2 on t1.name=t2.name where t1.name='bb';     
+----+------+
| id | name |
+----+------+
|  2 | bb   |
|  4 | bb   |
|  5 | bb   |
|  2 | bb   |
|  4 | bb   |
|  5 | bb   |
+----+------+
6 rows in set (0.00 sec)

所以在多表关联中的绑定字段是很重要的。

我们继续做多个字段的关联，看看优化器怎么解析，在where条件中再进行id列的映射。

mysql> select t1.id,t1.name from test1 t1 left join test2 t2 on t1.name=t2.name where t1.name='bb' and t1.id=t2.id;
+----+------+
| id | name |
+----+------+
|  2 | bb   |
|  4 | bb   |
+----+------+
2 rows in set (0.00 sec)

这个时候的效果是和inner join是类似的。

如果继续进行改造，改造为and的条件。

mysql>  select t1.id,t1.name from test1 t1 left join test2 t2 on t1.name=t2.name and t1.id=t2.id and t1.name='bb';
+----+------+
| id | name |
+----+------+
|  2 | bb   |
|  4 | bb   |
|  1 | aa   |
|  3 | cc   |
|  5 | bb   |
+----+------+
5 rows in set (0.00 sec)

继续改造。

mysql>  select t1.id,t1.name from test1 t1 left join test2 t2 on t1.name=t2.name and t1.id=t2.id where  t1.name='bb';
+----+------+
| id | name |
+----+------+
|  2 | bb   |
|  4 | bb   |
|  5 | bb   |
+----+------+
3 rows in set (0.00 sec)