编写 SQL 的排除联接

有两个表，就叫源表和目标表吧。它们有一个相同的字段，通过该字段可以把源表和目标表关联在一起，我们希望从源表中检索到的记录里的关联字段的值没有存在目标表中。举个例子，源表 dept，目标表 emp，获取 dept 表中部门编号不在 emp 表中的记录。在检查两张表的数据后，我们发现 emp 表中没有部门编号 40 的数据。

图1 emp 表的数

图2 dept 表的数据

实现这种的查询的方法有很多，不同的实现方式的性能也会不一样。我们就来看看都有哪些方法？

NOT IN

SELECT 
  * 
FROM
  dept 
WHERE deptno NOT IN 
  (SELECT 
    deptno 
  FROM
    emp)

这种实现方式需要注意一个点，就是在 not in 里面不能出现 NULL，如果出现 NULL 就会查不到结果。比如下面这条 SQL，没有数据返回。

SELECT 
  dname 
FROM
  dept 
WHERE deptno NOT IN 
  (SELECT 
    deptno 
  FROM
    emp 
  UNION ALL 
  SELECT 
    NULL)

为什么是这样呢？

因为在逻辑运算中，涉及到 NULL 的操作的结果仍为 NULL。not in 可以改写成 or 的形式，比如 deptno not in(10,NULL) 展开成 or 的表达式是：not (deptno = 10 or deptno = NULL)，最终的表达式是 not NULL 。

NOT EXISTS

使用 not exists 可以避免由于目标表的关联列上出现 NULL 而查不出数据。

WITH e AS 
(SELECT 
  deptno 
FROM
  emp 
UNION ALL 
SELECT 
  NULL) 
SELECT 
  * 
FROM
  dept 
WHERE NOT EXISTS 
  (SELECT 
    NULL 
  FROM
    e 
  WHERE e.deptno = dept.deptno)

使用 not exists 的 SQL 的一般形式：

SELECT 
  目标列 
FROM
  源表 
WHERE NOT EXISTS 
  (SELECT 
    NULL 
  FROM
    目标表 
  WHERE 关联字段)

在 MySQL 5.6 之前，子查询的性能表现得比较差，因而就有人想着把子查询改成连接的方式以提高查询性能。

LEFT JOIN

通常，我们会想到使用 NOT IN、NOT EXISTS 做排除操作。其实，使用 LEFT JOIN 也可以达到相同的目的。

SELECT 
  d.* 
FROM
  dept d 
  LEFT JOIN emp e 
    ON e.deptno = d.deptno 
WHERE e.deptno IS NULL 

对于表达式 a left join b ，不管 b 表中是否有数据可以和 a 表匹配得上，a 表总是能返回所有数据。如果 b 表中没有数据能匹配得上 a 表，在查询结果中会使用 NULL 填充 b 表的列。因此，通过过滤条件 b.关联列 is NULL 可以找到只存在于 a 表中的数据。

总结

1. 使用 not in 时要考虑到排除的值中是否有 NULL ，如果有，需要提前做过滤处理。
2. not exists 和 left join 都可以用来做排除操作，可以任选一种方式实现，如果 SQL 的性能表现不佳，则可以换另外一种方式试试。