SQL BETWEEN

Summary: in this tutorial, you’ll learn how to use the SQL BETWEEN operator to check if a value falls within a specific range.

Introduction to SQL BETWEEN operator

The BETWEEN operator is one of the logical operators in SQL. The BETWEEN operator checks if a value is within a range of values.

The syntax of the BETWEEN operator is as follows:

expression BETWEEN low AND high;Code language: SQL (Structured Query Language) (sql)

The BETWEEN operator returns true if the expression is greater than or equal to ( >=) the low value and less than or equal to ( <=) the high value.

Technically, the BETWEEN is the equivalent to the following expression that uses the greater than or equal to (>=) and less than or equal to (<=) operators:

expression >= low AND expression <= highCode language: SQL (Structured Query Language) (sql)

To compare a value with an exclusive range, you need to use the comparison operators less than (<) and greater than ( >).

NOT BETWEEN

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

expression NOT BETWEEN low AND highCode language: SQL (Structured Query Language) (sql)

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

Like the BETWEEN operator, you can rewrite the NOT BETWEEN operator using the less than (<) and greater than (>) operators with the OR operator as follows:

expression < low OR expression > highCode language: SQL (Structured Query Language) (sql)

In practice, you often use the BETWEEN and NOT BETWEEN operator in the WHERE clause of the SELECT to select rows whose value of a column is within a specific range.

SQL BETWEEN operator examples

We’ll use the employees table from the sample database to illustrate how the BETWEEN operator works.

1) Using the SQL BETWEEN opeator 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
ORDER BY 
	salary DESC;Code language: SQL (Structured Query Language) (sql)

Try It

+-------------+------------+-------------+---------+
| employee_id | first_name | last_name   | salary  |
+-------------+------------+-------------+---------+
|         116 | Shelli     | Baida       | 2900.00 |
|         117 | Sigal      | Tobias      | 2800.00 |
|         126 | Irene      | Mikkilineni | 2700.00 |
|         118 | Guy        | Himuro      | 2600.00 |
|         119 | Karen      | Colmenares  | 2500.00 |
+-------------+------------+-------------+---------+

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

The following query returns the same result set as the above query. However, it uses comparison operators greater than or equal to (>=) and less than or equal to (<=) instead:

SELECT 
    employee_id, 
    first_name, 
    last_name, 
    salary
FROM
    employees
WHERE
    salary >= 2500 AND salary <= 2900
ORDER BY 
	salary;Code language: SQL (Structured Query Language) (sql)

Try It

2) Using SQL NOT BETWEEN example

The following example uses the NOT BETWEEN operator to find all employees whose salaries are not in the range of 2,500 and 2,900:

SELECT 
    employee_id, 
    first_name, 
    last_name, 
    salary
FROM
    employees
WHERE
    salary NOT BETWEEN 2500 AND 2900
ORDER BY salary DESC;Code language: SQL (Structured Query Language) (sql)

Try It

