語法結構

SELECT columns
FROM tables
WHERE column1 BETWEEN value1 AND value2;

等價於 column1 >= value1 AND column1 <= value2，包含邊界值。

實戰範例

1. 數值範圍查詢

SELECT *
FROM suppliers
WHERE supplier_id BETWEEN 5000 AND 5010;

等同寫法：

SELECT *
FROM suppliers
WHERE supplier_id >= 5000
  AND supplier_id <= 5010;

2. 日期範圍查詢

SELECT *
FROM orders
WHERE order_date BETWEEN TO_DATE('2003/01/01', 'yyyy/mm/dd')
                     AND TO_DATE('2003/12/31', 'yyyy/mm/dd');

等同寫法：

SELECT *
FROM orders
WHERE order_date >= TO_DATE('2003/01/01', 'yyyy/mm/dd')
  AND order_date <= TO_DATE('2003/12/31', 'yyyy/mm/dd');

3. NOT BETWEEN 排除範圍

SELECT *
FROM suppliers
WHERE supplier_id NOT BETWEEN 5000 AND 5500;

等同寫法：

SELECT *
FROM suppliers
WHERE supplier_id < 5000
   OR supplier_id > 5500;

使用建議

• BETWEEN 適用於包含邊界的閉區間查詢，語意清晰。
• 日期查詢時務必確認資料型別與格式一致，避免隱性轉型導致索引失效。
• NOT BETWEEN 實際執行計畫可能轉為 OR 條件，大量資料時需關注索引使用情況。