一、背景
最近发现很多人对 left join 的 on 条件的作用有误解,导致出现 Bug。
由于很多人都不太注意这问题,很容易造成故障。
下面我们简单复现这个问题,并解释为什么会这样,希望大家日常开发中万分小心。
二、问题复现
大家可以在这里在线运行:http://mysql.jsrun.net/ 查看 SQL 效果。
我们创建两张表,并且插入一些数据。
CREATE DATABASE test;
use test;
CREATE TABLE student (
number int,
name varchar(255),
level varchar(255)
);
CREATE TABLE course (
number int,
course varchar(255)
);
INSERT INTO student VALUES (20, '张三','vip');
INSERT INTO student VALUES (23, '李四','vip');
INSERT INTO student VALUES (17, '王五','common');
INSERT INTO course VALUES (20, '计算机科学');
INSERT INTO course VALUES (20, '软件工程');
INSERT INTO course VALUES (17, '心理学');
学生表(student)数据:
选课表(course)数据:
某同学写出了这样的 SQL:
SELECT s.number,s.name,c.course FROM student s left join course c on s.number = c.number and s.level ='vip';
希望通过这个 SQL 查询出 VIP 的学生选课情况,理论上应该有 3 条数据。张三是 VIP 选了两门课,李四是 VIP 选了 0 门课。
结果事与愿违:并不是 vip 的王五被筛选出来了!
现实很残酷, 一个 Bug 迎面而来!!
三、背景知识
3.1 left join
left join 是一种连接操作,它会返回左表中的所有行,以及右表中与左表匹配的行。如果右表中没有匹配的行,那么右表中的列将显示为null。left join 也称为 left outer join。
我将使用上述两个表作为例子:
SELECT s.number,s.name,s.level,c.course FROM student s left join course c on s.number = c.number;
这个查询会返回 student 表中的所有行,以及 course 表中与 student 表匹配的行。如果 course 表中没有匹配的行,那么 student 的 course 将显示为null。
输出结果如下
:
从结果中可以看出,李四没有选修任何课程,所以他们的课程名为 null。
需要注意的是,“如果右表中没有匹配的行,那么右表中的列将显示为null。” 匹配的条件就是 on 语句!
我们看下下面的 SQL 执行结果就明白了:
SELECT s.number,s.name,s.level,c.course FROM student s left join course c on 1=2
3.2 where 和 on 的区别
- where 条件是用来过滤数据的,它会在连接操作完成后,筛选出满足条件的行。where 条件可以应用于任何列,不一定是连接列。
- on 条件是用来定义连接条件的,它会在连接操作进行时,指定两个表中哪些行是匹配的。on 条件只能应用于连接列。
四、语句分析
再来回看有问题的 SQL:
SELECT s.number,s.name,s.level,c.course FROM student s left join user u on s.number = u.number and s.level ='vip';
根据上述基础知识,我们可知这个查询会返回 student 表中的所有行,以及 course 表中与 student 表匹配(满足, s.number = u.number and s.level ='vip'
)的行。如果 course 表中没有匹配的行,那么 student 的 course 将显示为null。
由于 李四没有选修任何课程,所以他们的课程名为 null。由于 course 取自选课表,而王五这条数据不满足 s.level ='vip'
所以,王五的 course 也为 null。
讲解一下 SELECT * FROM student s left join user u on s.number = u.number ;和 SELECT * FROM student s left join user u on s.number = u.number and s.level =‘vip’;的区别
五、解决方案
通过本文的学习我们可知, left join
中的 on
仅作用于判断是否匹配的判断,并不是作为数据的筛选条件。
那么如何解决?
其实很简单,将过滤条件放在 where 条件中!!
方案1:
SELECT s.number,s.name,s.level,c.course FROM (select * from student where level ='vip') as s left join course c on s.number = c.number ;
方案2:
SELECT s.number,s.name,s.level,c.course FROM student s left join course c on s.number = c.number where s.level ='vip';
两个方案执行结果相同,符合预期:
- 方案1 查询是一个左连接,它会先从student表中筛选出level为vip的行,然后将这些行作为一个临时表s与course表进行连接。连接条件是s.number = c.number,即学生编号相等。这个查询会返回所有level为vip的学生,以及他们选修的课程(如果有的话)。
- 方案2 也是一个左连接,它会先将 student 表和 course 表进行连接,连接条件也是 s.number = c.number。然后,它会在where子句中筛选出level为vip的行。这个查询也会返回所有level为vip的学生,以及他们选修的课程(如果有的话)。
因此,这两个查询的结果是相同的,但是执行的顺序不同。第一个查询先筛选再连接,第二个查询先连接再筛选。一般来说,先筛选再连接的效率更高,因为可以减少连接操作的数据量。
六、总结
所谓“基础不牢,地动山摇!”。
大家学习 SQL 时,一定要认真消化每一个概念,勤动手实践和验证,才能保证写出符合预期的 SQL。
大家以后在写 join 语句时,一定要注意 on 和 where 的区别,避免出现不必要的 BUG 甚至故障。