NOT IN 行為的原因

sql> SELECT * FROM runners;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | John Doe     |
|  2 | Jane Doe     |
|  3 | Alice Jones  |
|  4 | Bobby Louis  |
|  5 | Lisa Romero  |
+----+--------------+

sql> SELECT * FROM races;
+----+----------------+-----------+
| id | event          | winner_id |
+----+----------------+-----------+
|  1 | 100 meter dash |  2        |
|  2 | 500 meter dash |  3        |
|  3 | cross-country  |  2        |
|  4 | triathalon     |  NULL     |
+----+----------------+-----------+

下面查詢的結果是什麼？

SELECT * FROM runners WHERE id NOT IN (SELECT winner_id FROM races);

上面的查詢不會返回任何內容，因為 races 表包含 null winner_id。但這背後的原因是什麼？

SELECT * FROM runners WHERE id NOT IN (SELECT winner_id FROM races);

是這樣的（實際值來自races）：

SELECT * FROM runners WHERE
id <> 2 and
id <> 3 and
id <> null;

id <> null根據定義unknown，因此整個where子句的計算結果為false(for ids: 2, 3) 或unknown(for ids: 1, 4, 5) 因為and運算符，因此沒有返回結果。

如果要執行查詢而不考慮 中的空值races，可以使用：

SELECT * FROM runners 
WHERE id NOT IN 
     (SELECT winner_id FROM races WHERE winner_id IS NOT NULL);

引用自：https://dba.stackexchange.com/questions/159137