SQL with Examples : SQL BETWEEN Operator

Hits: 4

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.