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-

用Python把朋友头像变表情包?

取数,取数,取个屁啊!

后台回复“入群”即可加入小z数据干货交流群
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值