When would you use IN versus BETWEEN in a WHERE clause?

How to think about it

The interviewer is really checking two things: do you know the semantic difference, and do you know the timestamp gotcha that catches people in production. The split is clean — IN checks membership in a discrete list; BETWEEN checks whether a value falls inside a closed interval. They solve different problems, so don’t reach for one when you mean the other.

IN — a discrete set

Use IN for a fixed list of values. It’s a tidier way to write a chain of ORs:

-- these two are equivalent; the first is far easier to read and extend
SELECT * FROM orders WHERE region IN ('West', 'Northeast', 'Midwest');
SELECT * FROM orders
WHERE region = 'West' OR region = 'Northeast' OR region = 'Midwest';

IN also pairs naturally with a subquery when the list lives in another table:

SELECT * FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE tier = 'Gold');

BETWEEN — a contiguous range

Use BETWEEN for numbers, dates, or any ordered value where you want everything from A to B. Both endpoints are included — col BETWEEN low AND high is exactly col >= low AND col <= high.

A worked example — same table, two operators

The IN filter keeps only the listed regions; the BETWEEN filter keeps a closed numeric range over the same rows:

SELECT id, region, amount FROM orders
WHERE region IN ('West', 'Midwest')
ORDER BY id;

id	region	amount
1	West	120
4	Midwest	50
5	West	300

SELECT id, region, amount FROM orders
WHERE amount BETWEEN 80 AND 200
ORDER BY id;

id	region	amount
1	West	120
2	Northeast	80
3	South	200
6	South	90

Notice both bounds of the BETWEEN survive — 80 and 200 are both in the result. That inclusivity is the whole reason the next trap exists.

The timestamp gotcha

This is the follow-up interviewers love. When the column stores date and time, an inclusive upper bound silently drops part of the last day:

-- WRONG for TIMESTAMP columns: stops at 2024-03-31 00:00:00,
-- so anything later on March 31 falls out
WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31'

-- CORRECT: exclusive upper bound covers the whole day
WHERE order_date >= '2024-01-01' AND order_date < '2024-04-01'