SQL连接与筛选：解析left join on和where的区别及典型案例分析

前言

作为一名Java（CRUD）开发工程师，与数据库打交道的时间可不少，我们在编写SQL语句时，经常会用到left join on和where条件来过滤数据，对于初学者来说，都会遇到一个经典问题：left join on后面的条件和where条件的区别到底是什么？

还记得在刚工作那会，我就写过2篇针对left join on相关的文章，最近又看了下之前的文章，发现又有了新的体会，

注意区分left join on 后面的条件 和where 后面的条件------这篇有点low了
测试一下你真的理解数据库左连接了吗？--------这篇说实话可以结合我写的例子看看你自己是否掌握了数据库的left join on左连接

本篇文章我将深入探讨left join on后面条件和where条件的异同，并通过实际SQL案例进行详细解析，帮助大家理解和应用这两个关键的查询语句。

数据库在运行时的执行顺序

这是数据库真正在运行时的底层执行顺序，必须要记住，这有利于我们写出更好的SQL，同时也可以发现on和where作用的时机也不一样！！！

(8)SELECT (9) DISTINCT (11)< Top Num> < select list>
(1) FROM [left_table]
(3)<join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) WITH <CUBE | RollUP>
(7) HAVING <having_condition>
(10) ORDER BY <order_by_list>

一、left join on和where条件的定义和作用

left join on条件

在SQL语句中，left join on条件是用来指定两个表之间相关字段的连接方式。它用于联结两个表，返回左表中所有的记录以及符合连接条件的右表记录。 left join on条件通常结合on子句使用，格式如下：

SELECT 列名
FROM 表名1
LEFT JOIN 表名2
ON 表名1.列名 = 表名2.列名

where条件

where条件则是用于过滤记录的条件语句。它用于对查询结果进行进一步筛选，只选择满足条件的记录。where条件可以组合多个条件，使用逻辑运算符（如AND、OR）来连接。格式如下：

SELECT 列名
FROM 表名
WHERE 条件1 AND 条件2 ...

二、left join on和where条件的区别

原理不同

• on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。

• where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

具体一步步分析一下：

left join原理：

1. 遍历左表中的每一条记录。
2. 对于左表中的每一条记录，尝试在右表中找到匹配的记录（注意如果右边找到多条，左表重复展示，右边分别展示对应的数据），匹配条件由ON子句指定。
3. 如果找到匹配的记录，则将这两条记录合并成一条新记录，并添加到结果集中。
4. 如果未找到匹配的记录，则将左表的记录与NULL值合并成一条新记录，并添加到结果集中。

特别需要注意上面第2点，遍历过程中如果右边找到多条，左表重复展示，右边分别展示对应的数据，下面会有例子帮助理解

where原理：

1. 从表中检索出所有记录。
2. 对每一条记录应用WHERE子句中的条件表达式。
3. 如果记录满足条件表达式，则将其包含在结果集中。
4. 如果记录不满足条件表达式，则将其排除在结果集之外。

• 这里也可以注意第2点，对于每一条记录都应用WHERE子句中的条件进行过滤，也就是如果不符合就是左边和右边都不会显示，
• 而上面left join on是对于不符合的会将左表记录与NULL值合并成一条记录添加到结果集,也就是左表记录数据一定会展示出来！

应用场景不同

• left join on常用于连接两个或多个表，并显示左表中所有记录以及匹配的右表记录。它适用于需要获取连接表间所有数据的场景。

• 而where条件则是用于在查询结果上进行进一步的筛选和限定，通常是基于某些列的具体值或范围进行选择。

执行顺序不同（作用阶段不同）

• left join on是在连接过程中确定两个表之间的连接条件，并将满足条件的记录组合在一起。也就是LEFT JOIN 发生在数据连接阶段！

• 而where条件是在连接完成后对结果集进行筛选和过滤。也就是WHERE 条件发生在数据筛选阶段！

结果集不同

• left join on会返回左表的所有记录以及符合连接条件的右表记录，即使条件不满足也会返回左表的数据。

• 而where条件只会返回满足条件的记录。

三、实际案例理解left join on和where条件

为了更深入地理解left join on和where条件的区别，让我们来看一个实际案例。

建表

注意这里的建表语句跟我之前文章的测试一下你真的理解数据库左连接了吗？一模一样，可以两篇一起理解看看！

假设我们有两张表：大学学生和大学选修班级来模拟大学生选修课程，。其中选修班级包括选修班级名称cname ，是否删除is_delete，学生表包括学生的姓名name、班级class_id、以及选修班级的课程（这里采用hobby字段标识），

选修班级表t_class如下：

• 注意这里Java课程有2条记录，其中一条是is_delete=1已经逻辑删除了，其中一条是正常启用的

