SQL BETWEEN Operator Explained with Range Examples

A report asks for the students aged 20 to 25, and the first question is whether a student who is exactly 20 counts. With BETWEEN, both ends count: Age BETWEEN 20 AND 25 is true for 20, for 25 and for every age in between. It is a shorter way to write two comparisons:

SELECT column_name
FROM table_name
WHERE column_name BETWEEN low_value AND high_value;

-- the same condition, written out
WHERE column_name >= low_value AND column_name <= high_value

The facts behind most BETWEEN questions:

• Both ends are inside the range.
• The low value goes first. Bounds in the wrong order return no rows and no error.
• NOT BETWEEN returns the values outside the range, without the two ends.
• A row whose value is NULL matches neither BETWEEN nor NOT BETWEEN.
• On a timestamp column, a date used as the upper bound means midnight at the start of that day.

What the SQL BETWEEN operator does

Every engine defines BETWEEN as the pair of comparisons above: PostgreSQL 17, MySQL 8.4, SQLite, SQL Server and Oracle. Both comparisons allow equality, so a value sitting exactly on a bound is a match. The examples below ran on PostgreSQL 17.9, MySQL 9.1.0 and SQLite 3.50.4, which returned the same rows except where a section says otherwise.

BETWEEN with numeric values

Six students carry most of the examples:

CREATE TABLE students (
    ID   INT PRIMARY KEY,
    Name VARCHAR(20),
    Age  INT
);

INSERT INTO students VALUES
    (1, 'Alice', 20),
    (2, 'Bob',   22),
    (3, 'Carol', 25),
    (4, 'Dave',  19),
    (5, 'David', 31),
    (6, 'Eve',   NULL);

SELECT Name, Age
FROM students
WHERE Age BETWEEN 20 AND 25;

Alice is exactly 20 and Carol is exactly 25, and both are in the result. Dave and David fall outside the range, and Eve's age is NULL, so there is nothing to compare:

Write the bounds the other way round and the same query returns nothing:

SELECT Name, Age
FROM students
WHERE Age BETWEEN 25 AND 20;

No rows come back, and there is no error. The condition now reads Age >= 25 AND Age <= 20, and no number is both. When the bounds come from variables or a form, put the smaller one first in your code.

PostgreSQL has a variant, BETWEEN SYMMETRIC, that sorts the two bounds before it compares:

SELECT Name, Age
FROM students
WHERE Age BETWEEN SYMMETRIC 25 AND 20;

It returns Alice, Bob and Carol, the same three rows as BETWEEN 20 AND 25. MySQL rejects the query with ERROR 1064 (42000): You have an error in your SQL syntax, SQLite with near "25": syntax error, and SQL Server's documented BETWEEN syntax has no SYMMETRIC either. Outside PostgreSQL, sorting the bounds is your job.

Practice this: Lesson 3 of the SQL tutorial runs WHERE, AND, OR, IN, BETWEEN and LIKE on a shop database in your browser, with checked exercises.

Negating ranges with NOT BETWEEN

NOT BETWEEN asks for the values outside the range. Age NOT BETWEEN 20 AND 25 is the same as Age < 20 OR Age > 25, so 20 and 25 are excluded:

SELECT Name, Age
FROM students
WHERE Age NOT BETWEEN 20 AND 25;

Eve is in neither result. Comparing NULL with a number gives unknown rather than true or false, and WHERE keeps only the rows where the condition is true. If the rows without an age belong in the "outside" list, ask for them:

SELECT Name, Age
FROM students
WHERE Age NOT BETWEEN 20 AND 25 OR Age IS NULL;

SQL NULL values covers the rest of what NULL does to a condition.

BETWEEN with dates

A sales table stores the moment of each sale:

CREATE TABLE sales (
    SaleID  INT PRIMARY KEY,
    Product VARCHAR(20),
    SoldAt  TIMESTAMP
);

INSERT INTO sales VALUES
    (101, 'Laptop',  '2023-01-15 09:00:00'),
    (102, 'Phone',   '2023-03-20 14:30:00'),
    (103, 'Tablet',  '2023-03-31 16:45:00'),
    (104, 'Monitor', '2023-04-01 00:00:00');

The tablet sold on the afternoon of 31 March, and the monitor at midnight as 1 April began. The obvious query for the first quarter of 2023 loses the tablet:

SELECT *
FROM sales
WHERE SoldAt BETWEEN '2023-01-01' AND '2023-03-31';

The upper bound '2023-03-31' has no time, so the database reads it as 31 March at 00:00:00, and 16:45 that day is later. PostgreSQL's EXPLAIN shows the bound it actually compares with:

