SQL BETWEEN
Back to: SQL Tutorial
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 <= high
Code 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 high
Code 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 > high
Code 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.
![employees_table](https://www.sqltutorial.org/wp-content/uploads/2016/03/employees_table.png)
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;
+-------------+------------+-------------+---------+ | 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;
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;
+-------------+-------------+------------+----------+ | 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;
+-------------+------------+------------+------------+ | 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;
+-------------+------------+-----------+------------+ | 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)
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;
+-------------+------------+-----------+-------------+ | 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 theYEAR()
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:
Database | The function to extract the year from a date |
---|---|
PostgreSQL | DATE_PART('year', hire_date) |
Oracle | EXTRACT(year from hire_date) |
SQL Server | YEAR(hire_date) |
Summary
- The BETWEEN operator returns true if a value is within a specific range.
- Use the NOT operator to negate the BETWEEN opeator.