BETWEEN Operator in SQL: Range Queries on Numbers and Dates

Filtering for a range of values — products priced between 100 and 500, orders placed between two dates — is one of the most common filtering needs in everyday SQL. While this can always be written with >= and <= combined using AND, BETWEEN offers a cleaner, more readable shorthand for exactly this pattern.

What Is the BETWEEN Operator?

BETWEEN is a SQL operator used in a WHERE clause to check whether a column's value falls within a specified range, inclusive of both boundary values. WHERE price BETWEEN 100 AND 500; is functionally equivalent to WHERE price >= 100 AND price <= 500;, but reads more naturally for this specific use case.

What You'll Learn

• Use BETWEEN to filter numeric columns within a range.
• Apply BETWEEN to date columns for date-range filtering.
• Understand that BETWEEN's boundaries are inclusive on both ends.
• Use NOT BETWEEN to exclude a range instead of including it.

Key Terms to Know

• BETWEEN: An operator checking whether a value falls within an inclusive range.
• Inclusive boundary: A range boundary value that is itself included as a match, not excluded.
• NOT BETWEEN: The inverse of BETWEEN, matching values outside the specified range.

BETWEEN on Numeric Columns

WHERE price BETWEEN 100 AND 500; returns every row where price is greater than or equal to 100 AND less than or equal to 500 — both 100 and 500 themselves would match. This inclusive behavior is important to remember, since some range concepts in everyday language are ambiguous about whether boundaries count.

BETWEEN on Date Columns

WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31'; returns every order placed during January 2026, inclusive of both January 1st and January 31st. This only works reliably when order_date is properly typed as DATE or DATETIME — a reminder of why correct data typing, covered earlier in this module, matters in practice.

A subtle gotcha with DATETIME columns: BETWEEN '2026-01-01' AND '2026-01-31' technically means up to '2026-01-31 00:00:00', so any timestamps later that same day (like 2:00 PM) would be excluded. Using '2026-01-31 23:59:59' as the upper bound, or comparing against '2026-02-01' with <, avoids this trap.

NOT BETWEEN: Excluding a Range

WHERE price NOT BETWEEN 100 AND 500; returns every row where price is either strictly less than 100 or strictly greater than 500 — the exact inverse of BETWEEN, and also exclusive of the boundary values themselves on the 'not matching' side, though the boundaries 100 and 500 are still excluded from the result since they were matched by the original BETWEEN range.

Visual Summary

Picture a number line with two markers placed on it, say 100 and 500. BETWEEN highlights everything sitting on or between those two markers, including the markers themselves. NOT BETWEEN highlights everything outside that highlighted stretch, on either side of the two markers.

BETWEEN vs Manual Range Comparison

Using BETWEEN	Equivalent Manual Form
price BETWEEN 100 AND 500	price >= 100 AND price <= 500
order_date BETWEEN '2026-01-01' AND '2026-01-31'	order_date >= '2026-01-01' AND order_date <= '2026-01-31'
price NOT BETWEEN 100 AND 500	price < 100 OR price > 500

SQL Example

-- Numeric range: mid-priced products
SELECT product_name, price
FROM products
WHERE price BETWEEN 100 AND 500;

-- Date range: orders placed in January 2026
SELECT order_id, order_date
FROM orders
WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31';

-- NOT BETWEEN: products outside the typical mid-price range
SELECT product_name, price
FROM products
WHERE price NOT BETWEEN 100 AND 500;

The first query finds mid-range priced products, including any priced at exactly 100 or 500 due to BETWEEN's inclusive boundaries. The second filters orders to a specific calendar month. The third inverts the logic entirely, surfacing budget and premium products that fall outside the typical mid-price band.

Real-World Examples

• E-commerce price filter sliders translate directly into a BETWEEN condition behind the scenes once a user selects a min and max price.
• Financial reporting tools use BETWEEN on transaction_date to scope monthly, quarterly, or annual reports precisely.
• HR systems use BETWEEN on hire_date to identify employees who joined within a specific onboarding cohort window.
• Age-based eligibility checks use BETWEEN on a calculated age value to determine qualification for age-restricted offers or services.

Best Practices and Pro Tips

