BETWEEN in SQL — range filtering

What BETWEEN does and why it matters

You are halfway through a Stripe or DoorDash analyst interview and the interviewer says: "Pull me revenue for January 2026." You type WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31' and feel good about yourself. Then they ask: "Are you sure you got every order from January 31?" Suddenly the room is quiet. That single question has ended more analyst interviews than join syntax ever has.

BETWEEN is the range filter of SQL — a shorter, more readable way to write column >= low AND column <= high. It works on numbers, dates, strings, and timestamps, and the optimizer treats it identically to the equivalent pair of inequalities. But two properties trip people up under interview pressure: it is inclusive on both ends, and it silently truncates timestamps to midnight. Memorize those two facts and you will handle ninety percent of BETWEEN questions you ever see.

This guide walks through the syntax, the inclusive-bounds question that gets asked in every analyst loop, the timestamp trap that quietly drops a whole day of data, NOT BETWEEN, string comparisons, and a small set of optimization notes for when your filter is hitting a billion-row events table.

Syntax and inclusive bounds

The shape of the operator is fixed and short:

SELECT column_list
FROM table_name
WHERE column BETWEEN low AND high;

It is exactly equivalent to:

WHERE column >= low AND column <= high;

Both endpoints are included. A row where column = low matches. A row where column = high matches. There is no dialect-specific surprise here: Postgres, MySQL, SQL Server, Snowflake, BigQuery, Redshift, and ClickHouse all use closed intervals.

-- products priced from $100 through $500, both inclusive
SELECT product_id, price
FROM products
WHERE price BETWEEN 100 AND 500;

A product priced at exactly 100 is returned. A product priced at exactly 500 is returned. A product priced at 500.01 is not. This is why integer columns and money columns behave intuitively, but float columns can occasionally surprise you near the boundary.

BETWEEN with numbers, dates, and strings

The most common shapes you will see in production code:

-- orders between $1,000 and $5,000
SELECT order_id, user_id, amount
FROM orders
WHERE amount BETWEEN 1000 AND 5000;

-- users aged 25 to 35 inclusive
SELECT user_id, name, age
FROM users
WHERE age BETWEEN 25 AND 35;

-- rating range for a recommendation filter
SELECT product_id, rating
FROM reviews
WHERE rating BETWEEN 4.0 AND 5.0;

For dates, things look clean if the column is a true DATE and not a TIMESTAMP:

-- all events in January 2026, assuming event_date is DATE type
SELECT event_id, event_date, user_id
FROM events
WHERE event_date BETWEEN '2026-01-01' AND '2026-01-31';

String comparison is lexicographic and depends on collation, which is the rule for how characters compare:

-- alphabetical range over surnames; result depends on collation
SELECT user_id, last_name
FROM users
WHERE last_name BETWEEN 'A' AND 'M';

Case sensitivity, accented characters, and locale all influence what comes back. In practice, analysts rarely use BETWEEN on strings outside of one-off audits — LIKE or full-text search usually fits better.

The timestamp trap

This is the gotcha that every senior interviewer asks. Suppose created_at is a TIMESTAMP column, which is the default for event tables at most companies — Snowflake TIMESTAMP_NTZ, BigQuery TIMESTAMP, Postgres timestamptz, all behave the same way here.

-- BROKEN: silently drops most of January 31
SELECT order_id, created_at, amount
FROM orders
WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31';

Why is it broken? The literal '2026-01-31' is interpreted as 2026-01-31 00:00:00. Any order placed at 2026-01-31 00:00:01 or later — meaning almost every order from that day — fails the <= '2026-01-31 00:00:00' half of the comparison. You lose roughly one day out of every period you query, and if you query a single day, you see almost nothing.

The fix is to switch to a half-open interval using >= and <:

-- CORRECT: half-open interval captures all of January
SELECT order_id, created_at, amount
FROM orders
WHERE created_at >= '2026-01-01'
  AND created_at <  '2026-02-01';

This pattern — closed on the left, open on the right — is how every well-run data team writes time filters internally. It composes cleanly across months, weeks, and days, and it never needs you to know how many days are in the month or whether a year is a leap year.

The exact same trap applies to weekly or hourly windows:

-- whole hour, no off-by-one
SELECT event_id
FROM events
WHERE event_ts >= '2026-05-20 14:00:00'
  AND event_ts <  '2026-05-20 15:00:00';

NOT BETWEEN and inverted ranges

NOT BETWEEN excludes the range. It is equivalent to column < low OR column > high:

-- orders cheaper than $1,000 or more than $5,000
SELECT order_id, amount
FROM orders
WHERE amount NOT BETWEEN 1000 AND 5000;

Endpoints 1000 and 5000 are not in the result. A common real-world use is filtering out the business-hours range to surface off-hours activity, which can be useful for fraud or operations dashboards:

-- transactions outside the 9-to-18 business window (local time)
SELECT order_id, created_at
FROM orders
WHERE EXTRACT(HOUR FROM created_at) NOT BETWEEN 9 AND 18;

NOT BETWEEN interacts with NULL the same way BETWEEN does. If the column value is NULL, the expression evaluates to NULL, not TRUE, and the row is excluded. If you want to keep nulls, append OR column IS NULL explicitly.

Reversing the operands of BETWEEN does not "auto-swap" them. Writing WHERE age BETWEEN 35 AND 25 returns zero rows in every mainstream dialect, because the standard requires the first operand to be less than or equal to the second.

BETWEEN vs explicit comparisons

There is no performance gap between the two forms. The query planner rewrites BETWEEN to >= AND <= internally, indexes are used identically, and the execution plan is the same. The choice is about readability and correctness.

