有兩個表，就叫作源表和目標表吧。它們有一個相同的字段，通過該字段可以把源表和目標表關聯在一起，我們希望從源表中檢索到的記錄里的關聯字段的值沒有存在目標表中。舉個例子，源表 dept，目標表 emp，獲取 dept 表中部門編號不在 emp 表中的記錄。檢查兩張表的數據，我們發現 emp 表中沒有部門編號 40 的數據。

實現這種的查詢的方法有很多，不同的實現方式的性能也會不一樣。我們就來看看都有哪些方法？

NOT IN

SELECT * 
FROMdept 
WHERE deptno NOT IN (SELECT deptno FROMemp)

這種實現方式需要注意一個點，就是在 not in 里面不能出現 NULL，如果出現 NULL 就會查不到結果。比如下面這條 SQL，沒有數據返回。

SELECT dname 
FROMdept 
WHERE deptno NOT IN (SELECT deptno FROMemp 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 
FROMemp 
UNION ALL 
SELECT NULL) 
SELECT * 
FROMdept 
WHERE NOT EXISTS (SELECT NULL FROMe 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.* 
FROMdept 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 的性能表現不佳，則可以換另外一種方式試試。

來源：SQL實現

作者：zero

原文：編寫 SQL 的排除聯接