用 IN 和 NOT IN 做資料過濾

想像一下，你在大學的資料庫裡工作，要找出那些在特定課程上課的學生。比如說「程式設計」、「數學」還有「物理」。當然，你可以寫一個很長的查詢，像這樣一堆條件：

SELECT *
FROM students 
WHERE course = '程式設計'
   OR course = '數學'
   OR course = '物理';

但說真的，這樣寫很累，看起來也不太優雅。幸好有 IN operator，可以讓你把同樣的查詢寫得超精簡，還省時間：

SELECT *
FROM students 
WHERE course IN ('程式設計', '數學', '物理');

是不是有點像魔法？不用一堆 OR，直接跟 SQL 說要找這個 list 裡的值。如果你想查某個值不在這個 list 裡，就用 NOT IN —— 找所有不在這個 list 裡的東西。

IN operator 的語法

這是 IN operator 的基本語法：

SELECT 欄位們
FROM 資料表
WHERE 欄位 IN (值1, 值2, 值3, ...);

現在來看幾個例子。

範例 1：同時修多個課程的學生

假設我們有一個 students 資料表：

我們想找出所有正在修「程式設計」、「數學」或「物理」的學生。用 IN：

SELECT name, course
FROM students
WHERE course IN ('程式設計', '數學', '物理');

結果：

你看，用 IN operator 真的簡單很多。不用寫一堆 OR，直接列出你要的值就好。

範例 2：沒修某些課的學生

現在假設你想找出那些沒修「程式設計」、「數學」和「物理」的學生。這時就用 NOT IN：

SELECT name, course
FROM students
WHERE course NOT IN ('程式設計', '數學', '物理');

結果：

所以 NOT IN operator 會回傳所有 course 欄位不在指定 list 裡的資料列。

用 IN 和 NOT IN 搭配子查詢

IN 和 NOT IN operator 超適合拿來比對兩個資料表的資料。舉例來說，假設我們有兩個資料表：

students 資料表：

courses 資料表：

假設我們要找出那些有註冊在 courses 資料表裡課程的學生。這時就可以用子查詢搭配 IN：

SELECT name
FROM students
WHERE course_id IN (
    SELECT id
    FROM courses
);

這個查詢怎麼運作：子查詢 SELECT id FROM courses 會回傳所有課程的 id。然後 IN operator 會檢查 course_id 有沒有在這個 list 裡。

結果：

為什麼 Dan 不見了？因為他的 course_id（104）在 courses 資料表裡找不到。

跟 NULL 有關的特性

IN operator 有個很重要的特性：如果 list 裡有 NULL，查詢結果可能會被影響。來看個例子。

grades 資料表：

查詢要找出成績在 ('A', 'B', 'C') 裡的學生，會像這樣：

SELECT student_id
FROM grades
WHERE grade IN ('A', 'B', 'C');

結果：

grade 欄位是 NULL 的那筆資料會被忽略，因為 NULL 不會被當成任何 list 的一部分。

現在想像你用 NOT IN operator。例如：

SELECT student_id
FROM grades
WHERE grade NOT IN ('A', 'B', 'C');

你可能以為會看到 student_id = 2，但結果會是空的！為什麼？因為 NULL 跟 list 裡每個值比對時，結果都是不確定（UNKNOWN）。這個行為很容易讓人搞混，所以用 NOT IN 時要注意有沒有 NULL。這種情況最好直接加個 NULL 判斷：

SELECT student_id
FROM grades
WHERE grade NOT IN ('A', 'B', 'C')
   OR grade IS NULL;

結果：

IN 和 NOT IN 的使用建議

用 IN 讓 SQL 更好讀
如果你要查欄位是不是在某個值的 list 裡，絕對用 IN，不要寫一堆 OR。

小心 NOT IN 跟 NULL
如果資料裡有 NULL 欄位，結果可能會怪怪的。用 NOT IN 時最好加上 NULL 的處理。

子查詢記得加 index
如果 IN 搭配子查詢，記得子查詢的欄位要有 index，不然效能會很差。

實戰範例

想像你在一個網路商店系統工作。有 orders 跟 users 兩個資料表。你想找出那些從來沒下過訂單的使用者。

users 資料表：

orders 資料表：

用 NOT IN 來解這題：

SELECT name
FROM users
WHERE id NOT IN (
    SELECT user_id
    FROM orders
);

結果：

這個查詢的邏輯是：先用子查詢 SELECT user_id FROM orders 找出所有有下過訂單的 user id（1 跟 3）。然後 NOT IN 把他們排除掉，只留下沒下過訂單的（Mello 跟 Dan）。