Lesson Beginner

IN / BETWEEN

Once you have written a few WHERE clauses, two patterns show up over and over:
"is this value one of a list?" and "is this value in a range?"
IN and BETWEEN are shorthand for both.

IN: One of a List #

col IN (a, b, c) is a cleaner way to write col = a OR col = b OR col = c.

sql

-- Long form
SELECT id, status
FROM orders
WHERE status = 'paid'
   OR status = 'pending';

-- Short form with IN
SELECT id, status
FROM orders
WHERE status IN ('paid', 'pending');

NOT IN (...) does the opposite: keep rows whose value is not in the list.

BETWEEN: A Range, Inclusive #

col BETWEEN a AND b is equivalent to col >= a AND col <= b. Both endpoints are included.

sql

SELECT id, total
FROM orders
WHERE total BETWEEN 50 AND 200;
-- same as
-- WHERE total >= 50 AND total <= 200

Tip

BETWEEN is inclusive on both ends. That is almost always what you want for numeric ranges, and it is fine for true DATE columns too: BETWEEN '2026-01-01' AND '2026-01-31' covers all of January. The trap is TIMESTAMP columns, where '2026-01-31' means midnight at the start of that day, so anything later (like 2026-01-31 14:00:00) is excluded. For timestamps, >= start AND < next_start is the safe habit.

Warning

NOT IN with a nullable list silently returns zero rows.
If any value in the list (or any row in a subquery) is NULL, NOT IN becomes UNKNOWN for every row, and nothing matches. When there is any chance of NULL, prefer NOT EXISTS or filter nulls out explicitly.

Try It #

Run the query, then change the list values and the range bounds to see how the result changes.

Loading SQL editor...

Practice #

Return id, status, and total for orders where the status is either 'paid' or 'pending' and the total is between 50 and 400 inclusive. No ordering requirement.

Loading SQL editor...

Mistakes to Watch For #

Assuming BETWEEN excludes the upper bound. It doesn't.
Using BETWEEN on timestamps without thinking about time-of-day precision.
Using NOT IN (subquery) when the subquery can produce NULL; reach for NOT EXISTS instead.
Writing BETWEEN 100 AND 50: the low bound must be first, otherwise the range is empty and nothing matches.

Knowledge check #

3 questions

0 / 3 answered

WHERE col BETWEEN 1 AND 10 is equivalent to:
For "every order in 2026" using a created_at (timestamp) column, the most index-friendly form is:
BETWEEN works on which types?

Next Step #

Continue to LIKE to match text by pattern rather than exact value.