DROP TABLE IF EXISTS `t_class`;
CREATE TABLE `t_class`
(`cid`       int(11)       NOT NULL AUTO_INCREMENT,`cname`     varchar(255)           DEFAULT NULL comment '选修班级名称',`is_delete` smallint(255) NOT NULL DEFAULT 0 comment '是否逻辑删除',PRIMARY KEY (`cid`) USING BTREE
);-- ----------------------------
-- Records of t_class
-- ----------------------------
INSERT INTO `t_class` VALUES (1, 'Java', 0);
INSERT INTO `t_class` VALUES (2, 'Python', 0);
INSERT INTO `t_class` VALUES (3, 'C语言', 0);
INSERT INTO `t_class` VALUES (4, 'Java', 1);

cid	cname	is_delete
1	Java	0
2	Python	0
3	C语言	0
4	Java	1

学生信息t_student如下：

• 注意这里采用hobby字段代表学生选修班级的名称，正常这里用选修班级id关联就可以了，有时候我们为了查询方便会多冗余一下别的字段

DROP TABLE IF EXISTS `t_student`;
CREATE TABLE `t_student`
(`id`       int(11) NOT NULL AUTO_INCREMENT,`name`     varchar(255) DEFAULT NULL comment '学生姓名',`class_id` int(11) NULL DEFAULT NULL comment '选修班级id',`hobby`    varchar(255) DEFAULT NULL comment '选修班级名称',PRIMARY KEY (`id`) USING BTREE
);
-- ----------------------------
-- Records of t_student
-- ----------------------------
INSERT INTO `t_student` VALUES (1, '小王', 1, 'Python');
INSERT INTO `t_student` VALUES (2, '小红', 2, 'Java');
INSERT INTO `t_student` VALUES (3, '小明', 3, 'C语言');
INSERT INTO `t_student` VALUES (4, '小李', 4, 'Java');

id	name	class_id	hobby
1	小王	1	Python
2	小红	2	Java
3	小明	3	C语言
4	小李	4	Java

案例实操

现在我们想查询每个学生以及他们的选修课程，即使没有选修课程记录，也要显示学生信息。

使用left join on的查询语句：

常规案例-on主外键

正常的我们用学生表的class_id关联课程表的cid即可

select * from t_student s left join t_class c on  s.class_id=c.cid

id	name	class_id	hobby	cid	cname	is_delete
1	小王	1	Python	1	Java	0
2	小红	2	Java	2	Python	0
3	小明	3	C语言	3	C语言	0
4	小李	4	Java	4	Java	1

这种是我们最常见的例子，接下来我们看看另外一种变形

变形1-on其他列

如果我们想用想学生表的选修班级名字去关联班级名称呢？SQL也很简单，但是对应的结果大家可以好好也猜一下

select * from t_student s left join t_class c on  s.hobby=c.cname

id	name	class_id	hobby	cid	cname	is_delete
1	小王	1	Python	2	Python	0
2	小红	2	Java	1	Java	0
2	小红	2	Java	4	Java	1
3	小明	3	C语言	3	C语言	0
4	小李	4	Java	1	Java	0
4	小李	4	Java	4	Java	1

我们可以发现关联出来的小红和小李他们的记录会有2行，这是为什么？有注意到我们刚刚上面提到的left join on的原理执行步骤吗：

1. 遍历左表中的每一条记录。
2. 对于左表中的每一条记录，尝试在右表中找到匹配的记录（注意如果右边找到多条，左表重复展示，右边分别展示对应的数据），匹配条件由ON子句指定。
3. 如果找到匹配的记录，则将这两条记录合并成一条新记录，并添加到结果集中。
4. 如果未找到匹配的记录，则将左表的记录与NULL值合并成一条新记录，并添加到结果集中。

注意到上面第2点，遍历过程中如果右边找到多条，左表重复展示，右边分别展示对应的数据，对应这个例子就是小红选修的课程有2条记录，其中一条是正常启用的，一条是废弃删除的

变形2-on多个条件

假如现在我们想查询每个学生以及他们选修了Java课程的，即使没有选修Java课程记录，也要显示学生信息。

select * from t_student s left join t_class c on  s.hobby=c.cname and s.hobby='Python'

id	name	class_id	hobby	cid	cname	is_delete
1	小王	1	Python	2	Python	0
2	小红	2	Java
3	小明	3	C语言
4	小李	4	Java

我们先把条件拆解，然后按着上面这个步骤一步步来
第1步执行完select * from t_student s left join t_class c on s.hobby=c.cname 就是上面的结果

id	name	class_id	hobby	cid	cname	is_delete
1	小王	1	Python	2	Python	0
2	小红	2	Java	1	Java	0
2	小红	2	Java	4	Java	1
3	小明	3	C语言	3	C语言	0
4	小李	4	Java	1	Java	0
4	小李	4	Java	4	Java	1

接着我们再看on的另外一条件 s.hobby='Python'，同时结合这两个步骤

• 如果找到匹配的记录，则将这两条记录合并成一条新记录，并添加到结果集中。
• 如果未找到匹配的记录，则将左表的记录与NULL值合并成一条新记录，并添加到结果集中。

所以我们就是在上面的表格上进行过滤，只保留cname='Python’的班级，对于找不到的班级，就用NULL与左表合并显示，所以我们的左边的记录一定是完整的！

