SQL BETWEEN Operator

The BETWEEN operator is a logical operator that is used to specify a range to test.

The BETWEEN operator checks if an expression or a column is within a range of values.

The BETWEEN operator is inclusive. The start and the end values are included.

The BETWEEN operator returns TRUE when the search value is present with the specified range; otherwise, it will returns FALSE. It will return NULL if any of the range values are NULL.

Syntax

The syntax of the BETWEEN operator is as the following:

SELECT column1_name, column2_name, ...columnN_name
FROM table_name
WHERE column_name | expression BETWEEN start_value AND end_value;

As explained above, the search value can be a column name or a calculation.

The start/end values create an inclusive range where the expression is compared to.

Demo table

Let us suppose that we have a table called "employee" with the following data:

Example Using the BETWEEN operator with Numeric values

In this example, we will see how to use the BETWEEN operator with numeric values.

The following SQL statement will return only employees whose salary falls within the range of 8000 and 9000.

SELECT * 
FROM employee
WHERE salary BETWEEN  8000 AND 9000;

After the execution, we will get the following result set:

+--------+-------------+-----------+------------+------------+-----------+------------------+
| emp_no | first_name  | last_name | birth_date | hire_date  | salary    | dept_name        |
+--------+-------------+-----------+------------+------------+-----------+------------------+
| 1002   | Maria       | Martinez  | 1993-06-13 | 2018-10-08 | 9000      | Development      |
| 1006   | James       | Johnson   | 2001-04-30 | 2014-02-18 | 8000      | Marketing        |
| 1007   | Stephanie   | Miller    | 1998-01-17 | 2009-12-12 | 8500      | Human Resources  |
+--------+-------------+-----------+------------+------------+-----------+------------------+

It is equivalent to the following SQL statement:

SELECT * 
FROM employee
WHERE salary >= 8000 
AND salary <= 9000;

Example Using BETWEEN operator with Date Values

The manipulation of Dates in SQL can be tricky. The use of Dates with the BETWEEN operator depends on which database you are using. We will give an example for each major database.

The following SQL statement will return all the employees who have been hired between the 1st January 2018 (2018-01-01) and the 31st December 2019 (2019-12-31):

Run one of the following SQL statements, depending on the database used.

For SQLite, PostgreSQL, and SQL Server:

SELECT * 
FROM employee
WHERE hire_date BETWEEN  2018-01-01 AND 2019-12-31;

For MariaDB and MySQL, use the CAST function:

SELECT * 
FROM employee
WHERE hire_date BETWEEN  CAST('2018-01-01' AS DATE) AND CAST('2019-12-31' AS DATE);

For Oracle, use the TO_DATE function:

SELECT *
FROM employee
WHERE hire_date BETWEEN TO_DATE('2018-01-01', 'yyyy-mm-dd') AND TO_DATE('2019-12-31', 'yyyy-mm-dd')

After running one of the above SQL statement, the result set will look like the following:

+--------+-------------+-----------+------------+------------+-----------+------------------+
| emp_no | first_name  | last_name | birth_date | hire_date  | salary    | dept_name        |
+--------+-------------+-----------+------------+------------+-----------+------------------+
| 1002   | Maria       | Martinez  | 1993-06-13 | 2018-10-08 | 9000      | Development      |
| 1003   | Andrew      | Rodriguez | 1994-10-25 | 2019-01-23 | 9500      | Finance          |
| 1004   | Robert      | Hernandez | 1995-09-07 | 2019-09-08 | 9600      | Development      |
+--------+-------------+-----------+------------+------------+-----------+------------------+

Example Using BETWEEN operator with Text Values

While using the BETWEEN operator with dates and numeric values are most common, you can also use it with text (string) values.

The following SQL statement will return all the employees whose 'first_name' begin with any of the letters between 'A' and 'D':

SELECT *
FROM employee
WHERE first_name BETWEEN 'A' and 'D';

The above SQL statement will output the following result set:

+--------+-------------+-----------+------------+------------+-----------+------------------+
| emp_no | first_name  | last_name | birth_date | hire_date  | salary    | dept_name        |
+--------+-------------+-----------+------------+------------+-----------+------------------+
| 1003   | Andrew      | Rodriguez | 1994-10-25 | 2019-01-23 | 9500      | Finance          |
| 1005   | Donna       | Williams  | 1996-03-15 | 2012-06-06 | 7400      | Finance          |
+--------+-------------+-----------+------------+------------+-----------+------------------+

Example using NOT operator with the BETWEEN operator

The BETWEEN operator can be used with the NOT operator.

The following SQL statement will select all employees whose salaries are not in the range of 7000 and 8500.

SELECT *
FROM employee
WHERE salary NOT BETWEEN 7000 AND 9000;

The above SQL statement will return the following result set:

+--------+-------------+-----------+------------+------------+-----------+------------------+
| emp_no | first_name  | last_name | birth_date | hire_date  | salary    | dept_name        |
+--------+-------------+-----------+------------+------------+-----------+------------------+
| 1001   | James       | Smith     | 1991-10-23 | 2010-05-13 | 6600      | Customer Service |
| 1003   | Andrew      | Rodriguez | 1994-10-25 | 2019-01-23 | 9500      | Finance          |
| 1004   | Robert      | Hernandez | 1995-09-07 | 2019-09-08 | 9600      | Development      |
+--------+-------------+-----------+------------+------------+-----------+------------------+

It is equivalent to the following SQL statement.

SELECT *
FROM employee
WHERE salary < 7000
OR salary > 8500;