BETWEEN ... AND ...

-- Retrieves rows where the value is greater than or equal to low and less than or equal to high (both endpoints included).
SELECT column FROM table_name WHERE column BETWEEN low AND high;

-- Retrieves rows where the value is outside the range (NOT BETWEEN).
SELECT column FROM table_name WHERE column NOT BETWEEN low AND high;

-- Can also be used in a HAVING clause (filtering after aggregation).
SELECT group_column, aggregate_function
FROM table_name
GROUP BY group_column
HAVING aggregate_function BETWEEN low AND high;

Retrieves characters from the inspectors table whose crime coefficient falls within a certain range.

SQL

-- Definition of the inspectors table (for sample purposes)
-- id, name, crime_coefficient, joined_date, division

-- Retrieves inspectors whose crime coefficient is between 50 and 120 inclusive (BETWEEN for numeric range).
SELECT name, crime_coefficient
FROM inspectors
WHERE crime_coefficient BETWEEN 50 AND 120;

-- Retrieves characters whose crime coefficient is outside the range (less than 50 or greater than 120).
SELECT name, crime_coefficient
FROM inspectors
WHERE crime_coefficient NOT BETWEEN 50 AND 120;

-- Retrieves inspectors whose assignment date falls within a specific period (BETWEEN for date range).
SELECT name, joined_date
FROM inspectors
WHERE joined_date BETWEEN '2095-01-01' AND '2112-12-31';

-- Specifies a range by character code order (BETWEEN for string range).
-- Conceptually retrieves characters whose names fall alphabetically between 'Kogami' and 'Shimotsuki'.
SELECT name
FROM inspectors
WHERE name BETWEEN 'Kogami' AND 'Shimotsuki';

-- Result of: SELECT name, crime_coefficient FROM inspectors
-- WHERE crime_coefficient BETWEEN 50 AND 120;
-- +--------------------+-------------------+
-- | name               | crime_coefficient |
-- +--------------------+-------------------+
-- | Akane Tsunemori    |                82 |
-- | Nobuchika Ginoza   |                57 |
-- | Mika Shimotsuki    |               108 |
-- +--------------------+-------------------+

-- Result of: SELECT name, crime_coefficient FROM inspectors
-- WHERE crime_coefficient NOT BETWEEN 50 AND 120;
-- +--------------------+-------------------+
-- | name               | crime_coefficient |
-- +--------------------+-------------------+
-- | Shinya Kogami      |               178 |
-- | Shogo Makishima    |               256 |
-- +--------------------+-------------------+

-- Result of: SELECT name, joined_date FROM inspectors
-- WHERE joined_date BETWEEN '2095-01-01' AND '2112-12-31';
-- +--------------------+-------------+
-- | name               | joined_date |
-- +--------------------+-------------+
-- | Akane Tsunemori    |  2112-04-01 |
-- | Nobuchika Ginoza   |  2109-04-01 |
-- | Mika Shimotsuki    |  2113-04-01 |
-- +--------------------+-------------+

Aggregates the average crime coefficient per division and filters to divisions within a specific range.

SQL

-- Calculates the average crime coefficient per division and retrieves divisions with an average between 70 and 150.
-- Uses BETWEEN in HAVING against the aggregated value.
SELECT division, AVG(crime_coefficient) AS avg_coefficient
FROM inspectors
GROUP BY division
HAVING AVG(crime_coefficient) BETWEEN 70 AND 150;

-- Result example
-- +--------------------+-----------------+
-- | division           | avg_coefficient |
-- +--------------------+-----------------+
-- | Public Safety Div1 |          105.25 |
-- +--------------------+-----------------+

The basic syntax of BETWEEN ... AND ... is commonly available across major databases.

SELECT name, crime_coefficient
FROM inspectors
WHERE crime_coefficient BETWEEN 50 AND 120;

Date type handling varies by database. In MySQL, date literals are written in the format 'YYYY-MM-DD'. PostgreSQL uses the same format, but an explicit type cast with ::DATE can also be used.

-- PostgreSQL (with explicit type cast)
SELECT name, joined_date
FROM inspectors
WHERE joined_date BETWEEN '2095-01-01'::DATE AND '2112-12-31'::DATE;

Care is needed when using BETWEEN with only a date value against a DATETIME or TIMESTAMP column. Because the upper bound has no time component, records after 00:00:00 on the upper boundary date are excluded.

-- Note when specifying only a date with BETWEEN against a DATETIME column.
-- '2112-12-31' is evaluated as '2112-12-31 00:00:00',
-- so records after 00:00:01 on that day are not retrieved.
-- To capture the intended range, specify the upper bound as less than the next day.
SELECT name, joined_at
FROM inspectors
WHERE joined_at >= '2095-01-01' AND joined_at < '2113-01-01';

BETWEEN low AND high is equivalent to low <= column AND column <= high. Always keep in mind that both endpoints (low and high) are included (inclusive). If you want to exclude the endpoints, switch to inequality expressions using > and <.

BETWEEN can be used with numbers, dates, and strings, but BETWEEN applied to strings performs a dictionary-order comparison based on character codes (collation). Because the result can vary depending on the database's collation setting, verify that applying BETWEEN to strings produces the intended behavior beforehand.

BETWEEN can be used in both WHERE and HAVING, but they serve different roles. Use WHERE to filter individual rows before grouping, and use HAVING to apply a range condition to values produced by GROUP BY aggregation (such as COUNT, AVG, or SUM). Conditions involving aggregate functions cannot be placed in WHERE.

To specify values outside a range, NOT BETWEEN can be used. NOT BETWEEN low AND high is equivalent to column < low OR column > high. If a value is NULL, both BETWEEN and NOT BETWEEN return UNKNOWN and the row is not retrieved. When working with a column that may contain NULL, combine the condition with IS NULL as needed.