SQL BETWEEN Operator

Summary: in this tutorial, you will learn how to use the 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 SELECT, UPDATE, 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;Code language: SQL (Structured Query Language) (sql)

The BETWEENoperator returns TRUEif the result of the expression or value of the column specified in the WHEREclause 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 NULLvalues to the BETWEENoperator e.g., expr,  lower_value or upper_value, the BETWEENoperator returns NULL.

SQL BETWEEN operator examples

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

SQL BETWEEN with number example

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

SELECT 
    productName, unitPrice
FROM
    products
WHERE
    unitPrice BETWEEN 18 AND 19;Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

The query returns 1 row instead 7 rows.

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: SQL (Structured Query Language) (sql)

In case the column that you want to compare is a DATETIMEcolumn, 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 BETWEENoperator with the NOT operator as follows:

SELECT 
    productName, unitPrice
FROM
    products
WHERE
    unitPrice NOT BETWEEN 18 AND 19;Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

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