Learn how to select the data interval using the SQL BETWEEN. We will show you its syntax and different scenarios of using it in filtering and labeling data.

As an SQL user, you’re already using it for managing and manipulating relational databases big time. It enables you to create, retrieve, update, and delete database records.

SQL is equipped with various operators, and you’re familiar with some of them. But what about the SQL BETWEEN operator? How often do you use it?

It’s a powerful comparison operator used to select values within a given range, inclusive of the range endpoints. If you don’t use it often, we think you should, as it can aid you in simplifying complex queries.

This article will dissect the SQL BETWEEN operator, diving into its syntax, applications, and nuanced interactions with other SQL elements. By the end of this read, you will have expert insights into this operator and know how to employ it effectively in a variety of scenarios.

What is BETWEEN Operator in SQL?

The BETWEEN operator in SQL is a logical operator used for selecting values within a particular range. This operator is typically used with the WHERE clause to filter out records based on specific criteria. The range specified by the BETWEEN operator includes the endpoints. In other words, if you say 'BETWEEN 3 AND 5', it will include 3 and 5.

The BETWEEN operator not only works with numeric values but also handles dates, strings, and other data types efficiently, making it a versatile tool for data filtering and manipulation.

Syntax of the SQL BETWEEN Operator

The general syntax of the SQL BETWEEN operator is:

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

In this syntax:

• column_name(s) represents the column or columns from which you want to select data.
• table_name refers to the name of the table where the data resides.
• column_name in the WHERE clause is the field against which the BETWEEN operator will filter the values.
• value1 and value2 represent the range within which the column_name's values should fall. It is critical to note that value1 and value2 are inclusive in the range.

Examples and Scenarios: Putting the SQL BETWEEN Operator Into Action

Understanding the SQL BETWEEN operator is greatly facilitated by seeing it in action.

Let's use the examples from our platform to show you different scenarios of using the SQL BETWEEN operator.

Using the SQL BETWEEN Operator for Numeric Values

The BETWEEN operator is straightforward to use with numeric values. You define a lower limit and an upper limit, and the operator will select all values that lie within this inclusive range.

In the context of numeric values, the BETWEEN operator is particularly useful in data analysis, providing the ability to slice and dice data based on various numerical ranges.

Take a look at this example from Wine Magazine.

Link to the question: https://platform.stratascratch.com/coding/10022-find-all-wine-varieties-which-can-be-considered-cheap-based-on-the-price

It asks you to find all wine varieties priced between $5 and $20.

Dataset

The question gives you one table to work with: winemag_p1.

It’s a list of wines with plenty of information about them. We can ignore most of it, as the only columns we’ll use are variety and price.

Code

Let’s dissect the below code to see how it works.

We first select the distinct wine variety. Then we use the BETWEEN in the WHERE clause to set the price condition for filtering the output.

First, we reference the column price in WHERE. Then comes BETWEEN, where we simply write the upper and lower boundaries of the range separated by AND.

SELECT DISTINCT variety
FROM winemag_p1
WHERE price BETWEEN 5 AND 20;

Output

The output shows the list of wines that satisfy the condition stated in BETWEEN.

Applying the SQL BETWEEN Operator for Date and Time Values

The BETWEEN operator also shines when dealing with date and time data types. For instance, you can fetch all records from a specific period using the BETWEEN operator.

However, it's essential to note that the date format should be compatible with the one stored in your database. If the database stores dates in the YYYY-MM-DD format, then your query should follow the same format.

Let’s show you how this works on the question asked by Google and Apple.

Link to the question: https://platform.stratascratch.com/coding/9649-count-the-number-of-accounts-used-for-logins-in-2016

The question wants you to find the accounts that logged in during 2016.

Dataset

We’ll work with the table product_logins.

The columns account_id and login_date are of interest to us.

Code

Here’s how to use BETWEEN to filter dates.

We use DISTINCT with the COUNT() aggregate function to find the number of logins.

The question wants us to show only logins in 2016. We filter by login date and then set the condition using WHERE.

We set the first day of 2016 as the lower boundary and the year’s last day as upper boundary. Remember, BETWEEN is inclusive, so both these dates will be included, which reflects the reality – January 1 and December 31 really are the first and the last days of any year.

The dates reflect the format in which they are stored in the database: YYYY-MM-DD. Also, they have to be written in single quotes ('') for the query to work.

SELECT COUNT(DISTINCT account_id) AS n_logins
FROM product_logins
WHERE login_date BETWEEN '2016-01-01' AND '2016-12-31';

This solution, and some others, use the aggregate functions. This is a very important SQL concept, so we advise you to read more about the SQL aggregate functions if you’re not confident using them.

Output

The output shows there are 73 accounts that performed login in 2016.

Dealing With Strings and Character Data Using the SQL BETWEEN Operator

