BETWEEN Operator
In this page:
What is BETWEEN?
The BETWEEN operator selects values within a given range, like ages 18 to 30. This range is inclusive, which means the start and end values are included in your results. It works great with numbers and reads more naturally than writing two separate comparison conditions.
Example: What is BETWEEN?
SELECT * FROM users WHERE age BETWEEN 18 AND 30;BETWEEN with Dates
BETWEEN is very common for filtering date ranges, such as pulling all orders from a specific quarter. It lets you find records that happened during a specific week, month, or year. Remember to use single quotes for dates, and watch for time components that can silently exclude the end date.
Example: BETWEEN with Dates
SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31';BETWEEN with Text Ranges
We can also use BETWEEN on text columns, since MySQL compares strings alphabetically just like numbers. This filters values alphabetically, so BETWEEN M AND P would catch names starting with those letters. It is useful for finding names or codes within a specific range of letters.
Example: BETWEEN with Text Ranges
SELECT * FROM users WHERE name BETWEEN 'M' AND 'S';The NOT BETWEEN Operator
If you want to find values outside of a range, use NOT BETWEEN, such as excluding a blackout date window. This exclusions filter is great for skipping over a common middle range of data while still including everything on either side.
Example: The NOT BETWEEN Operator
SELECT * FROM events WHERE event_date NOT BETWEEN '2024-06-01' AND '2024-06-15';Combining BETWEEN with AND
You can add other conditions alongside BETWEEN to narrow a range further, like a price range plus a specific category. We use the regular AND operator to link these conditions. This lets us filter ranges inside specific categories rather than across the whole table.
Example: Combining BETWEEN with AND
SELECT * FROM products WHERE price BETWEEN 10 AND 50 AND category = 'Books';Chapter Quiz — Complete all 8 topics to unlock
0/8 topics done
Complete these topics first: