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 fast. Three cities, a price band, one month of orders. In and between keep those filters short and easy to read. Let's tidy some up.
- The customers table. First, the customers table. We want everyone who lives in Leeds, York or Newcastle.
IN
- The long way. Here's the long way. City equals Leeds, or city equals York, or city equals Newcastle. It works, but it repeats itself. Ten customers live in those three cities.
SELECT first_name, last_name, city FROM customers WHERE city = 'Leeds' OR city = 'York' OR city = 'Newcastle'; - IN. Now the short way. City in, then the list in brackets, with commas between the values. No more repeating city equals. The same ten customers. Same answer, much easier to read and to change.
city IN ('Leeds', 'York', 'Newcastle')
NOT IN
- NOT IN. Add not, and you get everyone outside those cities. Twenty nine. But forty minus ten is thirty. The one customer with no city drops out, because an empty value never matches a list. Keep that in mind.
city NOT IN
BETWEEN
- BETWEEN numbers. Now ranges. Which products cost between twenty and thirty? Between takes a low value, and, then a high value. Six products. Between includes both ends, so a product at exactly twenty or thirty 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. Every order in March, from the first to the thirty first. Dates go in quotes, year first. Eleven orders in March. That's a monthly report filter in two 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. So, in matches a list and replaces a chain of ors. Not in skips empty values. Between matches a range, and it includes both ends.
