SQL - BETWEEN Operator

The BETWEEN operator is used in the WHERE conditions to filter records within the specified range. The range of values can be strings, numbers, or dates. The range of values must be specified with the AND operator, as shown below.

Syntax:

SELECT column1, column2,.. FROM table WHERE column BETWEEN begin_value AND end_value

For the demo purpose, we will use the following Employee tables in all examples.

Consider the following query.

SELECT EmpId, FirstName, LastName, Salary FROM Employee WHERE Salary BETWEEN 10000 AND 20000;

Above, the Salary column is used with the BETWEEN operator to filter records. The Salary BETWEEN 10000 AND 20000; specifies that the values in the Salary column should be between 10000 and 20000 (inclusive of both values). The above query will display the following result.

Note that you must use the AND operator with the BETWEEN operator; otherwise, it will raise an error.

BETWEEN Date Range

The following query uses the BETWEEN operator to specify the date range.

SELECT EmpId, FirstName, LastName, HireDate FROM Employee WHERE HireDate BETWEEN '2018-07-01' and '2018-8-31';

BETWEEN String Range

The following query uses the string range with the BETWEEN operator.

SELECT * FROM Employee WHERE FirstName BETWEEN 'a%' AND 'j%';

Above, string range FirstName BETWEEN 'a%' AND 'j%' fetches records where the FirstName value should start from either 'a%' or any character before 'j%' (but not 'j'). It will display the following result.

NOT BETWEEN

Use the NOT operator with the BETWEEN operator to filter records that do not fall in the specified range.

SELECT EmpId, FirstName, LastName, Salary FROM Employee WHERE Salary NOT BETWEEN 10000 AND 20000;

Above, the Salary column is used with the BETWEEN operator to filter records. The Salary NOT BETWEEN 10000 AND 20000; specifies that the value in the Salary column should not be between 10000 and 20000. The above query will display the following result.