The SQL BETWEEN operator checks whether a value falls inside a range. It works on numbers, dates and text, and it is inclusive: both the low and the high value count as inside the range.
SELECT columns
FROM table
WHERE column BETWEEN low AND high;
That is shorthand for column >= low AND column <= high. Put NOT in front to get everything outside the range. The low value must come first.
Jump to: sample data · numbers · is BETWEEN inclusive? · NOT BETWEEN · BETWEEN two dates · text · low value first · NULLs · with AND and OR · columns and expressions · in CASE, JOIN and HAVING · BETWEEN vs >= and <=, vs IN · database differences · performance · mistakes · FAQ
Sample data
Every query below was run on SQLite 3.45 and PostgreSQL 16, and the output is copied from the real result. The two agree everywhere except the date examples, where the difference is called out.
CREATE TABLE orders (
id INTEGER PRIMARY KEY, customer TEXT, amount INTEGER,
ordered_at TIMESTAMP, discount INTEGER
);
INSERT INTO orders VALUES
(1, 'Alice', 25, '2026-01-01 09:15:00', 5),
(2, 'Bob', 50, '2026-01-15 14:30:00', NULL),
(3, 'Carla', 75, '2026-01-31 00:00:00', 10),
(4, 'Dan', 100, '2026-01-31 16:45:00', NULL),
(5, 'Chen', 120, '2026-02-01 00:00:00', 20),
(6, 'Erin', 250, '2026-02-14 11:05:00', 15),
(7, 'adam', 40, '2026-03-02 08:00:00', 0);
A few details matter later: amounts of exactly 50 and 100, two orders on January 31 (one at midnight, one in the afternoon), two orders with no discount (NULL), and "adam" in lowercase.
BETWEEN with numbers
Find orders with an amount from 50 to 100:
SELECT id, customer, amount
FROM orders
WHERE amount BETWEEN 50 AND 100;
id | customer | amount
---+----------+-------
2 | Bob | 50
3 | Carla | 75
4 | Dan | 100
(3 rows)
Bob's 50 and Dan's 100 are both in the result. BETWEEN works the same way on decimals (price BETWEEN 9.99 AND 19.99) and negative numbers (temp BETWEEN -5 AND 5).
Is SQL BETWEEN inclusive?
Yes. In every major database (MySQL, PostgreSQL, SQL Server, Oracle, SQLite, Snowflake, BigQuery), BETWEEN includes both endpoints. The SQL standard defines x BETWEEN a AND b as exactly x >= a AND x <= b, and this query returns the same three rows as the one above:
SELECT id, customer, amount
FROM orders
WHERE amount >= 50 AND amount <= 100;
There is no "exclusive BETWEEN". If you want to leave the endpoints out, write the comparisons yourself:
SELECT id, customer, amount
FROM orders
WHERE amount > 50 AND amount < 100;
id | customer | amount
---+----------+-------
3 | Carla | 75
(1 row)
You can mix them too: amount >= 50 AND amount < 100 includes 50 but not 100. That "half-open" form turns out to be the right tool for dates.
NOT BETWEEN
NOT BETWEEN returns the rows outside the range. Because BETWEEN includes the endpoints, NOT BETWEEN excludes them:
SELECT id, customer, amount
FROM orders
WHERE amount NOT BETWEEN 50 AND 100;
id | customer | amount
---+----------+-------
1 | Alice | 25
5 | Chen | 120
6 | Erin | 250
7 | adam | 40
(4 rows)
It is the same as amount < 50 OR amount > 100. Three rows inside plus four outside makes seven, the whole table. That only holds when the column has no NULLs, as you'll see in the NULL section.
BETWEEN two dates
BETWEEN is most often used for date ranges, and this is where it most often goes wrong. Try to get every order placed in January 2026:
SELECT id, customer, ordered_at
FROM orders
WHERE ordered_at BETWEEN '2026-01-01' AND '2026-01-31';
PostgreSQL 16 returns:
id | customer | ordered_at
---+----------+--------------------
1 | Alice | 2026-01-01 09:15:00
2 | Bob | 2026-01-15 14:30:00
3 | Carla | 2026-01-31 00:00:00
(3 rows)
Dan's order from 4:45 pm on January 31 is missing. The column holds a date and a time, so the database turns '2026-01-31' into 2026-01-31 00:00:00, the first instant of that day. Carla's order at exactly midnight makes it in; everything later on the 31st is past the end of the range. MySQL, SQL Server and Oracle behave the same way.
The query doesn't fail. It just returns a January total that is one day short, which is why this bug survives in so many reports.
The fix: >= start AND < day after the end
Use a half-open range: on or after the first day, and strictly before the day after the last day.
SELECT id, customer, ordered_at
FROM orders
WHERE ordered_at >= '2026-01-01'
AND ordered_at < '2026-02-01';
id | customer | ordered_at
---+----------+--------------------
1 | Alice | 2026-01-01 09:15:00
2 | Bob | 2026-01-15 14:30:00
3 | Carla | 2026-01-31 00:00:00
4 | Dan | 2026-01-31 16:45:00
(4 rows)
All four January orders, and Chen's order at midnight on February 1 is correctly left out. This form works for any precision (seconds, milliseconds, microseconds), and consecutive ranges never overlap: January is >= Jan 1 AND < Feb 1, February is >= Feb 1 AND < Mar 1, and no order is counted twice or skipped.
Fixes that look right but aren't
BETWEEN '2026-01-01' AND '2026-02-01'returns five rows, becauseBETWEENis inclusive and Chen's order at exactly2026-02-01 00:00:00matches. That order would also be counted in February.BETWEEN '2026-01-01' AND '2026-01-31 23:59:59'returns the right four rows here, but misses anything stored at23:59:59.5. Adding.999doesn't save you either: SQL Server'sdatetimetype rounds23:59:59.999up to midnight of the next day.- Converting the column to a date,
WHERE CAST(ordered_at AS DATE) BETWEEN '2026-01-01' AND '2026-01-31'(orDATE(ordered_at)in MySQL and SQLite), does return the correct four rows. The cost is speed: in most databases a function on the column stops an index onordered_atfrom being used. See performance.
When BETWEEN is fine for dates
If the column is a true DATE with no time part (a birthday, a due date, a report day), BETWEEN '2026-01-01' AND '2026-01-31' is correct and reads nicely. The trap only applies to DATETIME and TIMESTAMP columns. If you aren't sure which one you have, use the half-open form; it is right for both.
Write date literals in ISO format, YYYY-MM-DD. A value like '01/02/2026' means January 2 or February 1 depending on the server's settings.
SQLite compares dates as text
SQLite has no date type; the timestamps above are stored as text and compared character by character. The first query returns only two rows there, because even '2026-01-31 00:00:00' is a longer string than '2026-01-31' and so sorts after it:
id | customer | ordered_at
---+----------+--------------------
1 | Alice | 2026-01-01 09:15:00
2 | Bob | 2026-01-15 14:30:00
(2 rows)
The half-open fix returns the correct four rows in SQLite too, as long as dates are stored in ISO format. More on that in SQLite date and time.
Rolling ranges: the last 7 days
The same pattern handles "recent" ranges. Only the date arithmetic changes by database:
| Database | Orders in the last 7 days |
|---|---|
| PostgreSQL | WHERE ordered_at >= CURRENT_DATE - INTERVAL '7 days' |
| MySQL | WHERE ordered_at >= CURDATE() - INTERVAL 7 DAY |
| SQL Server | WHERE ordered_at >= DATEADD(day, -7, CAST(GETDATE() AS date)) |
| SQLite | WHERE ordered_at >= DATE('now', '-7 days') |
Related guides: comparing dates in SQL, last 7 days in PostgreSQL, and the number of days between two dates in MySQL and SQL Server.
BETWEEN with text
BETWEEN on text uses alphabetical (collation) order. It has the same kind of trap as dates. Customers whose names start with A, B or C:
SELECT customer
FROM orders
WHERE customer BETWEEN 'A' AND 'C'
ORDER BY customer;
customer
--------
Alice
Bob
(2 rows)
Carla and Chen are missing. 'Carla' is longer than 'C' and sorts after it, the way "car" comes after "c" in a dictionary. The upper bound 'C' only matches the exact string "C". Use a half-open range that stops before the next letter:
SELECT customer
FROM orders
WHERE customer >= 'A' AND customer < 'D'
ORDER BY customer;
customer
--------
Alice
Bob
Carla
Chen
(4 rows)
"adam" is still missing, because SQLite and this PostgreSQL database compare text case-sensitively, and lowercase letters sort after uppercase ones. MySQL and SQL Server are case-insensitive by default and would include him. To get the same answer everywhere, compare the lowercase value: WHERE LOWER(customer) >= 'a' AND LOWER(customer) < 'd' returns all five names.
For "starts with" questions, LIKE 'C%' is usually clearer than a text range.
Numbers stored as text
If a numeric value lives in a text column, BETWEEN compares it as text. '50' BETWEEN '10' AND '100' is false, because the character "5" sorts after "1". Cast first, CAST(code AS INTEGER) BETWEEN 10 AND 100, or store numbers in a numeric column.
The low value must come first
Swap the bounds and the query returns nothing, with no error:
SELECT id, customer, amount
FROM orders
WHERE amount BETWEEN 100 AND 50;
id | customer | amount
---+----------+-------
(0 rows)
It expands to amount >= 100 AND amount <= 50, which no number can satisfy. This bites when the bounds come from user input or from two columns that might be in either order. PostgreSQL has BETWEEN SYMMETRIC, which sorts the bounds for you:
-- PostgreSQL
SELECT id, customer, amount
FROM orders
WHERE amount BETWEEN SYMMETRIC 100 AND 50;
id | customer | amount
---+----------+-------
2 | Bob | 50
3 | Carla | 75
4 | Dan | 100
(3 rows)
MySQL, SQL Server, Oracle and SQLite don't support SYMMETRIC (SQLite reports a syntax error). There, use BETWEEN LEAST(a, b) AND GREATEST(a, b) where those functions exist, or make sure the bounds are in order before the query runs.
BETWEEN and NULL
A NULL is never between anything. Orders with a discount from 5 to 15:
SELECT id, customer, discount
FROM orders
WHERE discount BETWEEN 5 AND 15;
id | customer | discount
---+----------+---------
1 | Alice | 5
3 | Carla | 10
6 | Erin | 15
(3 rows)
No surprise there. The surprise is that NOT BETWEEN doesn't return the NULL rows either:
SELECT id, customer, discount
FROM orders
WHERE discount NOT BETWEEN 5 AND 15;
id | customer | discount
---+----------+---------
5 | Chen | 20
7 | adam | 0
(2 rows)
Three rows inside the range, two outside, and Bob and Dan are in neither result. Comparing NULL to anything gives "unknown", and WHERE only keeps rows where the condition is true. If "no discount" should count as outside the range, say so:
SELECT id, customer, discount
FROM orders
WHERE discount NOT BETWEEN 5 AND 15
OR discount IS NULL;
id | customer | discount
---+----------+---------
2 | Bob | NULL
4 | Dan | NULL
5 | Chen | 20
7 | adam | 0
(4 rows)
Or treat a missing discount as zero with COALESCE(discount, 0) NOT BETWEEN 5 AND 15. A NULL bound behaves the same way: amount BETWEEN 50 AND NULL never matches a row, which matters when a bound comes from an optional parameter.
BETWEEN with AND and OR
The AND inside BETWEEN low AND high belongs to BETWEEN. You can add more conditions after it without parentheses:
SELECT id, customer, amount, discount
FROM orders
WHERE amount BETWEEN 50 AND 150
AND discount IS NOT NULL;
id | customer | amount | discount
---+----------+--------+---------
3 | Carla | 75 | 10
5 | Chen | 120 | 20
(2 rows)
To match several ranges, join them with OR:
SELECT id, customer, amount
FROM orders
WHERE amount BETWEEN 20 AND 50
OR amount BETWEEN 200 AND 300;
id | customer | amount
---+----------+-------
1 | Alice | 25
2 | Bob | 50
6 | Erin | 250
7 | adam | 40
(4 rows)
When you mix OR with other AND conditions, wrap the OR group in parentheses: WHERE (amount BETWEEN 20 AND 50 OR amount BETWEEN 200 AND 300) AND discount IS NOT NULL. Without them, AND is evaluated first and the filter means something else.
BETWEEN with columns and expressions
Any of the three parts can be a column or an expression, not just a constant. Here the tested value is a calculation, the price after discount:
SELECT id, customer, amount,
amount * (100 - COALESCE(discount, 0)) / 100.0 AS net
FROM orders
WHERE amount * (100 - COALESCE(discount, 0)) / 100.0 BETWEEN 40 AND 100;
id | customer | amount | net
---+----------+--------+------
2 | Bob | 50 | 50.0
3 | Carla | 75 | 67.5
4 | Dan | 100 | 100.0
5 | Chen | 120 | 96.0
7 | adam | 40 | 40.0
(5 rows)
The expression is repeated in WHERE because WHERE can't see the net alias. The bounds can be columns too, which is how you check a value against a range stored in the table: WHERE '2026-01-15' BETWEEN start_date AND end_date finds every booking, price or subscription that was active on January 15.
BETWEEN in CASE, JOIN and HAVING
BETWEEN is an ordinary true/false condition, so it works anywhere a condition does.
Bucket values with CASE
SELECT id, customer, amount,
CASE
WHEN amount BETWEEN 0 AND 49 THEN 'Small'
WHEN amount BETWEEN 50 AND 149 THEN 'Medium'
ELSE 'Large'
END AS size
FROM orders
ORDER BY id;
id | customer | amount | size
---+----------+--------+-------
1 | Alice | 25 | Small
2 | Bob | 50 | Medium
3 | Carla | 75 | Medium
4 | Dan | 100 | Medium
5 | Chen | 120 | Medium
6 | Erin | 250 | Large
7 | adam | 40 | Small
(7 rows)
Integer buckets like 0-49 and 50-149 are safe. With decimals, 49.50 would fall between the two ranges and land in ELSE, so use WHEN amount < 50 ... WHEN amount < 150 instead. More in the CASE statement guide.
Join to a range table
When the buckets live in a table, join on BETWEEN. This is called a range join:
CREATE TABLE tiers (tier TEXT, min_amount INTEGER, max_amount INTEGER);
INSERT INTO tiers VALUES
('Small', 0, 49), ('Medium', 50, 149), ('Large', 150, 999999);
SELECT o.id, o.customer, o.amount, t.tier
FROM orders o
JOIN tiers t ON o.amount BETWEEN t.min_amount AND t.max_amount
ORDER BY o.id;
The result is identical to the CASE version, and changing a boundary is now an UPDATE instead of a code change. Make sure the ranges don't overlap, or an order will match two tiers and appear twice.
Filter groups with HAVING
SELECT customer, SUM(amount) AS total
FROM orders
GROUP BY customer
HAVING SUM(amount) BETWEEN 50 AND 120
ORDER BY total;
customer | total
---------+------
Bob | 50
Carla | 75
Dan | 100
Chen | 120
(4 rows)
WHERE filters rows before grouping and HAVING filters the groups afterwards; see the GROUP BY guide.
A different BETWEEN: window frames
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW inside an OVER clause uses the same keyword, but it isn't a filter. It sets which neighbouring rows a window function looks at, for running totals and moving averages. That is covered in the window functions guide.
BETWEEN vs >= and <=, and BETWEEN vs IN
BETWEEN and >= ... AND <= ... mean exactly the same thing and run exactly the same way. Databases rewrite one into the other before planning the query, so there is no speed difference. The choice is about readability and safety:
| Use | When | Example |
|---|---|---|
BETWEEN | Whole numbers and date-only columns, where including both ends is what you mean | age BETWEEN 18 AND 65 |
>= and < | Timestamps, decimals and text, where the upper end should be excluded | ordered_at >= '2026-01-01' AND ordered_at < '2026-02-01' |
> and < | Both ends excluded | score > 0 AND score < 100 |
IN | A list of specific values, not a continuous range | status IN ('paid', 'shipped') |
BETWEEN and IN answer different questions. amount IN (50, 75, 100) matches only those three values, while amount BETWEEN 50 AND 100 also matches 51, 62.5 and everything else in the range. In our table they happen to return the same three rows, which is exactly how the two get confused. The free IN lesson covers lists.
BETWEEN in MySQL, PostgreSQL, SQL Server, Oracle and SQLite
The syntax is identical everywhere and always inclusive. The differences are in the data types being compared:
| Feature | MySQL | PostgreSQL | SQL Server | Oracle | SQLite |
|---|---|---|---|---|---|
| Inclusive on both ends | Yes | Yes | Yes | Yes | Yes |
NOT BETWEEN | Yes | Yes | Yes | Yes | Yes |
BETWEEN SYMMETRIC | No | Yes | No | No | No |
| Date-only string vs a timestamp column | Midnight | Midnight | Midnight | Midnight (DATE stores a time) | Text comparison |
| Text comparison is case-sensitive by default | No | Depends on collation | No | Yes | Yes |
| Get the date part of a timestamp | DATE(col) | col::date | CAST(col AS date) | TRUNC(col) | DATE(col) |
Oracle is worth a second look: its DATE type always carries a time, so the end-of-day trap applies to plain DATE columns there. For the full reference, see the MySQL comparison operators, PostgreSQL comparison predicates, SQL Server's BETWEEN and SQLite's expression docs. SQLite has its own walkthrough in SQLite BETWEEN.
BETWEEN performance and indexes
A range filter is one of the things an index is best at. Without one, SQLite reads the whole table:
EXPLAIN QUERY PLAN
SELECT id FROM orders
WHERE ordered_at BETWEEN '2026-01-01' AND '2026-01-31';
SCAN orders
Add an index on the column and the database jumps to the start of the range and reads until the end of it:
CREATE INDEX idx_orders_ordered_at ON orders (ordered_at);
EXPLAIN QUERY PLAN
SELECT id FROM orders
WHERE ordered_at BETWEEN '2026-01-01' AND '2026-01-31';
SEARCH orders USING COVERING INDEX idx_orders_ordered_at (ordered_at>? AND ordered_at<?)
The half-open version, ordered_at >= '2026-01-01' AND ordered_at < '2026-02-01', produced the same plan. Wrapping the column in a function did not:
EXPLAIN QUERY PLAN
SELECT id FROM orders
WHERE DATE(ordered_at) BETWEEN '2026-01-01' AND '2026-01-31';
SCAN orders USING COVERING INDEX idx_orders_ordered_at
SCAN means every row is read and DATE() is computed for each one. A few rules follow:
- Keep the column bare. Put the arithmetic on the constants (
ordered_at < '2026-02-01'), not on the column (DATE(ordered_at) <= '2026-01-31'). NOT BETWEENrarely uses an index well, because it usually matches most of the table. It was a full scan in this test too.- Range joins are expensive. A join on
BETWEENcan't use a hash join, so on large tables the database may compare every row with every range. Keep the range table small or indexed. - A wide range is still a lot of rows. An index helps when the range selects a small slice of the table; "all of last year" may be faster as a scan.
More tips in how to improve SQL query performance.
Common BETWEEN mistakes
- Using a date as the end of a timestamp range.
BETWEEN '2026-01-01' AND '2026-01-31'stops at midnight at the start of the 31st. Use>= '2026-01-01' AND < '2026-02-01'. (Example) - Counting boundary rows twice.
BETWEEN 0 AND 50andBETWEEN 50 AND 100both include 50. Adjacent ranges need one end excluded. - Putting the larger value first.
BETWEEN 100 AND 50returns no rows and no error. (Example) - Expecting text ranges to include the whole last letter.
BETWEEN 'A' AND 'C'leaves out "Carla". (Example) - Forgetting NULLs with
NOT BETWEEN. Rows where the value isNULLare in neither theBETWEENnor theNOT BETWEENresult. (Example) - Writing
BETWEEN 50, 100orBETWEEN (50, 100). The two bounds are separated byAND, never a comma. - Comparing numbers or dates stored as text.
'50' BETWEEN '10' AND '100'is false. Cast the column, or fix its type. - Using
BETWEENfor a list. For specific values such as 1, 5 and 9, useIN (1, 5, 9).
FAQ
What does BETWEEN do in SQL?
It tests whether a value is inside a range: WHERE price BETWEEN 10 AND 20 keeps rows where the price is at least 10 and at most 20. It works with numbers, dates and text.
Is BETWEEN inclusive or exclusive in SQL?
Inclusive. Both the lower and the upper value are part of the range, in every major database. For an exclusive range, use > and <.
How do I select rows between two dates?
For a date-only column, WHERE d BETWEEN '2026-01-01' AND '2026-01-31'. For a column with a time, use WHERE ts >= '2026-01-01' AND ts < '2026-02-01' so the whole last day is included.
How do I select values between two numbers?
WHERE amount BETWEEN 50 AND 100, with the smaller number first. That returns 50, 100 and everything in between.
What is the difference between BETWEEN and IN?
BETWEEN matches a continuous range between two values. IN matches a list of specific values. BETWEEN 1 AND 5 matches 2.5; IN (1, 5) doesn't.
Is BETWEEN faster than >= and <=?
No. They are the same condition to the database and produce the same query plan. Pick whichever is clearer and gives the right endpoints.
Does the order of values in BETWEEN matter?
Yes. The lower value must come first. BETWEEN 100 AND 50 matches nothing. PostgreSQL's BETWEEN SYMMETRIC accepts either order.
Does BETWEEN work with NULL?
A NULL value is never inside or outside a range, so it is excluded by both BETWEEN and NOT BETWEEN. Add OR column IS NULL if you need those rows.
Can I use BETWEEN with text?
Yes, it compares in alphabetical order. Remember that the upper bound is an exact string: BETWEEN 'A' AND 'C' stops at "C" and excludes "Carla".
How do I write NOT BETWEEN?
Put NOT before BETWEEN: WHERE amount NOT BETWEEN 50 AND 100. It returns values below 50 or above 100, and excludes 50 and 100 themselves.
Practice BETWEEN
Range filters are built from the comparison operators, so start with the free interactive greater than and greater than or equal lessons, then combine conditions in the AND lesson. They run in your browser with no signup. After that, try a range query in the SQL practice problems, see more filters in the SQL query examples, and keep the SQL cheat sheet open for the syntax. "Orders in a date range" is also a regular in SQL interviews.
When you're ready to filter data in your own database, Beekeeper Studio is a free, open source SQL editor for PostgreSQL, MySQL, SQLite and SQL Server.