最终得到的结果就应该班级表只有Python信息，但是左表学生全部都会查询展示信息

id	name	class_id	hobby	cid	cname	is_delete
1	小王	1	Python	2	Python	0
2	小红	2	Java
3	小明	3	C语言
4	小李	4	Java

同理，如果改成select * from t_student s left join t_class c on s.hobby=c.cname and s.hobby='Python'呢？如果真的理解了应该很容易想出答案！

id	name	class_id	hobby	cid	cname	is_delete
1	小王	1	Python
2	小红	2	Java	1	Java	0
2	小红	2	Java	4	Java	1
3	小明	3	C语言
4	小李	4	Java	1	Java	0
4	小李	4	Java	4	Java	1

~变态变形3-on无关联字段

前面的3个案例都是t_class和t_student有关联字段将两者关联在一起了，那么如果我没关联字段，阁下又当如何应对？

这个案例是我在文章测试一下你真的理解数据库左连接了吗？的最后一个例子，结果比较奇葩，正常也不会有这样关联的，大家可以蛮看理解一下！

select * from t_student s left join t_class c on s.hobby='Python'

结果如下：

id	name	class_id	hobby	cid	cname	is_delete
1	小王	1	Python	1	Java	0
1	小王	1	Python	2	Python	0
1	小王	1	Python	3	C语言	0
1	小王	1	Python	4	Java	1
2	小红	2	Java
3	小明	3	C语言
4	小李	4	Java

关键在于 left join 的条件 s.hobby = 'Python'。由于这个条件与 t_class 表无关，它实际上会导致一个笛卡尔积，然后根据这个条件来进行筛选

这个查询实际上会对 t_student 表中的每一行进行左连接，但 left join 的条件是 s.hobby = 'Python'。因为这个条件与 t_class 表无关，所有的行都会被保留，t_class 表的列会根据条件 s.hobby = 'Python' 来填充。

• 对于 id=1 这一行，hobby 是 'Python'，所以会与 t_class 表的所有行进行左连接，因为 hobby = 'Python' 的条件总是成立。
• 对于 id=2, id=3, 和 id=4 的行，hobby 分别是 'Java' 和 'C语言'，所以 t_class 表的所有列都是 NULL。

总结一下就是：查询的结果是对于每一个 t_student 表的行，如果 hobby == 'Python'，则会与 t_class 表的所有行进行连接，否则 t_class 表的所有列都是 NULL。结果中包含了所有 t_student 表的行，并且根据 hobby 是否为 'Python' 来决定 t_class 表的列是否填充。

使用where条件的查询语句：

我们看看将上面变形2的on的另外一个条件放在where是怎样的？

select * from t_student s left join t_class c on  s.hobby=c.cname where s.hobby='Python'

先再看下上面的where原理执行步骤：

1. 从表中检索出所有记录。
2. 对每一条记录应用WHERE子句中的条件表达式。
3. 如果记录满足条件表达式，则将其包含在结果集中。
4. 如果记录不满足条件表达式，则将其排除在结果集之外。

• 这里特别注意第2点，对于每一条记录都应用WHERE子句中的条件进行过滤，也就是如果不符合就是左边和右边都不会显示，
• 而上面LEFT JOIN ON是对于不符合的会将左表记录与NULL值合并成一条记录添加到结果集,也就是左表记录数据一定会展示出来！

执行结果如下：也很好理解对于where里面符合的条件s.hobby='Python'会正常展示一行，而不符合的就直接整行不展示，而不是保留左边值，右边显示NULL

id	name	class_id	hobby	cid	cname	is_delete
1	小王	1	Python	2	Python	0

而对于把条件过滤改成Java，执行结果应该也能很好理解了

select * from t_student s left join t_class c on  s.hobby=c.cname where s.hobby='Java'

id	name	class_id	hobby	cid	cname	is_delete
2	小红	2	Java	1	Java	0
4	小李	4	Java	1	Java	0
2	小红	2	Java	4	Java	1
4	小李	4	Java	4	Java	1

总结：

• 通过对比上述left join on 和where两个查询语句，我们可以发现在使用left join on时，即使没有选修课程记录，学生的信息也会显示出来，而在使用where条件时，要是选修课程的条件不满足，就直接整行不显示了，区别还是很大的！
• left join on主要用于连接表和显示所有记录，而where条件则用于对结果进行进一步筛选。平常工作中我们一定要理解这两者的区别和原理，才能正确地用好这两个关键的查询语句！

TODO后续

日常工作中写SQL，我们会经常left join一堆表，我最多见过的有20多张的。。。（在一家外企，然后还用了一堆存储过程有几千行的，吐了。。。）那么对于left join on和前面表关联条件的时候，有的时候是紧挨着的表，有的是隔开的，有啥区别呢？具体看看这个

a 表主键是aid，b表是bid，c表是cid，以下两个语句有什么区别？如何理解？
篇幅有限。详见下文~

select * from  a left join b on a.aid=b.aid  left join c on b.bid=c.bidselect * from  a left join b on a.aid=b.aid  left join c on a.aid=c.aid