SQL Between
BETWEEN tests whether a value falls inside a range. One BETWEEN test names both ends of the range.
A price range
BETWEEN keeps prices from 5 to 15. Three products fall inside the range.
SELECT name, price FROM products WHERE price BETWEEN 5 AND 15 ORDER BY price;Both ends count
The low end and the high end both belong to the range. Prices of exactly 4.5 and exactly 9.99 pass.
SELECT name, price FROM products WHERE price BETWEEN 4.5 AND 9.99 ORDER BY price;A date range
order_date stores each date as text in YYYY-MM-DD shape. That shape sorts like real dates, so BETWEEN finds the February orders.
SELECT order_id, order_date FROM orders WHERE order_date BETWEEN '2024-02-01' AND '2024-02-29' ORDER BY order_date;Example
SELECT name, price FROM products WHERE price BETWEEN 10 AND 40 ORDER BY price;Key points
- BETWEEN: a test that checks a value against a low end and a high end
- Use BETWEEN to keep rows whose value sits inside a range.
- Both ends belong to the range.
- AND joins the two ends inside a BETWEEN test.
- Date text: order_date stores dates as YYYY-MM-DD text, so its text order is date order
In this example, BETWEEN keeps prices from 10 to 40. The Water Bottle, the Desk Lamp and the Backpack fall inside the range. The rows come back in price order.
Try it Yourself
Edit the code, then press Run.
Running is simulated · DB Fiddle
Exercise
What does BETWEEN 5 AND 10 say about the values 5 and 10?