SQL BETWEEN Operator

BETWEEN checks whether a value falls within a range — inclusive of both endpoints.

Syntax

WHERE column BETWEEN low_value AND high_value

Sample Table: employees

Example

SELECT name, salary FROM employees
WHERE salary BETWEEN 50000 AND 70000;

Output:

Note 70000 is the upper bound and 65000 qualifies, but 71000 does not — BETWEEN is inclusive, so a salary of exactly 70000 would match; 71000 doesn't because it's above the range.

BETWEEN Is Equivalent To

WHERE salary >= 50000 AND salary <= 70000

BETWEEN is purely a readability shortcut — it doesn't do anything >=/<= can't.

BETWEEN with Dates

SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31';

Careful with datetime columns: if order_date is a DATETIME (not just DATE), '2024-03-31' means midnight at the start of that day — so an order placed at 3 PM on March 31st would be excluded. For datetime ranges, either cast to date or use '2024-03-31 23:59:59' as the upper bound.

NOT BETWEEN

WHERE salary NOT BETWEEN 50000 AND 70000

Common Mistakes

• Assuming BETWEEN excludes the endpoints — it's inclusive on both sides
• The datetime upper-bound trap described above — one of the most common real-world reporting bugs
• Using BETWEEN with the bounds in the wrong order (high, then low) — most databases return zero rows instead of erroring, which can go unnoticed

Interview Relevance

Q: "Is BETWEEN inclusive or exclusive?" — a simple but frequently asked check; follow it up by mentioning the datetime upper-bound gotcha to stand out.

Practice Question

Write a query to find all orders placed in the month of March 2024, being careful about the datetime boundary issue.

Related SQL Notes

• IN
• WHERE Clause
• Date Functions