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.
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 |
Python Example for Beginners
Two Machine Learning Fields
There are two sides to machine learning:
- Practical Machine Learning:This is about querying databases, cleaning data, writing scripts to transform data and gluing algorithm and libraries together and writing custom code to squeeze reliable answers from data to satisfy difficult and ill defined questions. It’s the mess of reality.
- Theoretical Machine Learning: This is about math and abstraction and idealized scenarios and limits and beauty and informing what is possible. It is a whole lot neater and cleaner and removed from the mess of reality.
Data Science Resources: Data Science Recipes and Applied Machine Learning Recipes
Introduction to Applied Machine Learning & Data Science for Beginners, Business Analysts, Students, Researchers and Freelancers with Python & R Codes @ Western Australian Center for Applied Machine Learning & Data Science (WACAMLDS) !!!
Latest end-to-end Learn by Coding Recipes in Project-Based Learning:
Applied Statistics with R for Beginners and Business Professionals
Data Science and Machine Learning Projects in Python: Tabular Data Analytics
Data Science and Machine Learning Projects in R: Tabular Data Analytics
Python Machine Learning & Data Science Recipes: Learn by Coding
R Machine Learning & Data Science Recipes: Learn by Coding
Comparing Different Machine Learning Algorithms in Python for Classification (FREE)
Disclaimer: The information and code presented within this recipe/tutorial is only for educational and coaching purposes for beginners and developers. Anyone can practice and apply the recipe/tutorial presented here, but the reader is taking full responsibility for his/her actions. The author (content curator) of this recipe (code / program) has made every effort to ensure the accuracy of the information was correct at time of publication. The author (content curator) does not assume and hereby disclaims any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from accident, negligence, or any other cause. The information presented here could also be found in public knowledge domains.