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

SQL Tutorial · Chapter 12 of 32

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

This chapter is for anyone learning SQL who already knows the basics of SELECT and WHERE. You will see the syntax, seven worked examples with their output, the date trap that catches most beginners, how BETWEEN behaves in MySQL, SQL Server, PostgreSQL and SQLite, and a short practice exercise.

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

All examples use this small Orders 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;

NOT BETWEEN is the same as Amount < 40 OR Amount > 300. Rows where Amount is NULL appear in neither result.

Example 3: Date Range

Orders placed in January 2026. Dates are written as quoted ISO strings ('YYYY-MM-DD'), which every major database understands.

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

Example 4: Text Range

Text ranges compare alphabetically, using the database’s sort order (collation).

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

Monitor and Mouse are excluded because “Monitor” sorts after the single letter “M”. To include every word starting with M, use Product < 'N' together with Product >= 'H'. For prefix matching, the LIKE operator is often clearer.

Example 5: BETWEEN with Other Conditions

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

The AND inside BETWEEN belongs to the operator; the second AND joins a new condition. Parentheses are optional here, but they help readability in longer filters. See SQL AND, OR, NOT and SQL IN for the other operators used.

Example 6: Grouping Values into Bands with CASE

BETWEEN is handy inside a CASE expression to label rows by range.

SELECT OrderID, Amount,
  CASE
    WHEN Amount BETWEEN 0 AND 49.99 THEN 'Small'
    WHEN Amount BETWEEN 50 AND 500 THEN 'Medium'
    ELSE 'Large'
  END AS OrderSize
FROM Orders;

Make sure the bands do not overlap or leave gaps. With decimal amounts, a half-open style (Amount < 50, then Amount < 500) avoids values such as 49.995 falling between two bands.

Example 7: BETWEEN in HAVING

BETWEEN also works on aggregated values. Customers whose total spend is between 50 and 1,200:

SELECT CustomerID, SUM(Amount) AS TotalSpent
FROM Orders
GROUP BY CustomerID
HAVING SUM(Amount) BETWEEN 50 AND 1200
ORDER BY CustomerID;

Customer 1 (1,500) is above the range, and customers 2 (25) and 3 (45) are below it. Learn more in GROUP BY and HAVING.

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 later that day. The safe pattern is a half-open range:

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

This version works for DATE and DATETIME columns alike, and it does not depend on how many days the month has.

Works in MySQL, SQL Server, PostgreSQL and SQLite

• BETWEEN is inclusive in all four systems, and text comparison follows each database’s collation (case-insensitive by default 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 the 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 the same 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 a fast 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. See SQL NULL values for how to include them deliberately.

Common Mistakes and Fixes

Tips

• Use BETWEEN for whole numbers and pure DATE columns; use half-open ranges for timestamps and decimals.
• Write date literals in ISO format so the query behaves the same in every database.
• When a range is really a short list of values, IN is usually clearer than BETWEEN.

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.

Answers:

-- 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

Test Yourself with the Free SQL Quiz

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 LIKE – pattern matching for text
• SQL from Scratch: full course overview

Date and number ranges are just as common in spreadsheet work, and the Excel AI Mastery: 100 Formulas course on NextGenTemplates Academy walks through range-based formulas with practice files.

Frequently Asked Questions

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. For DATE columns it works as expected. 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.

Is BETWEEN faster than using >= and <=?

No. The database treats them the same, and both can use an index. Choose whichever reads more clearly.

Does the order of the values matter?

Yes. The smaller value must come first. With the bounds reversed the query returns no rows, except in PostgreSQL when you use BETWEEN SYMMETRIC.

How does NOT BETWEEN treat NULL values?

A NULL is neither inside nor outside the range, so the row is excluded from both BETWEEN and NOT BETWEEN results.

Wrapping Up

BETWEEN is the simplest way to filter a range in SQL: inclusive at both ends, the same speed as two comparisons, and usable with numbers, dates, text and aggregates. Remember the two traps, reversed bounds and timestamps on the last day, and you will use it safely. Next, try the practice exercise above, then move on to SQL LIKE for pattern matching.

📅 Last updated: October 2026

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