When would you use IN versus BETWEEN in a WHERE clause?
Use IN for a discrete set of values; use BETWEEN for a contiguous range. BETWEEN is inclusive on both ends. IN does not imply any order and is ideal for filtering against a known list, while BETWEEN is cleaner for numeric or date ranges.
How to think about it
The interviewer is checking two things: do you know the semantic difference, and do you know the gotcha with timestamps? Start by separating the use cases clearly.
IN checks membership in a discrete list; BETWEEN checks whether a value falls within a closed interval. They solve different problems — don’t reach for one when you mean the other.
IN — discrete set
Use IN when you have a fixed list of values you care about. It’s a cleaner way to write multiple OR conditions:
-- Orders from three specific regions
SELECT * FROM orders WHERE region IN ('West', 'Northeast', 'Midwest');
-- Same thing, but painful to read and extend
SELECT * FROM orders
WHERE region = 'West' OR region = 'Northeast' OR region = 'Midwest';IN also works beautifully with subqueries when the list comes from another table:
SELECT * FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE tier = 'Gold');BETWEEN — contiguous range
Use BETWEEN for numbers, dates, or any sequential value where you want everything from A to B. Both endpoints are always included:
-- Orders placed in Q1 2024 (both bounds inclusive)
SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31';
-- Salaries from 50000 to 80000 inclusive
SELECT * FROM employees WHERE salary BETWEEN 50000 AND 80000;BETWEEN low AND high is exactly col >= low AND col <= high. Every beginner should memorise that — it comes up constantly.
Try it yourself
Run the query below. Then swap the IN list or change the BETWEEN bounds and see what changes.
Date range gotcha with timestamps
This is the follow-up interviewers love. When your column stores date+time, BETWEEN has a trap:
-- WRONG for TIMESTAMP columns: misses 2024-03-31 after midnight
WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31'
-- CORRECT: use an exclusive upper bound
WHERE order_date >= '2024-01-01' AND order_date < '2024-04-01'BETWEEN stops at 2024-03-31 00:00:00 — any order timestamped later in the day on March 31 silently drops out.