The BETWEEN operator works with string data types, too. When used with strings, BETWEEN operator selects values within the specified range in alphabetical order. 'A' is considered the lowest value and 'Z' the highest.

Let’s repurpose the previous question. Instead of logins in 2016, let’s show the accounts with the employer key starting with the letters between 'B' and 'G'.

Dataset

The dataset is the same as earlier. This time, we’ll use the columns account_id and employer_key in our solution.

Code

Here’s how to solve this modified question.

The principle is the same as earlier. Except now we use the column employer_key in WHERE. Also, the range in BETWEEN is from 'B' to 'G'. These boundaries are written in single quotes, the same as dates.

SELECT COUNT(DISTINCT account_id) AS n_logins
FROM product_logins
WHERE employer_key BETWEEN 'B' AND 'G';

Output

The output shows there are 17 accounts from employers starting with letters from 'B' to 'G'.

Using SQL NOT BETWEEN

There is BETWEEN in SQL, but there’s also its negation: NOT BETWEEN.

The NOT BETWEEN operator in SQL is used to fetch records that do not fall within a certain range. It is the exact inverse of the BETWEEN operator.

We’ll again repurpose the previous question to show you how NOT BETWEEN works.

Imagine that the question wants you to show the unique accounts and their employer keys, but only for those not between 'B' and 'G'.

Dataset

The data is the same as earlier.

Code

Here’s how to use NOT BETWEEN.

We select distinct account IDs and also list the employer key in SELECT. The employer key is again in WHERE. Using NOT BETWEEN is easy – just put NOT in front of BETWEEN, and that’s it!

SELECT DISTINCT account_id AS n_logins,
       employer_key
FROM product_logins
WHERE employer_key NOT BETWEEN 'B' AND 'G';

Output

The query outputs the list of account IDs with the employer keys starting with letters 'B' to 'G'.

But wait! Why is there Google if we excluded the letter G? Well, the thing is that (NOT) BETWEEN looks at the whole string. In this case, 'G' would exclude a company if it were named only G. However, Google is outside the upper range boundary as there are more letters after the letter G. In a way, Google is 'above' the upper boundary of 'G'.

Combining the SQL BETWEEN Operator With Other Logical Operators

The power of SQL lies in the ability to combine various operators and functions to write complex queries. The SQL BETWEEN operator is no exception. It can be used alongside operators such as AND, OR, IN, and NOT to create more complex queries.

Here’s an overview of all the logical operators in SQL and what they do.

Now, let us give you examples of how BETWEEN works with other logical operators.

Using SQL BETWEEN With AND

The AND logical operator is used for stating multiple conditions. It returns TRUE if all the conditions separated by AND are TRUE.

Let’s see how we can use it with BETWEEN. Here’s an example from the City of San Francisco.

Link to the question: https://platform.stratascratch.com/coding/9931-patrons-who-renewed-books

The question wants us to find the number of patrons who renewed books in April 2015 but did that less than ten times.

Dataset

We’ll work with the table library_usage.

It’s a list of library patrons with details about them and their library usage. We won’t need all this information. To solve this problem, we need the following columns: total_renewals, circulation_active_month, and circulation_active_year.

Code

We start solving this problem by using a simple COUNT(*) function to count the number of patrons.

Then we set three filtering conditions in WHERE—the first to output patrons with at least one but no higher than nine renewals.

Then we use the AND operator to add the second condition, which looks for renewals in April. It’s followed by another AND and the third condition looking for renewals in 2015.

Together, all these three conditions will output the patrons that had up to nine book renewals in April 2015, which is exactly what the question is asking.

SELECT COUNT(*) AS n_patrons
FROM library_usage
WHERE total_renewals BETWEEN 1 AND 9
  AND circulation_active_month = 'April'
  AND circulation_active_year = '2015';

Output

Running the code will show you that three patrons satisfy all the conditions.

Using SQL BETWEEN With IN

The SQL IN operator is used to specify multiple values in the WHERE clause. You can do that by providing a list of values in parentheses or using the subquery to return them.

The IN operator can, of course, be combined with the BETWEEN operator to handle more complex scenarios.

Here’s a question from Ring Central. It asks you to find the number of users that had any calls in April 2020.

Link to the question:https://platform.stratascratch.com/coding/2017-paid-users-in-april-2020

Data

The question provides us with two tables. The first one is rc_calls.

It’s a list of calls made by users.

The second table is rc_users. As you would imagine, it’s a list of the users.

Code

The solution combines the IN and BETWEEN operators, but also AND, which we learned to use in the previous example. Let’s see how this works!

We use COUNT() and DISTINCT() to find the number of users.

Then we use WHERE and BETWEEN to find April 2020 dates. In other words, we’re looking for the calls that were made in April 2020.