• Remember BETWEEN's boundaries are always inclusive on both ends — if you need an exclusive boundary, you'll need to fall back to explicit > and < comparisons instead.
• For DATETIME range queries spanning a full day or month, prefer comparing with < on the day after the range ends (e.g., order_date < '2026-02-01') rather than relying on '23:59:59', which avoids subtle timestamp-truncation bugs entirely.
• Use BETWEEN specifically when it improves readability over manual >= / <= comparisons — for a single-sided range (just a minimum or maximum), a plain comparison operator is simpler and clearer.

Common Mistakes to Avoid

• Forgetting that BETWEEN's boundaries are inclusive, leading to off-by-one errors when an exclusive range was actually intended.
• Using BETWEEN on a DATETIME column with only a date (no time) as the upper bound, accidentally excluding any timestamps later that same day.
• Reversing the order of the two BETWEEN values (e.g., BETWEEN 500 AND 100), which returns no rows since MySQL expects the lower bound first.
• Using BETWEEN on a VARCHAR-stored date column, which compares dates alphabetically rather than chronologically, the same risk noted in the WHERE clause lesson.

Interview Questions

Q1. Are the boundary values in a BETWEEN range included or excluded from the result?

Included. BETWEEN is inclusive on both ends, meaning WHERE price BETWEEN 100 AND 500 matches rows where price is exactly 100 or exactly 500, as well as everything in between.

Q2. What's a common pitfall when using BETWEEN with DATETIME columns and date-only boundary values?

Since a date-only value like '2026-01-31' is interpreted as midnight ('2026-01-31 00:00:00'), any DATETIME values later that same day are excluded from the range. Using the next day with < avoids this issue entirely.

Q3. How is NOT BETWEEN different from BETWEEN?

NOT BETWEEN returns rows whose value falls outside the specified range, the exact logical inverse of BETWEEN, which returns rows whose value falls within the range.

Q4. Write the manual equivalent of WHERE stock_qty BETWEEN 5 AND 20.

WHERE stock_qty >= 5 AND stock_qty <= 20, which produces the identical inclusive range result as the BETWEEN version.

Practice MCQs

1. What does WHERE price BETWEEN 100 AND 500 include?

1. Only values strictly greater than 100 and less than 500
2. Values from 100 to 500, including both 100 and 500
3. Only the value 100
4. Values outside the 100-500 range

Answer: B. Values from 100 to 500, including both 100 and 500

Explanation: BETWEEN is inclusive on both boundary values, so both 100 and 500 themselves satisfy the condition.

2. Which is the correct manual equivalent of WHERE x BETWEEN 10 AND 20?

1. WHERE x > 10 AND x < 20
2. WHERE x >= 10 AND x <= 20
3. WHERE x = 10 OR x = 20
4. WHERE x != 10 AND x != 20

Answer: B. WHERE x >= 10 AND x <= 20

Explanation: BETWEEN's inclusive range exactly matches a combination of >= for the lower bound and <= for the upper bound.

3. What is a safer alternative to BETWEEN '2026-01-01' AND '2026-01-31' for a DATETIME column covering all of January?

1. order_date BETWEEN '2026-01-01' AND '2026-01-31 23:59:59' or order_date < '2026-02-01'
2. order_date = '2026-01'
3. order_date BETWEEN 1 AND 31
4. order_date != '2026-02-01'

Answer: A. order_date BETWEEN '2026-01-01' AND '2026-01-31 23:59:59' or order_date < '2026-02-01'

Explanation: Either extending the upper bound to the very end of the day or comparing against the start of the next month with < avoids excluding timestamps later in the last day.

Quick Revision Points

• BETWEEN is inclusive of both boundary values.
• NOT BETWEEN returns values strictly outside the specified range.
• BETWEEN on DATETIME with date-only bounds can silently exclude later timestamps on the end date.
• BETWEEN is shorthand for combining >= and <= with AND.

Conclusion

• BETWEEN is a readability improvement over manual range comparisons, not a different capability altogether.
• Its inclusive boundaries are a small detail that matters a lot in practice.
• Date-range queries deserve extra care to avoid silently excluding valid timestamps.

BETWEEN provides a clean, readable shorthand for inclusive range filtering on both numeric and date columns, equivalent to combining >= and <= with AND. Its inclusive boundaries and the specific gotchas around DATETIME ranges are details worth internalizing, since they're easy to get subtly wrong. The next lesson covers IN and NOT IN, the equivalent shorthand for matching a column against a list of specific values rather than a continuous range.