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 ）

 

 

 

转载于:https://www.cnblogs.com/wanana/p/3512606.html