By the end of this lesson you can filter with BETWEEN, NOT BETWEEN, IN and NOT IN, explain why BETWEEN is inclusive and when that breaks on timestamps, and predict what NOT IN does when NULL is involved.
Why it matters: Price bands, date periods and lists of statuses or codes appear in almost every report. BETWEEN and IN make those filters short and readable, but each hides a trap that silently loses rows.
Dataset: Wadi Market online store. Examples run in the editor on the right.
BETWEEN: a range, both ends included
x BETWEEN low AND high is shorthand for x >= low AND x <= high. Both boundaries are included. The low value must come first: BETWEEN 200 AND 100 is never true. (PostgreSQL also has BETWEEN SYMMETRIC, which accepts either order.) NOT BETWEEN low AND high keeps values outside the range, meaning x < low OR x > high.
WHERE column BETWEEN low AND high -- low <= column <= high
WHERE column NOT BETWEEN low AND high
WHERE column IN (value_1, value_2, value_3)
WHERE column NOT IN (value_1, value_2)SELECT product_name, unit_price
FROM products
WHERE unit_price BETWEEN 100 AND 200;Equivalent to unit_price >= 100 AND unit_price <= 200. A product priced exactly 200.00 is included, and so is one at 100.00. About fifteen products fall in this band.
BETWEEN and timestamps: the lost last day
On a date column, signup_date BETWEEN '2025-03-01' AND '2025-03-31' is fine: every day of March is included. On a timestamp column it's a trap. The upper bound '2025-03-31' means midnight at the start of 31 March, so every order placed later that day is excluded. The fix is the half-open range from the previous lessons: ordered_at >= '2025-03-01' AND ordered_at < '2025-04-01'. It is correct for dates and timestamps alike, so many teams use it everywhere.
SELECT order_id, ordered_at
FROM orders
WHERE ordered_at BETWEEN '2025-12-30' AND '2025-12-31';You might expect orders from both days, but the upper bound is 2025-12-31 00:00:00, so the result holds only orders from 30 December. All the orders of the 31st, placed after midnight, are missing. Change the WHERE to ordered_at >= '2025-12-30' AND ordered_at < '2026-01-01' and they appear.
IN: one of a list
status IN ('pending', 'paid', 'shipped') is shorthand for status = 'pending' OR status = 'paid' OR status = 'shipped'. It's shorter, harder to get wrong with brackets, and easy to extend. The values must have the same type as the column (text in quotes, numbers without). NOT IN (…) keeps rows whose value is in none of the list.
An IN list can also come from a query, customer_id IN (SELECT …), which you'll learn in the subqueries track.
SELECT order_id, ordered_at, channel, status
FROM orders
WHERE status IN ('pending', 'paid', 'shipped');An order is kept when its status matches any value in the list. These are the orders that haven't reached the customer yet; delivered, cancelled and returned orders are excluded.
The NOT IN and NULL trap
IN and NOT IN are built from = comparisons, and any comparison with NULL is not true (the next-but-one lesson explains why). Two consequences:
- A row whose column is NULL never passes
INorNOT IN.brand NOT IN ('Volt', 'Sahm')does not return unbranded products, even though NULL is clearly "not Volt or Sahm". - If the list itself contains a NULL,
NOT INreturns no rows at all.brand NOT IN ('Volt', NULL)meansbrand <> 'Volt' AND brand <> NULL, and the second part is never true. With a typed list this is rare; with a subquery that returns a NULL it happens all the time, and the result is silently empty.
SELECT product_name, brand
FROM products
WHERE brand NOT IN ('Volt', 'Sahm');Products of every other brand are returned, but the two unbranded products (brand NULL, such as the Laptop Backpack) are missing, because NULL NOT IN (…) is not true. Add OR brand IS NULL if they belong in the answer.
SELECT product_name, brand
FROM products
WHERE brand NOT IN ('Volt', NULL);This returns no rows at all. For every product, brand <> NULL is unknown, and an AND containing an unknown can never be true. Keep NULL out of NOT IN lists, and treat NOT IN with a subquery with suspicion until you've met NOT EXISTS.