VMware ESXi and vSphere Cluster Management

SQL BETWEEN Operator: Filter Values Within a Range

Learn how SQL BETWEEN filters numeric, date, timestamp, and text values within inclusive ranges, including NULL behavior, NOT BETWEEN, and safe date-time patterns.

The SQL BETWEEN operator tests whether an expression falls within a specified range. It is commonly used in a WHERE clause to filter numeric amounts, dates, times, timestamps, or text values.

For an inclusive range, BETWEEN is a concise alternative to combining a greater-than-or-equal comparison with a less-than-or-equal comparison.

Basic BETWEEN syntax

SELECT column_list
FROM table_name
WHERE column_name BETWEEN lower_bound AND upper_bound;

The tested column or expression appears before BETWEEN. The lower bound is the first range value, normally the smallest or earliest permitted value. The upper bound is the second range value, normally the largest or latest permitted value.

For an introductory query, SELECT * returns every column:

SELECT *
FROM customers
WHERE amount BETWEEN 1200 AND 1900;

When only certain fields are needed, an explicit column list is usually preferable because it keeps the result focused:

SELECT customer_no, first_name, last_name, amount
FROM customers
WHERE amount BETWEEN 1200 AND 1900;

BETWEEN includes both endpoints

BETWEEN lower_bound AND upper_bound is inclusive. A value equal to the lower bound matches, and a value equal to the upper bound also matches.

For non-NULL comparable values, this:

amount BETWEEN 1200 AND 1900

has the same boundary meaning as this:

amount >= 1200 AND amount <= 1900

Numeric range filtering example

Suppose customers contains identifying fields, location fields, and an amount field:

This query selects customers whose amount is from 1200 through 1900:

SELECT *
FROM customers
WHERE amount BETWEEN 1200 AND 1900;

Customers 102 and 104 match because their amounts equal the lower and upper endpoints. Customers 103, 105, and 107 match because their amounts are inside the range. Customer 101 does not match because 1150 is below 1200, and customer 106 does not match because 2050 is above 1900. Customer 108 does not match because its amount is NULL.

Equivalent comparison form

You can write the same inclusive numeric condition explicitly:

SELECT *
FROM customers
WHERE amount >= 1200
  AND amount <= 1900;

The expanded form can be clearer when reviewing a complex condition because each comparison is visible. The two forms have the same inclusive boundary semantics for non-NULL comparable values.

Range ordering

Supply the lower bound first and the upper bound second:

amount BETWEEN 1200 AND 1900

Reversing ordinary numeric bounds generally produces no matches:

amount BETWEEN 1900 AND 1200

This is equivalent in effect to requiring a value to be both at least 1900 and at most 1200. No ordinary number can satisfy both conditions. Verify the range order before assuming that data is missing. The same principle applies to dates and other ordered values: use the earlier or smaller value first.

Filtering other value types

Date ranges

A date range is useful for a date-only column. This example selects orders dated from January 1 through January 31, 2026, including both dates:

SELECT order_id, order_date, total_amount
FROM orders
WHERE order_date BETWEEN DATE '2026-01-01' AND DATE '2026-01-31';

DATE '2026-01-01' is a typed date literal in SQL dialects that support this syntax. Literal syntax varies between database systems, so check the syntax for the database you use.

Timestamps and datetime values

A timestamp contains a date and a time. The time component affects the boundary. For example, an ending bound of 2026-01-31 00:00:00 includes only the first instant of January 31, not records later that day.

For a complete month, a safer timestamp pattern is often a half-open range: include the start and exclude the start of the next period.

SELECT order_id, created_at
FROM orders
WHERE created_at >= TIMESTAMP '2026-01-01 00:00:00'
  AND created_at <  TIMESTAMP '2026-02-01 00:00:00';

This pattern includes every timestamp from the beginning of January up to, but not including, February 1. It also works well for full days, months, and other reporting periods. It avoids guessing the final representable time of a day and avoids accidentally omitting fractional seconds.

Text ranges

BETWEEN can compare text, but the result follows the database's character set, comparison rules, and collation. A collation defines how text is compared and sorted, including possible rules for case and accents.

SELECT product_code, product_name
FROM products
WHERE product_code BETWEEN 'A100' AND 'A999';

Do not assume that text ordering matches simple human alphabetical expectations. Check case sensitivity, accented characters, padding, and the collation before relying on a text range. The bounds should be compatible with the data type of the tested expression.

NULL behavior

NULL represents missing or unknown data. It is not an ordinary number, date, or text value that can be placed into a sortable sequence.

