NOT IN vs. NOT EXISTS vs. LEFT JOIN/ IS NULL

最近没啥活干,复习下基础,在 俄国一个sql在线练习网站 上做点儿sql题,经常遇到这类问题:

相关联的左(l), 右(r) 表,取出左表中存在,但是右表中不存在的行(Select values present in one table but missing in another one.)

 

有一些不完整的发现,暂写成此文待补充

 

这个问题有三种写法:

1. 左连接后,选择右表中连接键为null的记录(LEFT JOIN/ IS NULL)

SELECT  l.*
FROM    t_left l
LEFT JOIN
        t_right r
ON      r.value = l.value
WHERE   r.value IS NULL

 

2. NOT IN

SELECT  l.*
FROM    t_left l
WHERE   l.value NOT IN
        (
        SELECT  value
        FROM    t_right r
        )

 

3. EXISTS

SELECT  l.*
FROM    t_left l
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    t_right r
        WHERE   r.value = l.value
        )

 

其中第一种方法之前没怎么见过,算是新知识。 

研究过程中在翻到很多关于三者比较的文章,原文链接如

SQL Server

Oracle

MySQL

 

搬运一下结果总结,有兴趣的看原文

 

结果是否相同?

首先对于NOT EXISTS 和 LEFT JOIN/IS NULL 的结果总是完全相同的。

如果连接字段非空,那么三者的语义在不同数据库环境下也是完全相同的。

 

当被判断字段可空的时候

Sql Server 和 ORACLE (引文中认为oracle中语义相同,但经过测试和参考AskTom 中的回答,我认为Oracle 的情况与Sql Server 一致)中, NOT IN 与另外两个的结果不不同。

也就是说,当右表查询结果中字段值为空的时候NOT IN (NULL) 不会返回任何结果, 因为NULL与NULL并不相等。

如,当l_left 和l_right 是完全相同的表,且所有的value全都是空的话, NOT IN返回结果数量是0,而NOT EXISTS是全部

 

  MySQL暂未测试,待补充。预计结论同上。

 

效率比较?

(本部分纯搬运)

SQL Sever 中NOT IN 的效率低于NOT EXISTS和LEFT JOIN/ IS NULL,后两者的执行计划相同

MySQL对三者生成三个不同的执行计划,其中NOT EXISTS 的效率明显低于NOT IN 和 LEFT JOIN/ IS NULL。

对于Oracle,三者生成完全相同的执行计划,cost完全相同。(此为原文,结论存疑)

(笔者简单测试了三种情况,结果不完整,待补充

1. 1万行做右表,主外键连接的,NOT IN 和NOT EXIST 计划一致,使用NESTED LOOPS ANTI, LEFT JOIN 使用HASH JOIN OUTER,最后一个比前边的效率略好5%

2.1 两个小于100行的表, 主外键连接, LEFT JOIN和NOT EXISTS 计划相同,使用HASH JOIN RIGHT ANTI ,NOT IN 使用FITER, 后者效率差22%

2.2 同上两个小表,可空字段连接,情况完全同2.1 )

 

 

 

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。