skip to content

What does the predicate x BETWEEN 10 AND 20 mean, and are both endpoints included?

level: juniorimportance: must knowfreq 80%

answer

  1. It is shorthand for two comparisons
  2. Ask yourself: are the ends in or out?
  3. Try it with the bounds written backwards
  4. Expands to >= low AND <= high

basics

~20 s

BETWEEN is inclusive at both ends: x BETWEEN 10 AND 20 means x >= 10 AND x <= 20, so 10 and 20 both match. The lower bound must be written first, or the predicate matches nothing.

solid answer

~40 s

`x BETWEEN 10 AND 20` is pure shorthand — the standard defines it as `x >= 10 AND x <= 20`. Both endpoints are **included**, which is the single most common thing candidates get wrong. The bounds are also **ordered**: the first operand is the low end and the second is the high end, so `x BETWEEN 20 AND 10` is `x >= 20 AND x <= 10`, which no value satisfies; the engine will not silently swap them for you. `NOT BETWEEN` negates the whole thing: `x < 10 OR x > 20`. BETWEEN works on anything comparable — numbers, dates, timestamps, strings — and for strings the range follows the column's collation order, not the alphabet you have in your head.

code

sql · 5 lines
sql
-- rows: score = 10, 15, 20, 25
SELECT score FROM results WHERE score BETWEEN 15 AND 20;  -- 15, 20
SELECT score FROM results WHERE score >= 15 AND score <= 20;  -- identical
SELECT score FROM results WHERE score NOT BETWEEN 15 AND 20;  -- 10, 25
SELECT score FROM results WHERE score BETWEEN 20 AND 15;  -- empty, not an error

go deeper

for a junior

Memorise the expansion: BETWEEN a AND b is >= a AND <= b, endpoints included. Be able to list which of a small set of sample values a given BETWEEN returns.

for a middle

Explain the ordered-bounds rule and why a reversed pair returns an empty result silently rather than erroring, and negate a BETWEEN correctly into a < OR > pair.

for a senior

Show the judgment of when not to use BETWEEN at all: continuous domains such as timestamps, where a half-open range is the correct idiom, and user-supplied bounds that need order validation.

for a principal

Frame it as a house rule: agree a team convention that ranges are half-open by default, so every range filter, report boundary and bucketing scheme composes without off-by-one arguments in review.

## What BETWEEN is `BETWEEN` is a range predicate, and it is defined in the SQL standard purely as syntax sugar. For an expression `X`, a low bound `Y` and a high bound `Z`: ```sql X BETWEEN Y AND Z -- means exactly: X >= Y AND X <= Z ``` There is no extra magic. Anything true of the expanded form is true of `BETWEEN`, which is why the reliable way to reason about a `BETWEEN` you are unsure of is to mentally expand it. ## Both endpoints are included The range is **closed** on both sides. Given rows with `score` values 10, 15, 20 and 25: ```sql SELECT score FROM results WHERE score BETWEEN 15 AND 20; -- returns 15 and 20 ``` English is the source of the confusion: "pick a number between 1 and 10" usually implies the ends are up for grabs, but "the rows between Monday and Friday" sometimes reads as exclusive. SQL has no ambiguity — both bounds are in. ## The bounds are ordered, and nothing reorders them The standard's default form is `BETWEEN ASYMMETRIC`: the first operand is the low bound, the second is the high bound. Write them backwards and you get a predicate that is `FALSE` for every non-NULL value: ```sql SELECT * FROM results WHERE score BETWEEN 20 AND 10; -- always empty ``` That is not an error and no engine warns about it, so a query built from application variables (`BETWEEN :from AND :to`) silently returns zero rows whenever the caller passes the pair the wrong way round. If your bounds come from user input, either validate the order in the application or normalise it in SQL with `LEAST`/`GREATEST`-style expressions where your engine offers them. The standard also defines `BETWEEN SYMMETRIC`, which sorts the two bounds for you; support for it varies by engine (PostgreSQL has it), so avoid it in code that must be portable. ## NOT BETWEEN `X NOT BETWEEN Y AND Z` is the negation of the whole conjunction, which by De Morgan is: ```sql X < Y OR X > Z ``` Note it is `OR`, not `AND`. Writing `X < Y AND X > Z` by hand is a classic bug that yields an empty result. ## NULLs If any of the three operands is NULL the predicate evaluates to UNKNOWN, and `WHERE` keeps only rows where the predicate is TRUE — so the row is dropped by both `BETWEEN` and `NOT BETWEEN`. That surprises people who expect `NOT BETWEEN` to return "everything else", including the NULL rows. If you want them, add `OR x IS NULL` explicitly. ## It is not only for numbers Any comparable type works: ```sql WHERE hire_date BETWEEN DATE '2024-01-01' AND DATE '2024-12-31' WHERE last_name BETWEEN 'A' AND 'M' ``` For character data the ordering is the collation's ordering. Whether `'m'` (lowercase) falls inside `'A' AND 'M'`, and where accented letters land, depends on the collation in effect for that column or expression — so a string `BETWEEN` that looks obviously correct on one database can return a different set on another. State that caveat if an interviewer pushes on it. ## Where BETWEEN is the right tool and where it isn't `BETWEEN` is a good fit for **discrete** domains where you can name the last legal value: integers, `DATE` columns, enumerated codes. It is a poor fit for **continuous** domains where the upper bound has values just below the next tick — most importantly `TIMESTAMP` columns, where `BETWEEN` an end date and its midnight silently drops most of that day. For those, the half-open form `col >= start AND col < next_start` is the safe idiom. The same reasoning applies to `DECIMAL` amounts with unknown scale. ## What to say in an interview Lead with the expansion (`>= AND <=`), state that both ends are inclusive, mention that the operands are ordered and a reversed pair yields no rows rather than an error, and finish with the one place you deliberately avoid `BETWEEN`: timestamp ranges.

  • How does NOT BETWEEN expand, and what happens to rows where the column is NULL?
    `x NOT BETWEEN a AND b` is `x < a OR x > b` — an OR, not an AND. If x (or either bound) is NULL, both `BETWEEN` and `NOT BETWEEN` evaluate to UNKNOWN, and WHERE keeps only TRUE, so the row is returned by neither. If you want NULL rows in the "outside the range" bucket, add `OR x IS NULL` yourself.
  • A report takes :from and :to from a user and filters with BETWEEN :from AND :to. What defensive step would you add?
    Guard the ordering. A reversed pair is not an error — it just returns nothing, which reads to the user as "no data" rather than "bad input". Validate in the application that from <= to, or normalise the pair before binding. Relying on `BETWEEN SYMMETRIC` is not portable enough for most codebases.
  • Does BETWEEN behave the same way on VARCHAR columns as on numbers?
    Structurally yes — it still expands to `>= AND <=` — but the ordering it uses is the collation's, not ASCII or the alphabet. Case sensitivity, accent handling and where digits and punctuation sort all depend on the collation in effect, so a string range can select different rows on two engines or two columns.

saying these in an interview costs you the question

  • Says BETWEEN excludes the endpoints
  • Thinks the engine swaps reversed bounds automatically
  • Expands NOT BETWEEN with AND instead of OR
  • Expects NOT BETWEEN to return rows where the column is NULL
  • Claims BETWEEN only works with numeric columns

context

resolving links…