Home » SQL Tutorial » SQL BETWEEN Operator

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;

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;
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';
SQL BETWEEN DATES example

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

Databases  #

Was this helpful?