当前位置: 首页>后端>正文

left join 不能触发索引 但是inner join 可以 left join没有on

一、背景

最近发现很多人对 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)数据:

left join 不能触发索引 但是inner join 可以 left join没有on,left join 不能触发索引 但是inner join 可以 left join没有on_SQL,第1张

选课表(course)数据:

left join 不能触发索引 但是inner join 可以 left join没有on,left join 不能触发索引 但是inner join 可以 left join没有on_数据库_02,第2张

某同学写出了这样的 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 的王五被筛选出来了!

left join 不能触发索引 但是inner join 可以 left join没有on,left join 不能触发索引 但是inner join 可以 left join没有on_数据_03,第3张

现实很残酷, 一个 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。

输出结果如下

left join 不能触发索引 但是inner join 可以 left join没有on,left join 不能触发索引 但是inner join 可以 left join没有on_数据_04,第4张


从结果中可以看出,李四没有选修任何课程,所以他们的课程名为 null。

需要注意的是,“如果右表中没有匹配的行,那么右表中的列将显示为null。” 匹配的条件就是 on 语句!

我们看下下面的 SQL 执行结果就明白了:

SELECT s.number,s.name,s.level,c.course FROM student s left join course c on 1=2

left join 不能触发索引 但是inner join 可以 left join没有on,left join 不能触发索引 但是inner join 可以 left join没有on_开发语言_05,第5张

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。

left join 不能触发索引 但是inner join 可以 left join没有on,left join 不能触发索引 但是inner join 可以 left join没有on_数据库_06,第6张

讲解一下 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 不能触发索引 但是inner join 可以 left join没有on,left join 不能触发索引 但是inner join 可以 left join没有on_开发语言_07,第7张

五、解决方案

通过本文的学习我们可知, 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';

两个方案执行结果相同,符合预期:

left join 不能触发索引 但是inner join 可以 left join没有on,left join 不能触发索引 但是inner join 可以 left join没有on_SQL_08,第8张

  • 方案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 甚至故障。


https://www.xamrdz.com/backend/33c1928837.html

相关文章: