SQL Between

The BETWEEN operator is a shorthand way to test whether a value falls within a range. Instead of writing two separate comparisons joined by AND, you can express a range check in a single, readable clause. It works with numbers, dates, and even text, and it is one of the most commonly used filtering tools in SQL because range queries (prices between two amounts, dates within a quarter, ages within a bracket) come up constantly in real applications.

Overview / How It Works

BETWEEN is used inside a WHERE (or HAVING) clause to check whether a column’s value lies within two boundaries. Under the hood, the database engine does not treat BETWEEN as a special operator with its own execution path — it simply rewrites the condition into two comparisons joined with AND. When you write:

WHERE price BETWEEN 20 AND 50

the query planner evaluates it exactly as if you had written:

WHERE price >= 20 AND price <= 50

This has an important consequence: BETWEEN is inclusive on both ends. A row with price = 20 or price = 50 will match. This trips up many beginners who assume it behaves like a strict, exclusive range.

Because BETWEEN compiles down to two ordinary comparisons, the same rules for indexes apply. If the column has an index, the engine can use a range scan to jump straight to the starting boundary and read forward until it passes the ending boundary, rather than scanning every row in the table. This makes BETWEEN just as efficient as writing the two comparisons manually — it is purely a readability convenience, not a performance feature by itself.

BETWEEN can be negated with NOT BETWEEN, which matches every row outside the range (again, the boundaries themselves are excluded from the match when negated, since they are part of the range that gets excluded).

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE column_name BETWEEN low_value AND high_value;

Part	Meaning
`column_name`	The column being tested against the range
`low_value`	The lower boundary of the range (inclusive)
`high_value`	The upper boundary of the range (inclusive)
`NOT BETWEEN`	Matches rows outside the range instead of inside it

BETWEEN works with numeric columns, date/text columns storing dates in a sortable format (such as YYYY-MM-DD), and plain text columns (using alphabetical ordering). The low_value must always be written before the high_value — SQL does not automatically reorder them for you.

Examples

Example 1: Filtering products by price range

CREATE TABLE products (
  product_id INTEGER,
  product_name TEXT,
  price REAL,
  category TEXT
);

INSERT INTO products VALUES
  (1, 'Keyboard', 25.00, 'Hardware'),
  (2, 'Monitor', 150.00, 'Electronics'),
  (3, 'Mouse', 15.00, 'Hardware'),
  (4, 'Webcam', 45.00, 'Electronics'),
  (5, 'Laptop Stand', 35.00, 'Hardware');

SELECT product_name, price
FROM products
WHERE price BETWEEN 20 AND 50;

Result:

product_name	price
Keyboard	25.00
Webcam	45.00
Laptop Stand	35.00

Only products priced from 20 up to 50, inclusive, are returned. The Monitor (150.00) and Mouse (15.00) fall outside the range and are excluded.

Example 2: Filtering orders by date range

CREATE TABLE orders (
  order_id INTEGER,
  customer_id INTEGER,
  order_date TEXT,
  amount REAL
);

INSERT INTO orders VALUES
  (1, 1, '2024-01-15', 250.00),
  (2, 2, '2024-03-22', 100.50),
  (3, 1, '2024-07-05', 75.25),
  (4, 3, '2024-11-30', 320.00);

SELECT order_id, order_date, amount
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-06-30';

Result:

order_id	order_date	amount
1	2024-01-15	250.00
2	2024-03-22	100.50

Because the dates are stored as text in the sortable YYYY-MM-DD format, ordinary string comparison also happens to be chronological comparison, so BETWEEN correctly picks out orders from the first half of 2024. Orders from July and November fall outside the range and are excluded.

Example 3: Excluding a range with NOT BETWEEN

CREATE TABLE employees (
  id INTEGER,
  name TEXT,
  department TEXT,
  salary REAL,
  hire_date TEXT
);

INSERT INTO employees VALUES
  (1, 'Alice', 'Engineering', 95000, '2019-03-01'),
  (2, 'Bob', 'Sales', 62000, '2021-07-15'),
  (3, 'Carla', 'Marketing', 58000, '2020-01-10'),
  (4, 'Dan', 'Engineering', 110000, '2018-05-20');

SELECT name, salary
FROM employees
WHERE salary NOT BETWEEN 60000 AND 100000;

Result:

name	salary
Carla	58000
Dan	110000

