MySQL BETWEEN 子句用法詳解：區間篩選替代方案

BETWEEN ... AND ... 子句提供直觀的區間篩選語法，可替代 >= 與 <= 的組合。本文以 employee_tbl 示範兩種寫法的等價性。

測試資料

mysql> SELECT * FROM employee_tbl;
+------+------+------------+--------------------+
| id   | name | work_date  | daily_typing_pages |
+------+------+------------+--------------------+
| 1    | John | 2007-01-24 | 250                |
| 2    | Ram  | 2007-05-27 | 220                |
| 3    | Jack | 2007-05-06 | 170                |
| 3    | Jack | 2007-04-06 | 100                |
| 4    | Jill | 2007-04-06 | 220                |
| 5    | Zara | 2007-06-06 | 300                |
| 5    | Zara | 2007-02-06 | 350                |
+------+------+------------+--------------------+

需求：daily_typing_pages 介於 170 到 300 之間（含邊界）

寫法一：傳統比較運算子

SELECT * FROM employee_tbl 
WHERE daily_typing_pages >= 170 
  AND daily_typing_pages <= 300;

寫法二：BETWEEN 子句（推薦，可讀性更佳）

SELECT * FROM employee_tbl 
WHERE daily_typing_pages BETWEEN 170 AND 300;

兩者結果完全相同

+------+------+------------+--------------------+
| id   | name | work_date  | daily_typing_pages |
+------+------+------------+--------------------+
| 1    | John | 2007-01-24 | 250                |
| 2    | Ram  | 2007-05-27 | 220                |
| 3    | Jack | 2007-05-06 | 170                |
| 4    | Jill | 2007-04-06 | 220                |
| 5    | Zara | 2007-06-06 | 300                |
+------+------+------------+--------------------+

適用型態與注意事項

• 支援數值、日期、字串等可比較型態。
• BETWEEN 為閉區間（包含兩端點）。
• 若下界大於上界（如 BETWEEN 300 AND 170），不會報錯但永遠無結果。
• 搭配索引時，BETWEEN 可觸發範圍掃描，效能與 >= AND <= 相同。