SQL中JOIN时条件放在Where和On的区别
设为“置顶或星标”，第一时间送达干货
背景
SQL中JOIN子句是用于把来自两个或多个表的数据连接起来，在这个过程中可能会添加一些过滤条件。昨天有小伙伴问，如下图的这两种SQL写法查询结果是否会一样？（好像这是某一年阿里的面试题）
这个问题提出来以后，多数小伙伴的回答是：查询结果应该是一样的吧，只是查询效率不一样。我当时的回答是，在Inner Join时这两种情况返回的结果是一样的，在Left、Right等情况时结果不一样。
案例
1、创建测试数据库和表并且插入用户测试的数据。
-- 创建数据库
CREATE DATABASE ods_study_1;
USE ods_study_1;
-- 创建表
CREATE TABLE ods_study_1.ods_study_join_a
(
study_id_a int
);
CREATE TABLE ods_study_1.ods_study_join_b
(
study_id_b int,
study_channel varchar(255)
);
-- 插入数据
INSERT INTO ods_study_1.ods_study_join_a(study_id_a) VALUES(1),(2),(3),(4),(5)
INSERT INTO ods_study_1.ods_study_join_b(study_id_b,study_channel) VALUES(1,'weixin'),(2,'zhihu'),(2,'zhihu'),(3,'toutiao');
2、查看一下原始的数据：
结果验证
将上面的两个表Inner Join和Left Join，过滤条件分别放在on和where中。
1、Inner Join时
ON中设置过滤条件
SELECT
*
FROM
ods_study_1.ods_study_join_a A
Inner JOIN
ods_study_1.ods_study_join_b B
ON (A.study_id_a = B.study_id_b AND B.study_channel='weixin')
返回结果:
Where中设置过滤条件
SELECT
*
FROM
ods_study_1.ods_study_join_a A
Inner JOIN
ods_study_1.ods_study_join_b B
ON (A.study_id_a = B.study_id_b )
WHERE B.study_channel='weixin'
返回结果:
结论：Inner Join时过滤条件放在on和where中返回结果一致。
2、Left Join时
ON中设置过滤条件
SELECT
*
FROM
ods_study_1.ods_study_join_a A
LEFT JOIN
ods_study_1.ods_study_join_b B
ON (A.study_id_a = B.study_id_b AND B.study_channel='weixin')
返回结果:
Where中设置过滤条件
SELECT
*
FROM
ods_study_1.ods_study_join_a A
LEFT JOIN
ods_study_1.ods_study_join_b B
ON (A.study_id_a = B.study_id_b )
WHERE B.study_channel='weixin'
返回结果:
结论：Left Join时过滤条件放在on和where中返回结果不一致。
原因分析
可以这么理解，当两张表在Left Join时，会生成一张连接临时表，然后再将这张连接临时表返回给用户。
在On的情况下，是在生成临时表时起作用，但由于Left Join的性质，就是他不管On里面的过滤条件是否为真，都会返回左表里的记录。对于不满足条件的记录，右表字段全部是NULL。
在Where的情况下，是在临时表生成好以后起作用，在对临时表进行过滤。此时，只要条件不为真的行，全部都过滤掉了。
——End——
后台回复关键字：1024，获取一份精心整理的技术干货 后台回复关键字：进群，带你进入高手如云的交流群。 推荐阅读
SQL 语句中 where 条件后 写上1=1 是什么意思 国产数据库建模工具，看到界面第一眼，良心了！ 快手公司厕所装坑位计时器，网友：再也不能带薪拉屎了！ 如何优雅地给妹子优化电脑？ 最全MySQL面试题集合 这是一个能学到技术的公众号，欢迎关注
点击「阅读原文」了解SQL训练营
评论