什么？left join 的 on 条件失效了？查出了不符合条件的数据?

明明如月学长

发布于 2023-04-21 20:02:31

9440

发布于 2023-04-21 20:02:31

文章被收录于专栏：明明如月的技术专栏明明如月的技术专栏

一、背景

最近发现很多人对 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的学生，以及他们选修的课程（如果有的话）。

因此，这两个查询的结果是相同的，但是执行的顺序不同。第一个查询先筛选再连接，第二个查询先连接再筛选。一般来说，先筛选再连接的效率更高，因为可以减少连接操作的数据量。