SQL BETWEEN & IN – Range and List Filtering

BETWEEN – Inclusive Range Filtering

BETWEEN low AND high returns rows where the column value is greater than or equal to low AND less than or equal to high. Both endpoints are included.

SQL

-- Numeric range (inclusive both ends)
SELECT * FROM products WHERE price BETWEEN 10 AND 50;
-- Equivalent to: WHERE price >= 10 AND price <= 50
-- Includes products priced exactly 10 or exactly 50

-- Age range
SELECT * FROM employees WHERE age BETWEEN 25 AND 40;

-- BETWEEN with integer ID ranges
SELECT * FROM orders WHERE id BETWEEN 1000 AND 2000;

ℹ️

BETWEEN is always inclusive on both ends

BETWEEN 10 AND 50 is exactly equivalent to >= 10 AND <= 50. The value 10 is included, and the value 50 is included. This is different from half-open ranges common in programming ([start, end)). Keep this in mind especially with date ranges — see the next section.

BETWEEN with Dates

Date BETWEEN is common, but you must be careful about timestamp precision at the end of the range.

SQL

-- DATE columns: clean and safe
SELECT * FROM events
WHERE event_date BETWEEN '2026-01-01' AND '2026-12-31';
-- Includes Jan 1 through Dec 31 — both dates included

-- DATETIME / TIMESTAMP: be careful — '2026-12-31' means '2026-12-31 00:00:00'
-- This MISSES orders placed on Dec 31 after midnight!
SELECT * FROM orders
WHERE created_at BETWEEN '2026-01-01' AND '2026-12-31';  -- BUG

-- CORRECT: use the last moment of the day, or use >= and <
SELECT * FROM orders
WHERE created_at BETWEEN '2026-01-01 00:00:00' AND '2026-12-31 23:59:59';

-- Even better: use >= and < (avoids microsecond precision issues)
SELECT * FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

NOT BETWEEN

NOT BETWEEN returns rows where the value falls outside the specified range.

SQL

-- Products NOT in the 10–50 price range
SELECT * FROM products WHERE price NOT BETWEEN 10 AND 50;
-- Equivalent to: WHERE price < 10 OR price > 50

-- Employees outside the mid-career age range
SELECT * FROM employees WHERE age NOT BETWEEN 30 AND 50;

-- Orders outside a date range
SELECT * FROM orders
WHERE created_at NOT BETWEEN '2026-01-01' AND '2026-06-30';

IN – List Membership Testing

IN checks whether a value matches any item in a list. It is much cleaner than chaining multiple OR conditions when testing one column against several values.

SQL

-- IN with strings
SELECT * FROM users WHERE country IN ('US', 'CA', 'GB', 'AU');
-- Equivalent (but messier): WHERE country = 'US' OR country = 'CA' OR ...

-- IN with numbers
SELECT * FROM products WHERE category_id IN (1, 3, 5, 7);

-- IN with subquery (find orders for active users)
SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users WHERE is_active = 1);

-- NOT IN with a clean list
SELECT * FROM employees WHERE department NOT IN ('Temp', 'Contract');

NOT IN and the NULL Gotcha

This is one of the most dangerous SQL traps: if a NULL exists anywhere in an IN list (or subquery), NOT IN returns no rows at all.

⚠️

NOT IN returns no rows if the list contains NULL

The logic behind this: x NOT IN (1, 2, NULL) expands to x <> 1 AND x <> 2 AND x <> NULL. But x <> NULL always evaluates to UNKNOWN. TRUE AND UNKNOWN = UNKNOWN. So no row can ever satisfy the condition — the result is always empty when NULL is in the list.

SQL

-- DANGER: if the subquery returns any NULL manager_id, this returns NOTHING
SELECT * FROM employees
WHERE manager_id NOT IN (SELECT manager_id FROM managers);
-- If managers.manager_id has even one NULL row → empty result set!

-- SAFE: use NOT EXISTS instead
SELECT * FROM employees e
WHERE NOT EXISTS (
  SELECT 1 FROM managers m
  WHERE m.manager_id = e.manager_id
);