Some style guides require analysts to use only >= and < everywhere, even on integer columns, so that time windows and amount filters look identical. That is a defensible choice. Others use BETWEEN freely on integer and pure-date columns and reserve >= AND < for timestamps. Pick one and apply it consistently within a repo.

Common pitfalls

The most expensive mistake is the timestamp truncation described above. When created_at is a timestamp and you write BETWEEN '2026-01-01' AND '2026-01-31', you lose the bulk of January 31 because the right endpoint is parsed as 2026-01-31 00:00:00. Almost every dashboard built by a junior analyst has this bug somewhere, and almost every senior reviewer checks for it first. The fix is to use >= '2026-01-01' AND < '2026-02-01' — a closed-open interval that always captures the full intended period without needing to know the month length.

A subtler trap is flipping the operands. WHERE age BETWEEN 35 AND 25 returns an empty set, not an error. Reviewers will catch this in code review, but in an interview under time pressure it is easy to type the larger number first when you are thinking about a "between 35 and 25 years old" demographic. Always read your BETWEEN aloud as "low to high" before you run it.

BETWEEN with NULL is a third common stumbling point. NULL BETWEEN 1 AND 10 evaluates to NULL, which is treated as falsy by WHERE, so the row is excluded. If your business question is "users whose score is missing or between 1 and 10," you need WHERE score IS NULL OR score BETWEEN 1 AND 10 — being explicit costs nothing and avoids surprises in monthly reports.

Float boundary precision bites teams that store money or rates as FLOAT or DOUBLE. A value displayed as 5.00 might actually be 4.999999999998 after arithmetic, and BETWEEN 1.00 AND 5.00 will quietly miss it. Use NUMERIC or DECIMAL for currency, or widen the upper bound by an epsilon when you cannot change the schema.

The final pitfall is string-collation surprise. WHERE name BETWEEN 'a' AND 'M' looks reasonable but depends on whether the database treats uppercase and lowercase letters as ordered together or apart. Two clusters with different default collations will return different rows for the same query. Either normalize the comparison (LOWER(name) BETWEEN 'a' AND 'm') or avoid string BETWEEN for anything user-facing.

Optimization tips

BETWEEN plays well with indexes when the filter column is indexed and the bounds are constants. A B-tree index on created_at lets the planner do a single range scan rather than a full table scan, and the same applies to numeric columns. The planner can also push BETWEEN predicates into partition pruning on partitioned tables — Snowflake, BigQuery, and ClickHouse all use the range to skip entire partitions or micro-partitions, which is often a 10x to 100x speedup on event tables.

To get that pruning, the column on the left of BETWEEN must be the raw column, not a function applied to the column. Writing WHERE DATE(created_at) BETWEEN '2026-01-01' AND '2026-01-31' defeats the index because the planner sees a function of a column rather than the column itself. Rewrite as WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01' to keep the index alive.

On very large fact tables, consider a materialized view or pre-aggregated daily summary if your dashboards repeatedly query the same BETWEEN window. A nightly job that builds daily_orders cuts the dashboard's runtime from minutes to milliseconds, and the BETWEEN filter on the summary table is trivial.

Related reading

• SQL window functions interview questions
• GROUP BY and HAVING in SQL
• SQL antipatterns guide
• How to read EXPLAIN ANALYZE
• Pandas vs SQL for analysts

If you want to drill range filters, date arithmetic, and the kind of edge cases that make BETWEEN interesting, NAILDD ships with hundreds of SQL problems built from real analyst interview loops.

FAQ

Does BETWEEN include both endpoints?

Yes. BETWEEN low AND high is exactly >= low AND <= high. Both the lower and upper values are returned if they exist in the data. This is true in every mainstream dialect — Postgres, MySQL, SQL Server, Oracle, Snowflake, BigQuery, Redshift, and ClickHouse all use closed intervals — so you can rely on it across stacks.

Why does BETWEEN drop data when the column is a TIMESTAMP?

The right-hand literal is parsed as midnight at the start of that day. BETWEEN '2026-01-01' AND '2026-01-31' translates to >= 2026-01-01 00:00:00 AND <= 2026-01-31 00:00:00, which excludes anything that happened during January 31 after the first second. Use >= '2026-01-01' AND < '2026-02-01' to capture the entire month safely, and apply the same closed-open pattern for hours and weeks.

Is BETWEEN slower than >= and <=?

No. The query planner rewrites BETWEEN into the equivalent pair of inequalities before optimization, so the execution plan, index usage, and runtime are identical. Pick whichever form is more readable, and reserve >= AND < specifically for timestamp ranges where the half-open interval is mandatory.

Can I use BETWEEN with subqueries?

Yes. WHERE amount BETWEEN (SELECT MIN(amount) FROM orders) AND (SELECT MAX(amount) FROM orders) is valid syntax. In practice this pattern is rare — if the subquery is correlated, the optimizer has to evaluate it per row, and the readability cost is high. Most analysts compute the bounds once into a CTE and reference scalar values instead.

What happens with NULL?

NULL BETWEEN 1 AND 10 evaluates to NULL, which is treated as falsy in WHERE, so the row is excluded. The same applies to NOT BETWEEN. If you want to keep NULL rows in the result, add OR column IS NULL explicitly. This is one of the most common ways BETWEEN quietly drops rows that the analyst intended to keep.

Does BETWEEN work with floats?

Logically yes, but binary floating point introduces rounding error. A value that looks like 5.00 in your client might actually be stored as 4.9999999998 after arithmetic, and BETWEEN 1.00 AND 5.00 will miss it. For money, percentages, and anything where rounding matters, store the column as NUMERIC or DECIMAL rather than FLOAT or DOUBLE.