• If the tested value is NULL, BETWEEN does not return true.
• If either bound is NULL, the predicate can evaluate to unknown.
• A normal WHERE clause returns only rows whose condition is true, so unknown results are not returned.

Use IS NULL when the goal is to find missing values:

SELECT customer_no, first_name, last_name
FROM customers
WHERE amount IS NULL;

If missing amounts should be included alongside a range, write that intention explicitly and use parentheses when necessary:

SELECT customer_no, first_name, amount
FROM customers
WHERE amount BETWEEN 1200 AND 1900
   OR amount IS NULL;

NOT BETWEEN

NOT BETWEEN selects comparable non-NULL values outside an inclusive range:

SELECT *
FROM customers
WHERE amount NOT BETWEEN 1200 AND 1900;

For ordinary comparable values, this is equivalent to:

amount < 1200 OR amount > 1900

Because BETWEEN includes its endpoints, NOT BETWEEN excludes values equal to 1200 or 1900. Its NULL behavior still requires separate consideration: a NULL amount does not satisfy NOT BETWEEN as true.

Combining BETWEEN with other filters

Use AND outside the BETWEEN expression to require another condition:

SELECT customer_no, first_name, last_name, country, amount
FROM customers
WHERE country = 'USA'
  AND amount BETWEEN 1200 AND 1900;

Here, a row must have country USA and an amount in the inclusive range.

Be deliberate when combining AND with OR. SQL commonly evaluates AND before OR, but parentheses make the intended logic clear:

SELECT customer_no, first_name, country, amount
FROM customers
WHERE (country = 'USA' OR country = 'Canada')
  AND amount BETWEEN 1200 AND 1900;

Without parentheses, a condition such as country = 'USA' OR country = 'Canada' AND amount BETWEEN 1200 AND 1900 can allow all USA rows, regardless of amount, because of operator precedence.

Performance and query design

• A range predicate on an indexed column can often be efficient because the database may be able to seek to the lower bound and scan through the range.
• Avoid wrapping the filtered column in a function or unnecessary cast when you expect an index to help. For example, compare a timestamp column directly with timestamp bounds instead of applying a date-extraction function to the column, when the intended logic permits it.
• Use bounds that are compatible with the column's data type. Implicit conversions can produce errors, unexpected comparisons, or poorer query plans depending on the database.
• In application code, use a parameterized query: supply bounds as bound parameters rather than concatenating user input into SQL text.

SELECT customer_no, amount
FROM customers
WHERE amount BETWEEN :minimum_amount AND :maximum_amount;

The exact parameter marker varies by programming language and database driver, but the principle is the same: keep SQL structure separate from input values.

Troubleshooting common problems

Expected boundary values are missing

Likely cause: the query used > and <, which exclude both endpoints. Check whether exact boundary values should match. Use BETWEEN lower_bound AND upper_bound or >= lower_bound AND <= upper_bound for inclusive behavior.

A numeric range returns no rows

Likely cause: the bounds were reversed. Confirm that the first value is the lower bound and the second is the upper bound.

Rows with missing amounts do not appear

Likely cause: the column contains NULL. Test with amount IS NULL. If the query must include missing values as well as ranged values, add a separately grouped OR amount IS NULL condition.

Final-day timestamp records are missing

Likely cause: the upper bound is midnight at the beginning of the final date. Inspect the column type and stored times. Prefer created_at >= start_timestamp AND created_at < next_period_start for a complete reporting period.

Text results seem unexpected

Likely cause: text comparison follows collation and sorting rules. Check collation, case sensitivity, accents, and the actual stored characters. Use text ranges only when those rules are understood.

The range query is slow

Likely cause: the column lacks a useful index, or a function or cast is applied to the filtered column. Review the execution plan, consider an index on the searched column, and compare the raw column with compatible bounds.

Exam-relevant notes

• BETWEEN is inclusive at both ends.
• The order is expression BETWEEN lower_bound AND upper_bound.
• BETWEEN a AND b corresponds to expression >= a AND expression <= b for non-NULL comparable values.
• NOT BETWEEN a AND b corresponds to expression < a OR expression > b; endpoint values are not selected.
• NULL does not satisfy a normal BETWEEN or NOT BETWEEN filter. Use IS NULL for missing values.
• For timestamp periods, an inclusive start and exclusive next-period boundary is often safer than an ending midnight with BETWEEN.
• Do not confuse the AND inside BETWEEN lower_bound AND upper_bound with a separate boolean condition, even though the keyword is the same.

Related SQL topic

Continue with SQL BETWEEN Operator for a concise reference to inclusive range filtering.