SQL BETWEEN Operator: Filter Values in a Range (Examples)

SQL Tutorial · Chapter 12 of 32

The SQL BETWEEN operator filters rows whose value falls within a range, including both end points. Amount BETWEEN 40 AND 300 is the same as Amount >= 40 AND Amount <= 300, but shorter and easier to read. BETWEEN works with numbers, dates and text, and NOT BETWEEN returns everything outside the range.

SQL BETWEEN Syntax

SELECT columns FROM table_name
WHERE column BETWEEN low_value AND high_value;

SELECT columns FROM table_name
WHERE column NOT BETWEEN low_value AND high_value;

The lower value must come first. BETWEEN 300 AND 40 is valid syntax but matches nothing, because no value is both at least 300 and at most 40.

Sample Table

Example 1: Numeric Range

SELECT OrderID, Product, Amount
FROM Orders
WHERE Amount BETWEEN 40 AND 300;

The Monitor at exactly 300 is included because BETWEEN is inclusive at both ends.

Example 2: NOT BETWEEN

SELECT OrderID, Product, Amount
FROM Orders
WHERE Amount NOT BETWEEN 40 AND 300;

Example 3: Date Range

Orders placed in January 2026. Dates are written as quoted ISO strings.

SELECT OrderID, OrderDate
FROM Orders
WHERE OrderDate BETWEEN '2026-01-01' AND '2026-01-31';

Example 4: Text Range

Text ranges compare alphabetically. Products from H up to (but not including) anything after “M”.

SELECT DISTINCT Product
FROM Orders
WHERE Product BETWEEN 'H' AND 'M'
ORDER BY Product;

Monitor and Mouse are excluded because “Monitor” sorts after “M” (it is longer than the single letter M). To include all M words use BETWEEN 'H' AND 'Mz' or Product < 'N'.

Example 5: BETWEEN with Other Conditions

SELECT OrderID, CustomerID, Amount
FROM Orders
WHERE Amount BETWEEN 40 AND 1200
  AND CustomerID IN (1, 3);

The End-of-Day Date Trap

If OrderDate stores a time as well as a date (a DATETIME or TIMESTAMP column), BETWEEN '2026-01-01' AND '2026-01-31' stops at midnight at the start of 31 January and misses every order placed during that day. The safe pattern is a half-open range:

WHERE OrderDate >= '2026-01-01' AND OrderDate < '2026-02-01'

Works in MySQL, SQL Server, PostgreSQL, SQLite

• BETWEEN is inclusive in all four systems, and text comparison follows each database’s collation (case-insensitive in MySQL and SQL Server, case-sensitive in PostgreSQL and SQLite).
• PostgreSQL adds BETWEEN SYMMETRIC, which swaps the bounds automatically if you give them in the wrong order.
• SQLite stores dates as text, so ISO format ‘YYYY-MM-DD’ is essential for correct ordering.

How BETWEEN Is Evaluated

The database rewrites x BETWEEN a AND b to x >= a AND x <= b before planning the query, so the two forms perform identically and both can use an index on the column. What matters more is the data type. Comparing a text column against numbers, or a date column against a differently formatted string, forces a conversion on every row and turns an instant index range scan into a full table read. Keep dates in date columns and numbers in numeric columns, and write literals in the matching type.

BETWEEN also behaves predictably with NULL: if the column value is NULL the result is unknown and the row is excluded, exactly as with any other comparison.

Common Mistakes

• Reversing the bounds and getting zero rows.
• Assuming BETWEEN is exclusive; both ends are included.
• Using BETWEEN on DATETIME columns with a date-only upper bound and missing the final day.
• Expecting BETWEEN 'A' AND 'M' to include every word starting with M.

Practice Exercise

1. Return orders with an Amount from 25 to 80 inclusive.
2. Return orders placed in February or March 2026 using one BETWEEN.

-- 1
SELECT OrderID, Amount FROM Orders WHERE Amount BETWEEN 25 AND 80;
-- 102 (25), 104 (45), 105 (80)

-- 2
SELECT OrderID, OrderDate FROM Orders
WHERE OrderDate BETWEEN '2026-02-01' AND '2026-03-31';
-- 104, 105, 106

Related Chapters

• SQL WHERE – comparison operators
• SQL IN – matching a list rather than a range
• SQL AND, OR, NOT – the long form of BETWEEN
• SQL from Scratch: full course overview

FAQ

Is SQL BETWEEN inclusive?

Yes. Both the lower and upper bound values are included in the result in every major database.

Can I use BETWEEN with dates?

Yes, but if the column also stores a time, use a half-open range (>= start AND < next day) so the last day is not cut off at midnight.

What is the difference between BETWEEN and IN?

BETWEEN tests a continuous range with two bounds; IN tests membership in a specific list of values. Use BETWEEN for ranges and IN for discrete sets.

Chapter 12 of 32 · SQL from Scratch: all 32 chapters