BETWEEN (Range)

"Players with 500 to 750 runs." "Innings played between 25 and 31 March." Range questions are everywhere, and BETWEEN writes them in a short, readable way.

Definition

BETWEEN tests whether a value lies within a range. expr BETWEEN low AND high is TRUE when expr >= low AND expr <= high — both boundary values are included (it is an inclusive range).

Key words: inclusive — 500 and 750 themselves are kept. low first — the smaller value must come first, otherwise no row can match.

Syntax

SELECT column_list
FROM table_name
WHERE column_name BETWEEN low_value AND high_value;

-- opposite: outside the range
WHERE column_name NOT BETWEEN low_value AND high_value;

Example — runs from 500 to 750

SELECT *
FROM players
WHERE runs BETWEEN 500 AND 750;

BETWEEN includes BOTH ends: Rohit (exactly 500) and Virat (exactly 750) are kept. This is identical to WHERE runs >= 500 AND runs <= 750.

Example — NOT BETWEEN

SELECT *
FROM players
WHERE runs NOT BETWEEN 500 AND 750;

Example — BETWEEN with dates

SELECT player, match_date, runs
FROM innings
WHERE match_date BETWEEN '2025-03-25' AND '2025-03-31';

Both 25 March and 31 March are included. (BETWEEN tested match_date, so Dhoni's NULL runs do not matter here.)

BETWEEN on text

Text compares alphabetically, which can surprise you:

SELECT name FROM players WHERE name BETWEEN 'D' AND 'M';

MS Dhoni is missing! 'MS Dhoni' is greater than 'M' (it is longer and starts with M), so it is outside the range. For "names starting D to M" use name >= 'D' AND name < 'N'.

Common mistakes

• ❌ BETWEEN 750 AND 500 (high first) → returns 0 rows in SQL Server. ✅ always BETWEEN low AND high.
• ❌ Thinking BETWEEN excludes the ends. ✅ it is inclusive on both sides.
• ❌ Datetime trap: on a DATETIME column, BETWEEN '2025-03-25' AND '2025-03-31' stops at 31 March 00:00:00, missing anything later that day. ✅ use match_time >= '2025-03-25' AND match_time < '2025-04-01'.
• ❌ WHERE runs BETWEEN 500, 750 → a comma is not allowed. ✅ BETWEEN 500 AND 750.

Interview tip

Q: Is BETWEEN inclusive or exclusive in SQL Server? A: Inclusive on both ends: x BETWEEN a AND b means x >= a AND x <= b. NOT BETWEEN is exclusive: x < a OR x > b.