Selecting Data Within a Range of Values with SQL BETWEEN Operator

Summary: in this tutorial, you will learn how to use SQL BETWEEN operator to select data within a range of values.

The BETWEEN operator is used in the WHERE clause to select a value within a range of values. We often use the BETWEEN operator in the WHERE clause of the SELECTUPDATE and DELETE statements.

The following illustrates how to use the BETWEEN operator:

SELECT column_1, column_2 FROM table WHERE (expr | column) BETWEEN lower_value AND upper_value;

The BETWEEN operator returns TRUE if the result of the expression or value of the column specified in the WHERE clause is less than or equal to lower_value and greater than or equal to upper_value. Otherwise, it returns FALSE. The BETWEEN operator is inclusive.

To specify an exclusive range, you use the less than (<) and greater than (>) operators instead.

If you pass the NULL values to the BETWEEN operator e.g., exprlower_value or upper_value, the BETWEEN operator returns NULL.

SQL BETWEEN operator examples

Let’s take a look at some examples of using the BETWEEN operator.

SQL BETWEEN with number example

The following query selects product whose unit price is from $18 to $19:

SELECT productName, unitPrice FROM products WHERE unitPrice BETWEEN 18 AND 19;
SQL Between Example

You can rewrite the BETWEEN operator using less than or equal ( <= ) and greater than or equal ( >=) operators as follows:

SELECT productName, unitPrice FROM products WHERE unitPrice >= 18 AND unitPrice <= 19;

This query produces the same result set, however, the query that uses the BETWEEN operator is much more readable.

The following query uses less than (<) or greater than (>) operators to select data exclusively. In this case, you cannot use the BETWEEN operator.

SELECT productName, unitPrice FROM products WHERE unitPrice > 18 and unitPrice < 19;

The query returns 1 row instead 7 rows.

select data exclusively

SQL BETWEEN with date examples

You can use the BETWEEN operator to select employees who were born between  01-Jan-1948 and  01-Jan-1960 as follows:

SELECT lastname, firstname, birthdate FROM employees WHERE birthdate BETWEEN '1948-01-01' AND '1960-01-01';
Code language: JavaScript (javascript)
SQL BETWEEN DATES example

In case the column that you want to compare is a DATETIME column, the following expression:

dt BEETWEEN '1980-01-01' AND '1980-01-02';

is translated as:

dt BEETWEEN '1980-01-01 00:00:00.000000 AND '1980-01-02 00:00:00.000000';

Because the time part is not specified in the date literals, the database engine uses 12:00:00 AM as the default time. It means any row that contains a time part after 12:00 A.M. on 1980-01-01 is not returned because it is outside the range.

SQL BETWEEN with NOT operator

You can combine the BETWEEN operator with the NOT operator to find rows whose column values are not in a range of values. For example, to find products whose unit price is out of the range $18 and $19, you can use the BETWEEN operator with the NOT operator as follows:

SELECT productName, unitPrice FROM products WHERE unitPrice NOT BETWEEN 18 AND 19;
SQL BETWEEN with NOT operator

However, the following query that uses the less than ( <) and greater than ( >) operator and produces the same result set is considered much more readable:

SELECT 
    productName, unitPrice
FROM
    products
WHERE
    unitPrice < 18 OR unitPrice > 19;

In this tutorial, we have shown how to use the BETWEEN operator to select data within a range of values inclusively.