SQL BETWEEN Operator

In addition to checking whether an expression is equal to another expression, you can build conditions that check whether an expression falls within a certain range. This type of condition is used when working with numerical or temporal data (data related to date and time), for example, to check for all products that cost between $10 and $50 or for all dates that fall in a certain date range.

In such situations where you need to check a range of values, the BETWEEN operator is used.

Syntax

The syntax of BETWEEN operator differs slightly from other WHERE clause operators because it requires two values – the beginning and the end of the range.

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Sample Table

To help you better understand the examples, and enable you to follow along with the tutorial, we are going to use the following sample table.

This table is part of an ‘Employee Management System’ that contains basic information about employees.

Using BETWEEN Operator

Suppose you wanted to find all employees hired from 2012 to 2014, you could write a query like this:

SELECT *
FROM Employees
WHERE HireDate >= '2012-01-01'
  AND HireDate < '2014-12-31';

In such situations, when you have both upper and lower limits for your range, you can choose to use a single condition that uses the BETWEEN operator instead of using two different conditions, as in:

SELECT *
FROM Employees
WHERE HireDate BETWEEN '2012-01-01' AND '2014-12-31';

Numeric ranges

You can also build conditions specifying a range of numbers. Numeric ranges are fairly easy to grasp, for example, to select all employees whose age is between 23 and 27, you can issue:

SELECT *
FROM Employees
WHERE Age BETWEEN 23 AND 27;

String ranges

Along with ranges of dates and numbers, you can also build conditions that search for ranges of strings. For example, you are searching for employees whose names are between the letters ‘A’ and ‘F’. Here’s what the query would look like:

SELECT *
FROM Employees
WHERE Name BETWEEN 'A' AND 'F';

Things to Note

When using the between operator, there are a couple of things to keep in mind.

Specify lower limit first

You should always specify the lower limit of the range first and the upper limit of the range second. Here’s what happens if you mistakenly specify the upper limit first:

SELECT *
FROM Employees
WHERE HireDate BETWEEN '2014-12-31' AND '2012-01-01';

As you can see, an empty result set is returned. This is because it is impossible to have a date that is both greater than 2015 and less than 2013.

BETWEEN is inclusive

The BETWEEN operator searches for all values in the range inclusively, meaning that the start and end values you provide are included in the query results.

For example, following query selects all employees whose salaries range from $30,000 to $60,000:

SELECT *
FROM Employees
WHERE Salary BETWEEN 30000 AND 60000;

NOT BETWEEN Operator

Like the BETWEEN operator, the NOT BETWEEN operator can be used to search for values that do not fall within the specified range.

Suppose you wanted to find all employees who were hired before 2012 and after 2014, you could write a query like this:

SELECT *
FROM Employees
WHERE HireDate NOT BETWEEN '2012-01-01' AND '2014-12-31';

Similarly let’s find all employees who are under 24 and over 26:

SELECT *
FROM Employees
WHERE Age NOT BETWEEN 24 AND 26;