一个网络空间如何做两个网站,个人如何注册网址,企业免费网站注册,怎样修改网站标题文章目录 前言数据库在运行时的执行顺序一、left join on和where条件的定义和作用left join on条件where条件 二、left join on和where条件的区别原理不同left join原理#xff1a;where原理#xff1a; 应用场景不同执行顺序不同#xff08;作用阶段不同#xff09;结果集… 文章目录 前言数据库在运行时的执行顺序一、left join on和where条件的定义和作用left join on条件where条件 二、left join on和where条件的区别原理不同left join原理where原理 应用场景不同执行顺序不同作用阶段不同结果集不同 三、实际案例理解left join on和where条件建表案例实操使用left join on的查询语句常规案例-on主外键变形1-on其他列变形2-on多个条件~变态变形3-on无关联字段 使用where条件的查询语句 总结 TODO后续 前言
作为一名JavaCRUD开发工程师与数据库打交道的时间可不少我们在编写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原理
遍历左表中的每一条记录。对于左表中的每一条记录尝试在右表中找到匹配的记录注意如果右边找到多条左表重复展示右边分别展示对应的数据匹配条件由ON子句指定。如果找到匹配的记录则将这两条记录合并成一条新记录并添加到结果集中。如果未找到匹配的记录则将左表的记录与NULL值合并成一条新记录并添加到结果集中。 特别需要注意上面第2点遍历过程中如果右边找到多条左表重复展示右边分别展示对应的数据下面会有例子帮助理解 where原理
从表中检索出所有记录。对每一条记录应用WHERE子句中的条件表达式。如果记录满足条件表达式则将其包含在结果集中。如果记录不满足条件表达式则将其排除在结果集之外。 这里也可以注意第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_delete1已经逻辑删除了其中一条是正常启用的
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);cidcnameis_delete1Java02Python03C语言04Java1
学生信息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);idnameclass_idhobby1小王1Python2小红2Java3小明3C语言4小李4Java
案例实操
现在我们想查询每个学生以及他们的选修课程即使没有选修课程记录也要显示学生信息。
使用left join on的查询语句
常规案例-on主外键 正常的我们用学生表的class_id关联课程表的cid即可 select * from t_student s left join t_class c on s.class_idc.cididnameclass_idhobbycidcnameis_delete1小王1Python1Java02小红2Java2Python03小明3C语言3C语言04小李4Java4Java1
这种是我们最常见的例子接下来我们看看另外一种变形
变形1-on其他列 如果我们想用想学生表的选修班级名字去关联班级名称呢SQL也很简单但是对应的结果大家可以好好也猜一下 select * from t_student s left join t_class c on s.hobbyc.cnameidnameclass_idhobbycidcnameis_delete1小王1Python2Python02小红2Java1Java02小红2Java4Java13小明3C语言3C语言04小李4Java1Java04小李4Java4Java1
我们可以发现关联出来的小红和小李他们的记录会有2行这是为什么有注意到我们刚刚上面提到的left join on的原理执行步骤吗
遍历左表中的每一条记录。对于左表中的每一条记录尝试在右表中找到匹配的记录注意如果右边找到多条左表重复展示右边分别展示对应的数据匹配条件由ON子句指定。如果找到匹配的记录则将这两条记录合并成一条新记录并添加到结果集中。如果未找到匹配的记录则将左表的记录与NULL值合并成一条新记录并添加到结果集中。
注意到上面第2点遍历过程中如果右边找到多条左表重复展示右边分别展示对应的数据对应这个例子就是小红选修的课程有2条记录其中一条是正常启用的一条是废弃删除的
变形2-on多个条件 假如现在我们想查询每个学生以及他们选修了Java课程的即使没有选修Java课程记录也要显示学生信息。 select * from t_student s left join t_class c on s.hobbyc.cname and s.hobbyPythonidnameclass_idhobbycidcnameis_delete1小王1Python2Python02小红2Java3小明3C语言4小李4Java
我们先把条件拆解然后按着上面这个步骤一步步来 第1步执行完select * from t_student s left join t_class c on s.hobbyc.cname 就是上面的结果
idnameclass_idhobbycidcnameis_delete1小王1Python2Python02小红2Java1Java02小红2Java4Java13小明3C语言3C语言04小李4Java1Java04小李4Java4Java1
接着我们再看on的另外一条件 s.hobbyPython同时结合这两个步骤
如果找到匹配的记录则将这两条记录合并成一条新记录并添加到结果集中。如果未找到匹配的记录则将左表的记录与NULL值合并成一条新记录并添加到结果集中。
所以我们就是在上面的表格上进行过滤只保留cnamePython’的班级对于找不到的班级就用NULL与左表合并显示所以我们的左边的记录一定是完整的
最终得到的结果就应该班级表只有Python信息但是左表学生全部都会查询展示信息
idnameclass_idhobbycidcnameis_delete1小王1Python2Python02小红2Java3小明3C语言4小李4Java 同理如果改成select * from t_student s left join t_class c on s.hobbyc.cname and s.hobbyPython呢如果真的理解了应该很容易想出答案 idnameclass_idhobbycidcnameis_delete1小王1Python2小红2Java1Java02小红2Java4Java13小明3C语言4小李4Java1Java04小李4Java4Java1
~变态变形3-on无关联字段 前面的3个案例都是t_class和t_student有关联字段将两者关联在一起了那么如果我没关联字段阁下又当如何应对 这个案例是我在文章测试一下你真的理解数据库左连接了吗的最后一个例子结果比较奇葩正常也不会有这样关联的大家可以蛮看理解一下
select * from t_student s left join t_class c on s.hobbyPython结果如下
idnameclass_idhobbycidcnameis_delete1小王1Python1Java01小王1Python2Python01小王1Python3C语言01小王1Python4Java12小红2Java3小明3C语言4小李4Java
关键在于 left join 的条件 s.hobby Python。由于这个条件与 t_class 表无关它实际上会导致一个笛卡尔积然后根据这个条件来进行筛选
这个查询实际上会对 t_student 表中的每一行进行左连接但 left join 的条件是 s.hobby Python。因为这个条件与 t_class 表无关所有的行都会被保留t_class 表的列会根据条件 s.hobby Python 来填充。
对于 id1 这一行hobby 是 Python所以会与 t_class 表的所有行进行左连接因为 hobby Python 的条件总是成立。对于 id2, id3, 和 id4 的行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.hobbyc.cname where s.hobbyPython先再看下上面的where原理执行步骤
从表中检索出所有记录。对每一条记录应用WHERE子句中的条件表达式。如果记录满足条件表达式则将其包含在结果集中。如果记录不满足条件表达式则将其排除在结果集之外。 这里特别注意第2点对于每一条记录都应用WHERE子句中的条件进行过滤也就是如果不符合就是左边和右边都不会显示而上面LEFT JOIN ON是对于不符合的会将左表记录与NULL值合并成一条记录添加到结果集,也就是左表记录数据一定会展示出来 执行结果如下也很好理解对于where里面符合的条件s.hobbyPython会正常展示一行而不符合的就直接整行不展示而不是保留左边值右边显示NULL
idnameclass_idhobbycidcnameis_delete1小王1Python2Python0
而对于把条件过滤改成Java执行结果应该也能很好理解了
select * from t_student s left join t_class c on s.hobbyc.cname where s.hobbyJavaidnameclass_idhobbycidcnameis_delete2小红2Java1Java04小李4Java1Java02小红2Java4Java14小李4Java4Java1
总结
通过对比上述left join on 和where两个查询语句我们可以发现在使用left join on时即使没有选修课程记录学生的信息也会显示出来而在使用where条件时要是选修课程的条件不满足就直接整行不显示了区别还是很大的left join on主要用于连接表和显示所有记录而where条件则用于对结果进行进一步筛选。平常工作中我们一定要理解这两者的区别和原理才能正确地用好这两个关键的查询语句
TODO后续 日常工作中写SQL我们会经常left join一堆表我最多见过的有20多张的。。。在一家外企然后还用了一堆存储过程有几千行的吐了。。。那么对于left join on和前面表关联条件的时候有的时候是紧挨着的表有的是隔开的有啥区别呢具体看看这个 a 表主键是aidb表是bidc表是cid以下两个语句有什么区别如何理解 篇幅有限。详见下文~
select * from a left join b on a.aidb.aid left join c on b.bidc.bidselect * from a left join b on a.aidb.aid left join c on a.aidc.aid