Alice (95000) and Bob (62000) fall inside the 60000–100000 band, so they are excluded by NOT BETWEEN. Carla is below the range and Dan is above it, so both are returned.

How It Works Step by Step / Under the Hood

When the database engine processes a query containing BETWEEN, it follows the standard logical query-processing order:

FROM — the source table (and any joins) is identified.
WHERE — each row is tested against the condition. column BETWEEN low AND high is evaluated as column >= low AND column <= high, so both comparisons must be true for the row to survive.
GROUP BY / HAVING — if present, rows that passed WHERE are grouped, and HAVING can also use BETWEEN to filter on aggregated values.
SELECT — the requested columns are produced for the surviving rows.
ORDER BY / LIMIT — the result set is sorted and optionally trimmed.

If the column used in the BETWEEN condition is indexed, the optimizer can perform a range scan: it seeks directly to the row matching low_value in the index and reads sequentially until it passes high_value, avoiding a full table scan. This is why range filters on indexed date or numeric columns (such as filtering an orders table by order_date) tend to be fast even on large tables.

Common Mistakes

Mistake 1: Forgetting that BETWEEN is inclusive

SELECT product_name, price
FROM products
WHERE price BETWEEN 25 AND 45;

Some developers expect the boundary values to be excluded, similar to some programming language range functions. In SQL, both endpoints are included. Running this against the products table from Example 1 returns Keyboard (25.00) and Webcam (45.00) — the exact boundary values — along with Laptop Stand (35.00). If you actually want an exclusive range, you must write it explicitly with > and < instead of BETWEEN.

Mistake 2: Writing the bounds in the wrong order

SELECT product_name, price
FROM products
WHERE price BETWEEN 50 AND 20;

This is not a syntax error — it runs without complaint — but it silently returns zero rows, because the engine translates it to price >= 50 AND price <= 20, a condition no value can satisfy. The fix is simply to put the smaller value first:

SELECT product_name, price
FROM products
WHERE price BETWEEN 20 AND 50;

This is a common bug when the boundary values come from variables or user input and get swapped by mistake — always double-check which value is smaller before building the query.

Mistake 3: Omitting the AND

SELECT product_name FROM products WHERE price BETWEEN 20;

BETWEEN always requires exactly two boundary values joined with AND. Leaving out the upper bound and the AND keyword produces a syntax error and the query will not run at all. The correct form always includes both bounds: WHERE price BETWEEN 20 AND 50.

Best Practices

Always write the lower value first and the higher value second — SQL does not reorder them for you.
Remember that BETWEEN is inclusive on both ends; use > and < if you need an exclusive range.
When filtering date ranges, store dates in a sortable text format (YYYY-MM-DD) or a native date/datetime type so that string or chronological comparison lines up correctly.
Be careful with date-range boundaries that include a time component: BETWEEN '2024-01-01' AND '2024-01-31' may miss rows timestamped later on January 31st (e.g. '2024-01-31 14:00:00') if the column stores time as well as date; consider using < '2024-02-01' as the upper bound instead.
Index columns that are frequently filtered with BETWEEN, especially in large tables, so the engine can use a range scan instead of a full scan.
Use NOT BETWEEN sparingly and test it carefully, since negated range conditions can be less intuitive to read than an equivalent pair of OR conditions.

Practice Exercises

Using the products table from Example 1, write a query that returns all products priced between 15 and 35, inclusive.
Using the employees table from Example 3, write a query that returns the names of employees hired between '2019-01-01' and '2020-12-31'.
Using the orders table from Example 2, write a query using NOT BETWEEN to find orders with an amount outside the range 100 to 300.

Summary

BETWEEN low AND high is shorthand for column >= low AND column <= high.
Both boundary values are inclusive — rows exactly equal to either bound are included in the result.
The lower value must be written first; reversing the order produces zero matching rows instead of an error.
NOT BETWEEN returns rows outside the range, again excluding the boundary values from the result.
BETWEEN works on numbers, sortable date/text values, and plain text, and can benefit from an index on the filtered column.

SQL Between

Overview / How It Works

Syntax

Examples

Example 1: Filtering products by price range

Example 2: Filtering orders by date range

Example 3: Excluding a range with NOT BETWEEN

How It Works Step by Step / Under the Hood

Common Mistakes

Mistake 1: Forgetting that BETWEEN is inclusive

Mistake 2: Writing the bounds in the wrong order

Mistake 3: Omitting the AND

Best Practices

Practice Exercises

Summary

Table of Contents