Is SQL BETWEEN inclusive or exclusive?

BETWEEN is inclusive — it includes both boundary values. For example, BETWEEN 200 AND 300 returns rows where the value is 200, 300, or anything in between. To exclude the boundaries, use greater-than and less-than operators instead.

Can SQL BETWEEN be used with dates?

Yes. BETWEEN works with date and datetime columns. The date format depends on your database. For example: WHERE Txn_Date BETWEEN '1999-01-06' AND '1999-01-10' will return all rows within that date range, inclusive.

What is NOT BETWEEN in SQL?

NOT BETWEEN is the negation of BETWEEN — it returns rows where the column value falls outside the specified range. For example, WHERE Sales NOT BETWEEN 280 AND 1000 returns rows with Sales less than 280 or greater than 1000.

Does the order of min and max values matter in BETWEEN?

Yes. The lower bound must be listed first (before AND) and the upper bound second. If you reverse the order — e.g., BETWEEN 1000 AND 200 — the statement is syntactically valid but will return no rows because no value can be simultaneously >= 1000 and <= 200.

SQL BETWEEN Operator | Filter a Range of Values

SQL > SQL Commands > Between

The BETWEEN operator is used when the filtering criteria is a continuous range with a maximum value and a minimum value. It is always used in the WHERE clause.

The SQL BETWEEN operator filters rows where a column's value falls within a continuous range — it is inclusive of both boundary values and works with numbers, dates, and text. Use NOT BETWEEN to exclude a range.

Syntax

The syntax for the BETWEEN operator is as follows:

SELECT "column_name"
FROM "table_name"
WHERE "column_name" BETWEEN 'min_value' AND 'max_value';

This will select all rows whose column has a value between 'min_value' and 'max_value.' Please note the order: 'min_value' needs to be listed before AND and 'max_value.' needs to be listed after. If the order is switched, the SQL statement is still valid, but will not give the desired results.

Examples

We use the following table for our examples.

Table Store_Information

Store_Name	Sales	Txn_Date
Los Angeles	1500	Jan-05-1999
San Diego	250	Jan-07-1999
San Francisco	300	Jan-08-1999
Boston	700	Jan-08-1999

Example 1

To select view all sales information between January 6, 1999, and January 10, 1999, we key in,

SELECT *
FROM Store_Information
WHERE Txn_Date BETWEEN 'Jan-06-1999' AND 'Jan-10-1999';

Note that date may be stored in different formats in different databases. This tutorial simply choose one of the formats.

Result:

Store_Name	Sales	Txn_Date
San Diego	250	Jan-07-1999
San Francisco	300	Jan-08-1999
Boston	700	Jan-08-1999

BETWEEN is an inclusive operator, meaning that 'value1' and 'value2' are included in the result. If we wish to exclude 'value1' and 'value2' but include everything in between, we need to change the query to the following:

SELECT "column_name"
FROM "table_name"
WHERE ("column_name" > 'value1')
AND ("column_name" < 'value2');

Example 2

We can also use the BETWEEN operator to exclude a range of values by adding NOT in front of BETWEEN. In the above example, if we want to show all rows where the Sales column is not between 280 and 1000, we will use the following SQL:

SELECT *
FROM Store_Information
WHERE Sales NOT BETWEEN 280 and 1000;

Result:

Store_Name	Sales	Txn_Date
Los Angeles	1500	Jan-05-1999
San Diego	250	Jan-07-1999

Frequently Asked Questions

How is BETWEEN different from using >= and <=?

They are functionally equivalent. WHERE Sales BETWEEN 200 AND 500 is the same as WHERE Sales >= 200 AND Sales <= 500. BETWEEN is more concise, while the comparison operators give you finer control (e.g., exclusive boundaries with > and <).

Does BETWEEN work with text (string) values?

Yes. BETWEEN can be applied to text columns, and comparisons use alphabetical (lexicographic) ordering. For example, WHERE Last_Name BETWEEN 'A' AND 'M' returns names starting with letters A through M. Case sensitivity depends on the database collation setting.

Can BETWEEN be used in an UPDATE or DELETE statement?

Yes. BETWEEN can appear in the WHERE clause of UPDATE or DELETE just like in a SELECT. For example: DELETE FROM Orders WHERE Order_Date BETWEEN '2024-01-01' AND '2024-03-31';

Is BETWEEN supported in all SQL databases?

Yes. BETWEEN is part of the SQL standard and is supported in all major databases including MySQL, PostgreSQL, SQL Server, Oracle, and SQLite.

Exercises

For these exercises, assume we have a table called User_Sales with the following data:

Table User_Sales

First_Name	Last_Name	Birth_Date	Gender	Join_Date	Total_Sales
Sophie	Lee	Jan-05-1960	F	Apr-05-2015	500
Richard	Brown	Jan-07-1975	M	Apr-05-2015	200
Jamal	Santo	Oct-08-1983	M	Apr-09-2015	350
Casey	Healy	Sep-20-1969	M	Apr-09-2015	80
Jill	Wilkes	Nov-20-1979	F	Apr-15-2015	210

1. Which of the following SQL statement is valid? (There can be more than one answer)
a) SELECT * FROM User_Sales WHERE Total_Sales BETWEEN 200 OR 300;
b) SELECT * FROM User_Sales WHERE Total_Sales IS BETWEEN 200 OR 300;
c) SELECT * FROM User_Sales WHERE Total_Sales IS BETWEEN 200 AND 300;
d) SELECT * FROM User_Sales WHERE Total_Sales BETWEEN 200 AND 300;

2. How many records will be returned by the following query?
SELECT * FROM User_Sales WHERE Join_Date BETWEEN 'Apr-05-2015' AND 'Apr-10-2015';

3. How many records will be returned by the following query?
SELECT * FROM User_Sales WHERE Gender = 'F' OR Total_Sales BETWEEN 50 AND 100;

Next: SQL Wildcard

This page was last updated on March 19, 2026.

SQL BETWEEN Operator