这是学习笔记的第 2210 篇文章
读完需要
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)
QQ群号:763628645
QQ群二维码如下, 添加请注明:姓名+地区+职位,否则不予通过
订阅我的微信公众号“杨建荣的学习笔记”,第一时间免费收到文章更新。别忘了加星标,以免错过新推送提示。
7
近期热文
你可能也会对以下话题感兴趣。点击链接就可以查看。
8
转载热文
你可能也会对以下话题感兴趣,文章来源于转载,点击链接就可以查看。