SQL IN and BETWEEN: Cleaner Filters for Lists and Ranges
IN replaces a long chain of ORs, and BETWEEN filters a range of prices or dates in one line. See both on a sample online shop database, plus the NOT IN trap with missing values.
Step by step
Cleaner filters
- Lists and ranges. Filters get long quickly: 3 cities, a price band, 1 month of orders. IN and BETWEEN keep these filters short and easy to read. Here they tidy up queries on a sample online shop database.
- The customers table. Start with the customers table. The goal is to list every customer who lives in Leeds, York or Newcastle.
IN
- The long way. The long way: select first_name, last_name and city from customers, WHERE city = 'Leeds' OR city = 'York' OR city = 'Newcastle'. It works, but city = is repeated 3 times. Run it and 10 customers come back from those 3 cities.
SELECT first_name, last_name, city FROM customers WHERE city = 'Leeds' OR city = 'York' OR city = 'Newcastle'; - IN. The short way: replace the OR chain with city IN, then the list in brackets with commas between the values: ('Leeds', 'York', 'Newcastle'). Each text value still goes in single quotes. Run it and the same 10 customers come back. Same answer, but much easier to read and to change.
city IN ('Leeds', 'York', 'Newcastle')
NOT IN
- NOT IN. Change city IN to city NOT IN to get every customer outside those 3 cities. It returns 29 rows, not 30. The table has 40 customers and 10 live in those cities, but 1 customer has no city (NULL). A NULL never matches IN or NOT IN, so that customer drops out of both results.
city NOT IN
BETWEEN
- BETWEEN numbers. Now a range. To find products that cost from 20 to 30, select product_name and unit_price from products WHERE unit_price BETWEEN 20 AND 30. BETWEEN takes a low value, then AND, then a high value. It returns 6 products. BETWEEN includes both ends, so a product priced at exactly 20 or exactly 30 would be in the list too.
SELECT product_name, unit_price FROM products WHERE unit_price BETWEEN 20 AND 30;
BETWEEN dates
- BETWEEN dates. BETWEEN works on dates as well. To get every order in March, select order_id, order_date and order_total from orders WHERE order_date BETWEEN '2025-03-01' AND '2025-03-31'. Dates go in quotes, year first. It returns 11 orders in March. That's a monthly report filter in 2 short lines.
SELECT order_id, order_date, order_total FROM orders WHERE order_date BETWEEN '2025-03-01' AND '2025-03-31';
Recap
- IN and BETWEEN. IN matches a list and replaces a chain of ORs. NOT IN skips rows where the value is NULL. BETWEEN matches a range and includes both ends.
Common mistakes
- Expecting NOT IN to include missing values. A customer with a NULL city is not in the list, but not outside it either, so NOT IN skips them. Add OR city IS NULL if you want those rows too.
- A NULL inside the NOT IN list. If the list itself contains a NULL, for example from a subquery, NOT IN returns no rows at all. Remove NULLs from the list first, or use NOT EXISTS.
- Dates that also store a time. If order_date holds a date and a time, BETWEEN '2025-03-01' AND '2025-03-31' misses orders placed after midnight on 31 March. Use order_date >= '2025-03-01' AND order_date < '2025-04-01' instead.
- Putting the high value first. BETWEEN 30 AND 20 returns no rows, because nothing is both 30 or more and 20 or less. Write the low value first.
When a business analyst uses this
Before signing off a monthly sales report, a business analyst can check its totals against the source data with WHERE order_date BETWEEN '2025-03-01' AND '2025-03-31' AND city IN ('Leeds', 'York'). If the order count doesn't match the report, that goes in the defect log with the query attached.
Questions people ask
Is SQL BETWEEN inclusive?
Yes. BETWEEN includes both end values, so BETWEEN 20 AND 30 matches 20, 30 and everything in between. This works the same way in SQL Server, MySQL, PostgreSQL and SQLite.
What is the difference between IN and BETWEEN in SQL?
IN matches a list of exact values, such as 3 city names. BETWEEN matches every value in a range, from a low value to a high value, such as prices from 20 to 30.
How do you query between two dates in SQL?
Use WHERE order_date BETWEEN '2025-03-01' AND '2025-03-31', with the dates in quotes and the year first. If the column also stores times, use >= the first day and < the day after the last day instead.
