SQL 中 NOT IN 的陷阱

這是我的 SQL

WITH T AS (
	SELECT 1 AS id 
	UNION ALL 
	SELECT 2 
	UNION ALL
	SELECT 3 
	UNION ALL
	SELECT 4
),
S AS (
	SELECT 10 AS id
	UNION ALL 
	SELECT 11 
	UNION ALL 
	SELECT 12
	UNION ALL 
	SELECT 4
)
SELECT * FROM S
WHERE id NOT IN (SELECT id FROM T)

這段 SQL 執行完畢後，會得到

id
10
11
12

但是，如果今天 T 加上一筆 null 資料，即：

id
1
2
3
4
(null)

寫成 SQL 就是

WITH T AS (
	SELECT 1 AS id 
	UNION ALL 
	SELECT 2 
	UNION ALL
	SELECT 3 
	UNION ALL
	SELECT 4
	UNION ALL
	SELECT NULL
),
S AS (
	SELECT 10 AS id
	UNION ALL 
	SELECT 11 
	UNION ALL 
	SELECT 12
	UNION ALL 
	SELECT 4
)
SELECT * FROM S
WHERE id NOT IN (SELECT id FROM T)

這種情況，就不會回傳任何東西

有趣的是，如果不是 NOT IN而是 IN（即下面這段 SQL），就會有資料

SELECT * FROM S
WHERE id IN (SELECT id FROM T)

結果會是：

id
4

解法

解決辦法有很多種

我自己就想到以下幾種

LEFT JOIN

由於 LEFT JOIN 在抓不到 JOIN table 資料時，欄位會是 NULL，這邊利用這特性，特別抓取 JOIN table 資料為 null 的情況即可

即：

SELECT S.*
FROM S
LEFT JOIN T ON T.id = S.id
WHERE T.id IS NULL

Sub-Query 加上條件

在 Sub-Query 加上 not null 條件

SELECT *
FROM S
WHERE id NOT IN
    (SELECT id
     FROM T
     WHERE id IS NOT NULL)