DevLearningTools

LEARN · SQL

SQL BETWEEN

BETWEEN matches a value that falls within a range, inclusive on both ends. Learn the syntax, and a real gotcha when using it with dates.

← SQL INSQL Aliases →

Checking that a value falls within a range, salary between two numbers, a date within a month, is common enough to have its own operator: BETWEEN.

Learning Objectives

  • Use BETWEEN to match a value within an inclusive range.
  • Use NOT BETWEEN to exclude a range.
  • Recognize the specific gotcha BETWEEN has with date/time values that include a time component.

Basic BETWEEN

Both boundary values are included, a salary of exactly 85000 or exactly 95000 both match. BETWEEN syntax and inclusive behavior is identical across every database in this course.

MySQL / MariaDB / PostgreSQL / SQLite / SQL Server / Oracle
SELECT first_name, salary
FROM employees
WHERE salary BETWEEN 85000 AND 95000;

-- Equivalent to:
-- WHERE salary >= 85000 AND salary <= 95000;

NOT BETWEEN

Matches values outside the range, on either side, below 85000 or above 95000.

MySQL / MariaDB / PostgreSQL / SQLite / SQL Server / Oracle
SELECT first_name, salary
FROM employees
WHERE salary NOT BETWEEN 85000 AND 95000;

The Date/Time Gotcha

If order_date is a full timestamp column (date and time together, not just a date), '2026-01-31' is treated as midnight, 2026-01-31 00:00:00. An order placed at 3pm on January 31st, stored as 2026-01-31 15:00:00, is after that midnight boundary and gets silently excluded, even though "January 31st" sounds like it should obviously be included.

This can silently miss rows from January 31st
SELECT * FROM orders
WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31';
Safer: use < the first moment of the next day
SELECT * FROM orders
WHERE order_date >= '2026-01-01' AND order_date < '2026-02-01';
NOTE

This is a real-world bug, not a hypothetical, and it affects every database in this course identically, it's not a dialect difference, it's how BETWEEN's inclusive range interacts with any timestamp that has a time component. The >= / < pattern with the start of the following day sidesteps it entirely.

Try It Yourself

Write a query finding employees earning between 87000 and 98000, inclusive.

Answer
SELECT first_name, salary
FROM employees
WHERE salary BETWEEN 87000 AND 98000;

Frequently Asked Questions

Does the order of the two values in BETWEEN matter?

Yes, BETWEEN low AND high is expected. BETWEEN 95000 AND 85000 (high first) doesn't error, but matches zero rows on every database in this course, since no value can simultaneously be >= 95000 and <= 85000.

Can BETWEEN be used with text values, not just numbers?

Yes, WHERE last_name BETWEEN 'A' AND 'M' matches text that alphabetically falls in that range, useful but less common than numeric or date ranges.

Common Beginner Mistakes

Using BETWEEN with a date range and expecting the entire end day to be included

If the column stores a full timestamp, BETWEEN '...-01' AND '...-31' only includes up to midnight on the 31st, missing any row timestamped later that same day. Use >= start AND < (end + 1 day) instead.

Writing the high value first in BETWEEN

BETWEEN 95000 AND 85000 is valid syntax but matches nothing, it's always low value first, then AND, then high value.

Best Practices

  • Avoid BETWEEN for a date range on a timestamp column, use >= start AND < end-of-range instead to avoid silently dropping same-day rows.
  • Double-check the low-then-high order any time a BETWEEN range is built dynamically in application code.
  • Prefer BETWEEN over two separate >= and <= conditions when the column is a plain date or number, it reads more clearly.

Interview Questions

Why can WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31' miss orders placed on January 31st?

If order_date stores a timestamp, not just a date, '2026-01-31' in the BETWEEN comparison is interpreted as midnight at the very start of that day. Any order placed later on the 31st has a timestamp past that boundary and falls outside the range, excluded despite genuinely being a January 31st order.

Rewrite a BETWEEN date-range query to avoid the timestamp gotcha.

WHERE order_date >= '2026-01-01' AND order_date < '2026-02-01'; — using < the first moment of the following month includes every timestamp through the last possible instant of January 31st, regardless of time of day.

Summary

This lesson covered matching an inclusive range with BETWEEN and NOT BETWEEN, and a real, database-independent gotcha: BETWEEN on a timestamp column can silently exclude same-day rows after midnight on the end date.

What's Next?

The final lesson in this module covers aliases in full, column aliases (a recap) and table aliases, which become essential once joins are introduced.

</>

Ready to Practice SQL?

Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.

SQLite / PostgreSQL

Fast, no server, no ads.

Open DB Fiddle ↗

MySQL / SQL Server

Switch databases with one dropdown.

Open SQL Fiddle ↗

Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.

← SQL INSQL Aliases →