The other request by the question is to show calls only by the paid users. So we need to set another condition in WHERE, which is really simple – first, follow the first condition with the operator AND and the column name. In this case, the second condition uses the column user_id. Now we use the operator IN with the subquery. The subquery looks for users whose status is 'paid'.

In other words, the second condition in WHERE takes user IDs and returns those that are listed as paid users by a subquery.

SELECT COUNT (DISTINCT user_id)
FROM rc_calls
WHERE date BETWEEN '2020-04-01' AND '2020-04-30'
  AND user_id IN
    (SELECT user_id
     FROM rc_users
     WHERE status = 'paid');

Output

The output shows there are only five paid users that placed calls in April 2020.

Alternatives to the SQL BETWEEN Operator: Other Comparison Operators

We have been silent about this so far, but it’s time to reveal the big secret! The BETWEEN operator is exactly the same as using the '>=' and '<=' operators!

The NOT BETWEEN is, then, the same as using '<' and '>' operators.

You realized that by now, didn’t you?

All these operators that are alternatives to (NOT) BETWEEN are called comparison operators. The full list and the description are given below.

The general syntax for replacing BETWEEN with the comparison operators is:

SELECT column_name(s)
FROM table_name
WHERE column_name >= value1 AND column_name <= value2;

In this syntax:

• column_name(s) represents the column or columns from which you want to select data.
• table_name refers to the name of the table where the data resides.
• column_name in the WHERE clause is the field against which the comparison operators will filter the values.
• value1 represents the value above which or equal to the column_name's values should be.
• value2 represents the value below which or equal to the column_name's values should be.

Now, the syntax for replacing NOT BETWEEN with the comparison operators is:

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

In this syntax:

• column_name(s) represents the column or columns from which you want to select data.
• table_name refers to the name of the table where the data resides.
• column_name in the WHERE clause is the field against which the comparison operators will filter the values.
• value1 represents the value below which the column_name's values should be.
• value2 represents the value above which or equal to the column_name's values should be.

As you can see, using the comparison operators instead of BETWEEN or NOT BETWEEN results in longer code. This is one of the benefits of BETWEEN being made obvious!

While the BETWEEN operator is handy for working with ranges, sometimes you may need to use other operators depending on the situation. The choice between the BETWEEN operator and the combination of other comparison operators depends on the specific requirements and personal preferences of the SQL user.

However, some of the logical operators give you the possibilities that BETWEEN doesn’t! This is why we advise you to learn using all the logical operators, too. Don’t stick only to BETWEEN and NOT BETWEEN!

Let’s get to the interview example to show you how to use the comparison operators instead of BETWEEN.

We’ll use the interview question by Tata Consultancy.

Link to the question: https://platform.stratascratch.com/coding/2144-flight-satisfaction-2022

The question wants you to calculate the customer satisfaction average for this age group across all three flight classes for 2022.

Data

The question gives you two tables. The first one is survey_results. It’s a list of customer surveys and their details.

The second table is loyalty_customers, which is a list of loyalty customers.

Code

The official solution uses BETWEEN, but we’ll solve the question using the comparison operators.

We select the class and use the ROUND() and AVG() functions to calculate the customer satisfaction average.

Then we use WHERE to filter the output. In it, we’re comparing the customer ID with the list of loyalty customers between the ages of 30 and 40. We use the subquery to get this information.

The subquery has WHERE of its own. In it, we use the comparison operators and the operator AND to set the two conditions: that the customer's age is equal to or above 30 and that the customer’s age is equal to or below 40.

The output is grouped by class.

SELECT class,
       ROUND(AVG(satisfaction), 0) AS pc_score
FROM survey_results
WHERE cust_id IN
    (SELECT cust_id
     FROM loyalty_customers
     WHERE age >= 30 AND age <=40)
GROUP BY 1;

We used grouping here, so you might want to read more about GROUP BY and how to use it.

Output

The output shows three flight classes and the average customer satisfaction with it, rounded to the nearest whole number.

Conclusion: Leveraging the Power of the SQL BETWEEN Operator

In conclusion, the SQL BETWEEN operator is a powerful tool in the hands of a SQL user. It offers a convenient way to filter records based on a range of values. Its compatibility with different data types—numeric, date/time, and string—makes it a versatile operator.

Furthermore, its ability to be combined with other SQL operators allows you to create complex queries.

As with any SQL operator, using the BETWEEN operator efficiently requires understanding its syntax, its behavior with different data types, and its interaction with other SQL elements. This understanding, combined with regular practice, will enable SQL developers to leverage the full potential of the BETWEEN operator. While alternatives exist, the BETWEEN operator remains an effective and expressive way to specify a range of values in SQL. So, make it an integral part of your SQL toolkit!

We can help you with that! Go to coding questions on StrataScratch, and you will find plenty of questions requiring you to use the BETWEEN operator. Of course, there are many other SQL questions where you can practice other logical and comparison operators, as well as any other SQL concept.