SQL BETWEEN

Summary: This tutorial shows you how to use the SQL BETWEEN operator to select values within a range.

Introduction to SQL BETWEEN operator

The BETWEEN the operator is a logical operator. It returns a value of true, false, or unknown. The BETWEEN the operator is used in the WHERE clause of the SELECT,  DELETE, or UPDATE statement to find values within a range.

The following illustrates the syntax of the BETWEEN operator:

expression BETWEEN low AND high;

In this syntax:

•  expression is the expression to test for in the range defined by low and high.
• low and high can be either expressions or literal values with a requirement that the value of low is less than the value of high.

The BETWEEN the operator returns true if the expression is greater than or equal to ( >=) the value of low and less than or equal to ( <=) the value of high, which is equivalent to the following condition:

expression >= low and expression <= high

In case you want to specify an exclusive range, you must use the comparison operators less than ( <) and greater than ( >).

To negate the result of the BETWEEN operator, you add the NOT operator:

expression NOT BETWEEN low AND high

The NOT BETWEEN returns true if the expression is less than the value of low or greater than (>) the value of high; otherwise, it returns false.

The NOT BETWEEN can be rewritten using the following condition:

expression < low OR expression > high

SQL BETWEEN operator examples

We will use the employees the table in the sample database to demonstrate how the BETWEEN operator works.

SQL BETWEEN with numbers example

The following statement uses the BETWEEN operator to find all employees whose salaries are between 2,500 and 2,900:

SELECT 
    employee_id, first_name, last_name, salary
FROM
    employees
WHERE
    salary BETWEEN 2500 AND 2900;

Notice that employees whose salaries are 2,500 and 2,900 are included in the result set.

The following query, which uses comparison operators greater than or equal to (>=) and less than or equal to (<=) and the logical operator AND, returns the same result set:

SELECT
	employee_id,
	first_name,
	last_name,
	salary
FROM
	employees
WHERE
	salary >= 2500
AND salary <= 2900;

To find all employees whose salaries are not in the range of 2,500 and 2,900, you use the NOT BETWEEN operator in the WHERE clause as shown below:

SELECT 
    employee_id, first_name, last_name, salary
FROM
    employees
WHERE
    salary NOT BETWEEN 2500 AND 2900
ORDER BY salary;

SQL BETWEEN dates example

To find all employees who joined the company between January 1, 1999, and December 31, 2000, you check whether the hire date is within the range:

SELECT 
    employee_id, first_name, last_name, hire_date
FROM
    employees
WHERE
    hire_date BETWEEN '1999-01-01' AND '2000-12-31'
ORDER BY hire_date;

To retrieve all employees who have not joined the company from January 1, 1989 to December 31, 1999, you use the NOT BETWEEN operator as follows:

SELECT 
    employee_id, first_name, last_name, hire_date
FROM
    employees
WHERE
    hire_date NOT BETWEEN '1989-01-01' AND '1999-12-31'
ORDER BY hire_date;

SQL BETWEEN operator usage notes

1) values of low and high

The BETWEEN operator requires the low and the high values. When you get input from users, you should always check if the low value is less than the high value before passing it to the query. If the low value is greater than the high value, you will get an empty result set.

The following query does not return any rows because the low value is greater than the high value:

SELECT 
    first_name, last_name, salary
FROM
    employees
WHERE
    salary BETWEEN 5000 AND 2000;

2) Using the BETWEEN operator with the DATETIME data

Consider the following t1 table:

There are four rows created between June 29, 2016 and June 30, 2016. If you use the BETWEEN operator to query the rows whose created_at values are between June 29, 2016, and June 30, 2016, you will get what you may expect.

SELECT 
    id, created_at
FROM
    t1
WHERE
    created_at BETWEEN '20160629' AND '20160630';

The result shows that only three rows returned. This is because when you used the following condition:

created_at BETWEEN '20160629' AND '20160630'

The database system translates it into something like:

created_at BETWEEN '20160629 00:00:00.000000' AND '20160630 00:00:00.000000'

Therefore the row with the value 2016-06-30 23:59:59 was not included in the result set.

In this tutorial, we have shown you how to use the SQL BETWEEN operator to select data based on a range.