Range Matching with BETWEEN

Explanation

Filtering for a range of values, like salaries between two amounts, or hire dates within a given year, is extremely common. You could write this with two comparisons joined by AND, but SQL gives you a more compact way to express it: the BETWEEN operator.

WHERE salary >= 50000 AND salary <= 80000

is exactly equivalent to:

WHERE salary BETWEEN 50000 AND 80000

Both include employees earning exactly $50,000 or exactly $80,000, BETWEEN is inclusive on both ends of the range.

Syntax

SELECT column_name
FROM table_name
WHERE column_name BETWEEN low_value AND high_value;

BETWEEN works with numbers, dates, and even text (using alphabetical ordering), though it's most commonly used with numbers and dates.

Interactive Example

Common Mistakes

• Putting the values in the wrong order. BETWEEN expects the lower value first and the higher value second. BETWEEN 100000 AND 50000 won't raise an error, it will simply match no rows.
• Assuming the boundaries are excluded. As covered above, both the low and high values are included in the results.
• Forgetting quotes around date literals. Dates are typically written as text strings, like '2020-01-01', and need quotes just like any other string value.

Best Practices

• Use BETWEEN whenever you're filtering a single column against a continuous range, it reads more clearly than two separate comparisons.
• Double check whether you actually want inclusive boundaries. If you need to exclude an endpoint, fall back to > or < instead of >= or <=.
• Be consistent with date formats (YYYY-MM-DD) to avoid ambiguous or misinterpreted date ranges.

Practice Question

Using the playground above, write a query that returns the first_name and hire_date of every employee hired between January 1, 2018 and December 31, 2019, inclusive.

Summary

BETWEEN lets you filter for values within an inclusive range, on both the low and high end, and works cleanly with numbers, dates, and text. It's a more readable alternative to writing two separate comparisons joined by AND.

FAQ