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.

 ID Name Age Job Salary HireDate 1 Bob 28 Manager 60000 2011-03-07 2 Kim 26 Manager 55000 2014-04-25 3 Eve 22 Developer 32000 2013-03-11 4 Joe 24 Developer 30000 2016-10-05 5 Max 26 Janitor 9000 2015-01-15 6 Sam 30 Janitor 10000 2012-02-10

## 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';``````
 ID Name Age Job Salary HireDate 2 Kim 26 Manager 55000 2014-04-25 3 Eve 22 Developer 32000 2013-03-11 6 Sam 30 Janitor 10000 2012-02-10

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';``````
 ID Name Age Job Salary HireDate 2 Kim 26 Manager 55000 2014-04-25 3 Eve 22 Developer 32000 2013-03-11 6 Sam 30 Janitor 10000 2012-02-10

### 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;``````
 ID Name Age Job Salary HireDate 2 Kim 26 Manager 55000 2014-04-25 4 Joe 24 Developer 30000 2016-10-05 5 Max 26 Janitor 9000 2015-01-15

### 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';``````
 ID Name Age Job Salary HireDate 1 Bob 28 Manager 60000 2011-03-07 3 Eve 22 Developer 32000 2013-03-11

To work with string ranges, you need to know the order of the characters within your character set, also known as collation. Collation refers to a set of rules that determine how data is sorted and compared.

## 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';``````
 ID Name Age Job Salary HireDate

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;``````
 ID Name Age Job Salary HireDate 1 Bob 28 Manager 60000 2011-03-07 2 Kim 26 Manager 55000 2014-04-25 3 Eve 22 Developer 32000 2013-03-11 4 Joe 24 Developer 30000 2016-10-05

## 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';``````
 ID Name Age Job Salary HireDate 1 Bob 28 Manager 60000 2011-03-07 4 Joe 24 Developer 30000 2016-10-05 5 Max 26 Janitor 9000 2015-01-15

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

``````SELECT *
FROM Employees
WHERE Age NOT BETWEEN 24 AND 26;``````
 ID Name Age Job Salary HireDate 1 Bob 28 Manager 60000 2011-03-07 3 Eve 22 Developer 32000 2013-03-11 6 Sam 30 Janitor 10000 2012-02-10