BETWEEN 範圍查詢

BETWEEN 運算子用來篩選值在指定範圍內的資料。

語法

SELECT column1, column2, ...
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

數值範圍

假設有一個 products 資料表：

查詢價格在 200 到 600 之間的商品：

SELECT * FROM products
WHERE price BETWEEN 200 AND 600;

結果：

這等同於：

SELECT * FROM products
WHERE price >= 200 AND price <= 600;

日期範圍

-- 查詢 2024 年的訂單
SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

-- 查詢最近 7 天的訂單
SELECT * FROM orders
WHERE order_date BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY) AND NOW();

字串範圍

字串會依字母順序比較：

-- 查詢名字在 A 到 M 開頭的客戶
SELECT * FROM customers
WHERE name BETWEEN 'A' AND 'M';

NOT BETWEEN

NOT BETWEEN 用來排除在範圍內的值：

SELECT * FROM products
WHERE price NOT BETWEEN 200 AND 600;

結果：

搭配其他條件

SELECT * FROM products
WHERE price BETWEEN 100 AND 500
AND category = '電子產品';