Filter: ((soldat >= '2023-01-01 00:00:00'::timestamp without time zone) AND (soldat <= '2023-03-31 00:00:00'::timestamp without time zone))

Moving the upper bound to the next day overcorrects:

SELECT *
FROM sales
WHERE SoldAt BETWEEN '2023-01-01' AND '2023-04-01';

PostgreSQL and MySQL return all four sales. The monitor was sold in April, and its time equals the new upper bound. SQLite returned three rows here, without the monitor. It has no date type and compares these values as text, and '2023-04-01 00:00:00' sorts after the shorter '2023-04-01'.

The version that is right on every engine starts at the first instant of the period and stops before the first instant of the next one:

SELECT *
FROM sales
WHERE SoldAt >= '2023-01-01' AND SoldAt < '2023-04-01';

BETWEEN is fine on a DATE column, which holds no time. On a timestamp column, use the >= and < pair.

BETWEEN with times

A log table records the time of day of each event:

CREATE TABLE logs (
    LogID     INT PRIMARY KEY,
    Event     VARCHAR(20),
    EventTime TIME
);

INSERT INTO logs VALUES
    (1, 'Login',  '08:30:00'),
    (2, 'Logout', '10:00:00'),
    (3, 'Backup', '10:30:00'),
    (4, 'Batch',  '23:15:00'),
    (5, 'Report', '01:30:00');

EventTime BETWEEN '08:00:00' AND '10:00:00' returns the login and the logout, which happened at exactly 10:00:00, and leaves out the backup half an hour later. A night window, from ten in the evening to two in the morning, puts the larger value first:

SELECT *
FROM logs
WHERE EventTime BETWEEN '22:00:00' AND '02:00:00';

No rows come back, because no time of day is both after 22:00 and before 02:00. The night is two ranges, one up to midnight and one after it, joined by OR:

SELECT *
FROM logs
WHERE EventTime >= '22:00:00' OR EventTime < '02:00:00';

BETWEEN with text

Text has an order too, so BETWEEN takes names. The result surprises at the upper end:

SELECT Name
FROM students
WHERE Name BETWEEN 'Alice' AND 'Dave';

David is missing. Text is compared character by character, and 'David' sorts after 'Dave' because i comes after e. To get every name from A to D, stop before the first E:

SELECT Name
FROM students
WHERE Name >= 'A' AND Name < 'E';

The order is the column's collation, the same rule that sorts ORDER BY Name.

BETWEEN vs LIKE and comparison operators

Each range has a natural way to write it:

LIKE matches a pattern in text, while BETWEEN asks about order. Choose between BETWEEN and the comparison pair by which ends you need included, not by speed: PostgreSQL's EXPLAIN for the first students query shows that it plans BETWEEN as the two comparisons:

Seq Scan on students
  Filter: ((age >= 20) AND (age <= 25))

The LIKE operator article covers patterns.

Combining BETWEEN with other SQL operators

BETWEEN is one condition, so it joins others in the WHERE clause with AND and OR, beside an IN list or a LIKE:

SELECT Name, Age
FROM students
WHERE Age BETWEEN 20 AND 25 AND Name LIKE 'C%';

The first AND belongs to BETWEEN, and the second joins the range to the LIKE condition. PostgreSQL's documentation notes this ambiguity and asks for parentheses around a bound that is a complex expression.

BETWEEN in UPDATE and DELETE statements

An UPDATE or a DELETE takes BETWEEN in its WHERE clause exactly as a SELECT does. Run the WHERE clause as a SELECT first, and the rows it lists are the rows the change will touch:

SELECT Name, Age
FROM students
WHERE Age BETWEEN 19 AND 20;

Both students sit on a bound. The DELETE with the same condition removes those two rows and no others:

DELETE FROM students
WHERE Age BETWEEN 19 AND 20;

In DbSchema's SQL Editor, an UPDATE or DELETE that you run with Run stays in an open transaction on the connected database. The result line says how many rows changed, and nothing is kept until you press Commit. If the count differs from what the SELECT showed, press Rollback.

Common mistakes and how to avoid them

Most wrong results from a range filter have one of these causes:

Practice corner

1. List the students aged 22 to 31, and say which rows sit on a bound.
2. List every sale from March 2023, with no sale of 31 March left out.
3. List the log events from 23:00 to 02:00.

Download DbSchema from https://dbschema.com/download.html, connect to your database, and run each answer in the SQL Editor. Connecting and the SQL Editor are in the free Community Edition.

FAQs

Is BETWEEN inclusive in every database?

PostgreSQL, MySQL, SQLite, SQL Server and Oracle all define x BETWEEN a AND b as x >= a AND x <= b, so both ends are included in each. The SQL Server and Oracle answer rests on their documentation, linked in the first section; the other three were also run.