MySQL 特殊比較運算子實戰：BETWEEN、GREATEST、LEAST 進階用法

MySQL 特殊比較運算子實戰：BETWEEN、GREATEST、LEAST 進階用法

除了常用的 =、<>、LIKE、IN，MySQL 提供 BETWEEN、GREATEST、LEAST 等特殊比較運算子，能簡化範圍判斷、極值取得等邏輯，提升 SQL 可讀性與效能。

1. BETWEEN … AND … — 範圍包含判斷

語法：expr BETWEEN min AND max，等價於 expr >= min AND expr <= max。支援數值、日期、字串。

-- 數值範圍
SELECT 2350 BETWEEN 100 AND 10000;  -- 1 (true)

-- 日期範圍 (字串隱性轉換)
SELECT '2023-04-05' BETWEEN '2023-01-01' AND '2024-01-01';  -- 1
SELECT '2023-04-05' BETWEEN '2023-01-01' AND '2023-04-01';  -- 0 (4/05 > 4/01)

-- 實戰：查詢某月份訂單
SELECT * FROM orders
WHERE order_date BETWEEN '2023-04-01' AND '2023-04-30 23:59:59';
-- 建議改用半開區間避免秒級邊界問題：
WHERE order_date >= '2023-04-01' AND order_date < '2023-05-01'

注意：BETWEEN 為閉區間，日期時間建議用半開區間 (>= AND <) 避免邊界遺漏。

2. GREATEST() / LEAST() — 多值極值取得

語法：GREATEST(val1, val2, ...) 回傳最大值；LEAST(val1, val2, ...) 回傳最小值。支援數值、日期、字串 (按字典序)。

-- 日期最大值 (最新日期)
SELECT GREATEST('2023-06-01', '2023-05-29', '2023-06-04');  -- 2023-06-04

-- 數值最小值
SELECT LEAST(100, 200, 50, -6, -73, 1000);  -- -73

-- 實戰：取多欄位中最新的更新時間
SELECT id, GREATEST(updated_at, synced_at, migrated_at) AS latest_touch
FROM user_profile;

NULL 傳播規則：任一參數為 NULL，結果為 NULL。實務上常搭配 COALESCE() 或 IFNULL() 預處理：

SELECT GREATEST(COALESCE(a, '1970-01-01'), COALESCE(b, '1970-01-01'), COALESCE(c, '1970-01-01'));

3. 綜合實戰案例：動態篩選器

需求：使用者可選填「最小價格」、「最大價格」、「最早日期」、「最晚日期」，任一欄位留空則不限制。

SELECT * FROM products
WHERE (min_price IS NULL OR price >= min_price)
  AND (max_price IS NULL OR price <= max_price)
  AND (start_date IS NULL OR created_at >= start_date)
  AND (end_date IS NULL OR created_at <= end_date);

使用 BETWEEN 重寫 (需處理 NULL)：

SELECT * FROM products
WHERE (min_price IS NULL AND max_price IS NULL OR price BETWEEN COALESCE(min_price, 0) AND COALESCE(max_price, 999999))
  AND (start_date IS NULL AND end_date IS NULL OR created_at BETWEEN COALESCE(start_date, '1970-01-01') AND COALESCE(end_date, '9999-12-31'));

GREATEST/LEAST 版本 (最簡潔，需 MySQL 8.0+ 支援 NULL 忽略特性或自行 COALESCE)：

SELECT * FROM products
WHERE price >= COALESCE(min_price, 0)
  AND price <= COALESCE(max_price, 999999)
  AND created_at >= COALESCE(start_date, '1970-01-01')
  AND created_at <= COALESCE(end_date, '9999-12-31');

效能提示

• BETWEEN 可使用索引範圍掃描，等同 >= AND <=。
• GREATEST/LEAST 為純量函數，無法使用索引。若用於 WHERE GREATEST(a,b) > 100 會導致全表掃描。建議改寫為 WHERE a > 100 OR b > 100 (配合索引)。
• 大量資料下，極值計算建議在應用層或物化視圖預先計算。