+-------------+-------------+------------+----------+
| employee_id | first_name  | last_name  | salary   |
+-------------+-------------+------------+----------+
|         100 | Steven      | King       | 24000.00 |
|         101 | Neena       | Kochhar    | 17000.00 |
|         102 | Lex         | De Haan    | 17000.00 |
|         145 | John        | Russell    | 14000.00 |
|         146 | Karen       | Partners   | 13500.00 |
|         201 | Michael     | Hartstein  | 13000.00 |
|         205 | Shelley     | Higgins    | 12000.00 |
|         108 | Nancy       | Greenberg  | 12000.00 |
|         114 | Den         | Raphaely   | 11000.00 |
|         204 | Hermann     | Baer       | 10000.00 |
|         109 | Daniel      | Faviet     |  9000.00 |
|         103 | Alexander   | Hunold     |  9000.00 |
|         176 | Jonathon    | Taylor     |  8600.00 |
|         177 | Jack        | Livingston |  8400.00 |
|         206 | William     | Gietz      |  8300.00 |
|         121 | Adam        | Fripp      |  8200.00 |
|         110 | John        | Chen       |  8200.00 |
|         120 | Matthew     | Weiss      |  8000.00 |
|         122 | Payam       | Kaufling   |  7900.00 |
|         112 | Jose Manuel | Urman      |  7800.00 |
|         111 | Ismael      | Sciarra    |  7700.00 |
|         178 | Kimberely   | Grant      |  7000.00 |
|         113 | Luis        | Popp       |  6900.00 |
|         123 | Shanta      | Vollman    |  6500.00 |
|         203 | Susan       | Mavris     |  6500.00 |
|         179 | Charles     | Johnson    |  6200.00 |
|         202 | Pat         | Fay        |  6000.00 |
|         104 | Bruce       | Ernst      |  6000.00 |
|         106 | Valli       | Pataballa  |  4800.00 |
|         105 | David       | Austin     |  4800.00 |
|         200 | Jennifer    | Whalen     |  4400.00 |
|         107 | Diana       | Lorentz    |  4200.00 |
|         192 | Sarah       | Bell       |  4000.00 |
|         193 | Britney     | Everett    |  3900.00 |
|         115 | Alexander   | Khoo       |  3100.00 |
+-------------+-------------+------------+----------+

3) Using SQL BETWEEN operator with a date ranges

The following example uses the BETWEEN operator to find all employees who joined the company between January 1, 1999, and December 31, 2000:

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

Try It

+-------------+------------+------------+------------+
| employee_id | first_name | last_name  | hire_date  |
+-------------+------------+------------+------------+
|         107 | Diana      | Lorentz    | 1999-02-07 |
|         178 | Kimberely  | Grant      | 1999-05-24 |
|         119 | Karen      | Colmenares | 1999-08-10 |
|         113 | Luis       | Popp       | 1999-12-07 |
|         179 | Charles    | Johnson    | 2000-01-04 |
+-------------+------------+------------+------------+

The following example uses the NOT BETWEEN operator to find employees who have not joined the company from January 1, 1989 to December 31, 1999:

SELECT 
    employee_id, 
    first_name, 
    last_name, 
    hire_date
FROM
    employees
WHERE
    hire_date NOT BETWEEN '1989-01-01' AND '1992-12-31'
ORDER BY 
	hire_date;Code language: SQL (Structured Query Language) (sql)

Try It

+-------------+------------+-----------+------------+
| employee_id | first_name | last_name | hire_date  |
+-------------+------------+-----------+------------+
|         100 | Steven     | King      | 1987-06-17 |
|         200 | Jennifer   | Whalen    | 1987-09-17 |
|         179 | Charles    | Johnson   | 2000-01-04 |
+-------------+------------+-----------+------------+
3 rows in set (0.00 sec)Code language: JavaScript (javascript)

4) Using SQL BETWEEN operator with a function example

The following example uses the BETWEEN operator with the YEAR function to find employees who joined the company between 1990 and 1993:

SELECT 
    employee_id, 
    first_name, 
    last_name, 
    year(hire_date) joined_year
FROM
    employees
WHERE 
    year(hire_date) BETWEEN 1990 and 1993    
ORDER BY 
    hire_date;

Output:

+-------------+------------+-----------+-------------+
| employee_id | first_name | last_name | joined_year |
+-------------+------------+-----------+-------------+
|         103 | Alexander  | Hunold    |        1990 |
|         104 | Bruce      | Ernst     |        1991 |
|         102 | Lex        | De Haan   |        1993 |
+-------------+------------+-----------+-------------+

In this example:

• First, the YEAR() function returns the year from the hire date.
• Second, the BETWEEN operator uses the result of the YEAR() function and check if it is within the range 1990 and 1993.

If your database doesn’t support the YEAR() function, you need to use a similar function:

Summary

• The BETWEEN operator returns true if a value is within a specific range.
• Use the NOT operator to negate the BETWEEN opeator.