-- Or: exclude NULLs explicitly from the subquery
SELECT * FROM employees
WHERE manager_id NOT IN (
  SELECT manager_id FROM managers WHERE manager_id IS NOT NULL
);

-- IN itself is safe with NULLs in the list (just doesn't match them):
SELECT * FROM users WHERE country IN ('US', NULL);
-- Returns rows where country = 'US'. The NULL in the list is harmless for IN.
-- (But the row where country IS NULL is NOT returned.)

IN vs OR – When to Use Each

Aspect	IN	Chained OR
Readability	Cleaner for 3+ values	Gets verbose quickly
Performance	Optimized well, often faster for long lists	Equivalent for short lists; optimizer may handle differently
Subquery support	Yes — `IN (SELECT ...)`	No
NULL handling	NOT IN with NULLs is dangerous (see above)	Explicit, no hidden NULL traps
Multiple columns	Yes — `IN ((col1, col2) IN (...))`	Need explicit AND/OR combinations

SQL

-- Use IN: clean and intention-clear
SELECT * FROM orders
WHERE status IN ('pending', 'processing', 'shipped');

-- The chained OR equivalent (harder to read for 3+ values)
SELECT * FROM orders
WHERE status = 'pending' OR status = 'processing' OR status = 'shipped';

-- IN with a subquery preview (advanced — covered in the subqueries section)
SELECT * FROM customers
WHERE id IN (
  SELECT DISTINCT customer_id
  FROM orders
  WHERE total > 1000
);

Summary

📋 Summary

BETWEEN low AND high is inclusive on both ends. Equivalent to >= low AND <= high.
For DATETIME ranges, use >= start AND < next_day to avoid missing the last day's data.
NOT BETWEEN returns values outside the range (equivalent to < low OR > high).
IN (val1, val2, ...) is cleaner than chained OR for same-column checks and supports subqueries.
NOT IN NULL trap: if the list or subquery contains any NULL, NOT IN returns zero rows. Use NOT EXISTS or filter NULLs out of the subquery.
IN is generally faster than OR for long lists and cleaner to read.

FAQ

Is BETWEEN inclusive or exclusive of the endpoint values? +

Always inclusive on both ends. BETWEEN 10 AND 50 is exactly >= 10 AND <= 50 — values of exactly 10 and exactly 50 are included. This is consistently defined in the SQL standard and applies in MySQL, PostgreSQL, SQLite, and SQL Server. It is a common source of off-by-one errors, especially with date ranges where the timestamp midnight on the end date can exclude records from that day.

Can I use BETWEEN with strings? +

Yes — BETWEEN works with any data type that supports ordering, including strings. WHERE last_name BETWEEN 'A' AND 'M' returns names that sort alphabetically between 'A' and 'M', inclusive. However, string BETWEEN behaves based on the column's collation and can be case-sensitive. In practice, numeric and date ranges are the most common use cases because the results are predictable. String BETWEEN is occasionally useful for searching within a known alphabetical segment.

How large can an IN list be? +

There is no SQL standard limit, but databases have practical limits. MySQL's limit is controlled by max_allowed_packet and query length — thousands of values are fine. PostgreSQL also handles large IN lists well but recommends using a temporary table or subquery for very large lists (1000+ values). In practice, if your IN list comes from another table, using a JOIN or a subquery (IN (SELECT ...)) is more efficient and maintainable than a hardcoded list of thousands of IDs.

Why does NOT IN return no rows when the subquery has a NULL? +

Because NOT IN is syntactic sugar for a series of <> comparisons joined by AND. When one of the values in the list is NULL, one of those comparisons becomes col <> NULL, which evaluates to UNKNOWN. And TRUE AND UNKNOWN = UNKNOWN, which fails the WHERE filter. So no row can return TRUE — every row evaluates to UNKNOWN or FALSE. The safe alternatives are: (1) NOT EXISTS with a correlated subquery, which handles NULLs correctly; or (2) add WHERE col IS NOT NULL inside the subquery to exclude NULLs from the list before NOT IN evaluates them.