SQL 循序漸進 (15)：IN 與 BETWEEN 條件運算子完整指南

IN 條件運算子：集合成員資格測試

語法

SELECT column1, SUM(column2)
FROM table_list
WHERE column3 IN (value1, value2, ...);

範例：特定姓氏員工

SELECT employeeid, lastname, salary
FROM employee_info
WHERE lastname IN ('Hernandez', 'Jones', 'Roberts', 'Ruiz');

回傳姓氏為四者之一的員工記錄。

等價改寫（較冗長）

WHERE lastname = 'Hernandez'
   OR lastname = 'Jones'
   OR lastname = 'Roberts'
   OR lastname = 'Ruiz'

IN 優勢：語句簡潔、可讀性高、易維護，尤其值超過 2-3 個時。

排除用法：NOT IN

WHERE lastname NOT IN ('Hernandez', 'Jones')

BETWEEN 條件運算子：範圍邊界測試

語法

SELECT column1, SUM(column2)
FROM table_list
WHERE column3 BETWEEN value1 AND value2;

範例：30-40 歲員工

SELECT employeeid, age, lastname, salary
FROM employee_info
WHERE age BETWEEN 30 AND 40;

包含邊界值（30 與 40 歲皆符合）。

等價改寫

WHERE age >= 30 AND age <= 40

排除用法：NOT BETWEEN

WHERE age NOT BETWEEN 30 AND 40

資料型別適用性

• 數值、日期、字串（字典序）皆可使用
• 日期範例：WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31'
• 字串範例：WHERE product_code BETWEEN 'A000' AND 'M999'

效能與最佳實務

• IN 清單過長（超過數百）可能影響最佳化，考慮臨時表或 JOIN
• BETWEEN 於索引欄位可觸發範圍掃描
• 邊界值語意明確：BETWEEN = >= AND <=，避免 off-by-one 錯誤
• 子查詢搭配：WHERE id IN (SELECT id FROM ...)