SQL online course

SQL BETWEEN Operator

Learn how SQL BETWEEN filters rows within an inclusive range, with numeric examples, endpoint behavior, NULL handling, and equivalent comparisons.

What the SQL BETWEEN Operator Does

BETWEEN is a SQL predicate: a condition evaluated for each row. It checks whether a column or expression falls between a lower value and an upper value.

BETWEEN is commonly used in a WHERE clause, the part of a query that determines which rows are returned. For example, you can use it to find customers whose spending falls within a target interval.

For a refresher on filtering rows, see SQL WHERE Clause.

Basic BETWEEN Syntax

SELECT * FROM table_name WHERE column_name BETWEEN value1 AND value2;

The parts of this pattern are:

  • column_name is the tested expression or column.
  • BETWEEN starts the range test.
  • value1 is the lower bound, or first value in the range.
  • AND separates the two boundary values.
  • value2 is the upper bound, or second value in the range.

For the intended range, place the lower bound first and the upper bound second.

BETWEEN Includes Both Endpoints

BETWEEN describes an inclusive range. This means that values exactly equal to either boundary match the condition.

amount BETWEEN 1200 AND 1900 matches amounts that are at least 1200 and at most 1900. Therefore, 1200 and 1900 both satisfy the condition, while 1199 and 1901 do not.

Boundary behavior

  • 1199: no match because it is below the lower bound.
  • 1200: match because the lower endpoint is included.
  • 1501: match because it is inside the range.
  • 1900: match because the upper endpoint is included.
  • 1901: no match because it is above the upper bound.
  • NULL: no match because NULL is not a known numeric value.

Numeric Range Filtering Example

Suppose a customers table contains customer numbers, names, locations, and an amount column. This query returns customers whose amount is from 1200 through 1900:

SELECT * FROM customers WHERE amount BETWEEN 1200 AND 1900;

Values below 1200 are excluded because they do not reach the lower bound. Values above 1900 are excluded because they exceed the upper bound.

Sample customers data

No | Last Name | First Name | City    | Country       | Amount
1  | Smith     | Anna       | London  | UK            | 1166
2  | Brown     | Ben        | Paris   | France        | 1216
3  | Garcia    | Carla      | Madrid  | Spain         | 1286
4  | Jones     | David      | Rome    | Italy         | 1501
5  | Lee       | Eva        | Berlin  | Germany       | 1621
6  | Patel     | Farah      | Dublin  | Ireland       | 1286
7  | Wilson    | Greg       | Oslo    | Norway        | 1900
8  | Martin    | Hana       | Lisbon  | Portugal      | 2000
9  | Clark     | Ian        | Vienna  | Austria       | 2200
10 | Young     | Jo         | Prague  | Czechia       | 5200
11 | King      | Kai        | Zurich  | Switzerland   | NULL

The query returns rows 2 through 7. It includes amounts such as 1216, 1286, 1501, 1621, and 1900. Both customers with an amount of 1286 are returned because every qualifying row is included, even when values repeat.

Result set for 1200 through 1900

No | Last Name | First Name | City   | Country | Amount
2  | Brown     | Ben        | Paris  | France  | 1216
3  | Garcia    | Carla      | Madrid | Spain   | 1286
4  | Jones     | David      | Rome   | Italy   | 1501
5  | Lee       | Eva        | Berlin | Germany | 1621
6  | Patel     | Farah      | Dublin | Ireland | 1286
7  | Wilson    | Greg       | Oslo   | Norway  | 1900

Equivalent Comparison Logic

For ordinary comparable values, BETWEEN expresses the same intended condition as a greater-than-or-equal-to test combined with a less-than-or-equal-to test:

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

The lower comparison uses >=, and the upper comparison uses <=. These operators make the inclusive endpoints explicit. The equivalent logic is:

lower_value <= amount AND amount <= upper_value

If the bounds are reversed, such as BETWEEN 1900 AND 1200, the condition does not describe the intended interval and will commonly return no rows. Always put the lower value first.

For more practice combining conditions, see SQL AND and OR Operators.

NULL Values and BETWEEN

NULL represents a missing or unknown value. It is not the same as zero, an empty string, or any other numeric value. SQL cannot determine that an unknown amount is inside a numeric range, so a row with amount = NULL does not match:

SELECT * FROM customers WHERE amount BETWEEN 1200 AND 1900;

The customer whose amount is NULL is therefore absent from the result. If you need to review missing amounts separately, test for NULL explicitly:

SELECT * FROM customers WHERE amount IS NULL;

If a report should include both the numeric range and missing amounts, combine the conditions deliberately:

SELECT * FROM customers
WHERE amount BETWEEN 1200 AND 1900
   OR amount IS NULL;

Compatible Range Values and Data Types

The tested column and both bounds should contain comparable values of compatible data types. Numeric columns should generally be compared with numeric bounds. Comparing incompatible types can cause an error, implicit conversion, or unexpected results depending on the database system.

BETWEEN is commonly used with numbers, but it can also apply to:

  • Date and time values: for example, rows between two dates, subject to the database's date and time comparison rules.
  • Text values: for example, values in a lexicographic order, subject to the database's collation and sorting rules.

When filtering timestamps, remember that a timestamp includes a time component. A date boundary may not include every time on that date unless the bounds are chosen carefully.

BETWEEN Compared with IN

BETWEEN tests a continuous interval. It includes every comparable value between the lower and upper bounds.

IN tests membership in an explicit list of values. Use it when only specific exact values should match:

SELECT * FROM customers WHERE amount IN (1200, 1501, 1900);

This query does not match every amount between 1200 and 1900. It matches only 1200, 1501, and 1900. Learn more in SQL IN Operator.

Other WHERE-clause predicates, such as equality, pattern matching, and NULL tests, solve different filtering needs. Choose the predicate that describes the rule you actually want.

Checking Endpoint Inclusion

This query makes the endpoint behavior easy to verify:

SELECT * FROM customers WHERE amount BETWEEN 1286 AND 1501;

Rows with amount exactly 1286 are included, and rows with amount exactly 1501 are also included. Values between those endpoints match as well.

Troubleshooting BETWEEN Queries

Expected boundary values are missing

If values equal to an endpoint are missing, check whether the query uses strict comparisons:

amount > 1200 AND amount < 1900

Strict > and < comparisons exclude the endpoints. Use BETWEEN, or use >= for the lower bound and <= for the upper bound.

A row with a missing amount is not returned

This is expected when the amount is NULL. Handle missing values separately with IS NULL if they must be included or reviewed.

The query returns no rows or unexpected rows

Check that the lower bound comes first, the upper bound comes second, and both bounds are compatible with the tested column's data type. Also check whether text, dates, or timestamps are being compared according to the database's ordering rules.

BETWEEN matches too many values

BETWEEN includes every comparable value in the interval. If only a few exact values should match, replace it with IN.

Key Points

  • BETWEEN is a predicate typically used in a WHERE clause.
  • Its pattern is expression BETWEEN lower_bound AND upper_bound.
  • The lower and upper endpoints are both included.
  • BETWEEN is equivalent to >= lower_bound AND <= upper_bound for compatible values.
  • NULL does not match an ordinary BETWEEN test.
  • Use compatible data types and place the lower bound before the upper bound.
  • Use IN instead when checking a discrete list of exact values.