SQL BETWEEN

What is the SQL BETWEEN operator used for?

SQL BETWEEN returns rows where a value falls inclusively within a specified lower and upper bound.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL BETWEEN: Supported by PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite, Snowflake, BigQuery, Redshift, and all ANSI-compliant databases.

SQL BETWEEN Full Explanation

BETWEEN is a comparison operator used in the WHERE or HAVING clause to filter results to rows whose tested expression lies inclusively between two boundary values. The operator works with numeric, string, date, time, and timestamp data types as long as the expression and bounds are comparable.BETWEEN is evaluated as: expression >= lower_bound AND expression <= upper_bound. Because the bounds are inclusive, both endpoints are kept if they exist in the data set. NOT BETWEEN performs the opposite comparison.Null values in the tested expression or bounds yield UNKNOWN, so those rows are not returned unless IS NULL logic is added separately. The lower and upper bounds can be expressions, constants, or columns, but their evaluation order matters: most optimizers rewrite BETWEEN to two separate comparisons and may leverage indexes on the tested column for performance.If the lower bound is greater than the upper bound, the predicate always evaluates to false (no rows) in most implementations. Some dialects allow symmetric syntax (e.g., BETWEEN SYMMETRIC in PostgreSQL) to avoid this pitfall.BETWEEN is part of the SQL-92 core specification and is supported by virtually every relational database.

SQL BETWEEN Syntax

<expression> [NOT] BETWEEN <lower_bound> AND <upper_bound>;

SQL BETWEEN Parameters

expression - any comparable column or scalar expression to test
lower_bound - literal, column, or expression representing the inclusive minimum
upper_bound - literal, column, or expression representing the inclusive maximum

Example Queries Using SQL BETWEEN

-- Numeric range
SELECT id, salary
FROM employees
WHERE salary BETWEEN 60000 AND 90000;

-- Date range
SELECT order_id, order_date
FROM orders
WHERE order_date BETWEEN DATE '2023-01-01' AND DATE '2023-03-31';

-- NOT BETWEEN example
SELECT product_id, inventory_count
FROM products
WHERE inventory_count NOT BETWEEN 10 AND 100;

-- Using column bounds
SELECT t.*
FROM temperature_readings t
WHERE reading BETWEEN daily_min AND daily_max;

Expected Output Using SQL BETWEEN

Each query returns only the rows whose tested column values fall within the specified inclusive range (or outside the range for NOT BETWEEN)

Use Cases with SQL BETWEEN

Filter salaries, prices, ages, scores, or other numeric data within a target band
Select records that occurred between two timestamps
Quickly segment data for reporting periods, such as quarters or fiscal years
Exclude outliers by combining NOT BETWEEN with other predicates

Common Mistakes with SQL BETWEEN

Assuming BETWEEN is exclusive; both endpoints are included
Reversing the lower and upper bounds without USING SYMMETRIC support
Forgetting that NULL comparisons return UNKNOWN, thus excluding nulls unintentionally
Using BETWEEN on incomparable data types (e.g., mixing strings and numbers)

First Introduced In

SQL-92

Frequently Asked Questions

Is SQL BETWEEN inclusive?

Yes. The operator keeps rows that are greater than or equal to the lower bound and less than or equal to the upper bound.

Can I use BETWEEN with strings?

Absolutely. String comparisons use the database's collation rules, so ranges follow lexicographic order. Be mindful of case sensitivity.

How is NOT BETWEEN implemented internally?

Most optimizers rewrite NOT BETWEEN to two separate comparisons joined by OR, so indexes can still be used if applicable.

Does BETWEEN handle NULL values?

No. If the tested expression or either bound is NULL, the result is UNKNOWN, and the row is excluded unless explicitly checked with IS NULL.