A Detailed Guide to SQL's BETWEEN Operator

By Community / Developer
May 22, 2023

This post was written by Pius Aboyi. Scroll down for the author’s bio.

SQL provides many operators that you can use to filter data in your queries based on specific conditions. For instance, when you need to get data with a value within some range, you can use the BETWEEN operator.

A practical use case for the BETWEEN operator can be when you need to fetch all records of children within a range like ages of five to ten. In such a case, your query will fetch all rows that have the age value from 5 all the way to 10.

In this post, you’ll learn how to use the BETWEEN operator in a step-by-step guide with examples.

What is the SQL BETWEEN operator?

The BETWEEN operator selects rows that are within a specific range. And since it’s inclusive, it will include the rows with values that are equal to the start and end of the range. You can use the BETWEEN operator to filter text, numbers, and date values.

The syntax for the BETWEEN operator looks like this:

table BETWEEN minimum_value AND maximum_value

Usually, the BETWEEN operator is placed after the WHERE clause, similar to other SQL logical operators.

When to use the BETWEEN operator

There are many situations and problems that the BETWEEN operator can solve easily.

1. Fetching data within a time range: The BETWEEN operator is great when you need to select data using a start date and an end date such as if you need, for example, to fetch all transactions from February 1 to March 15 in a financial application.

2. Get data within a numeric range: Here you can use the BETWEEN operator when you need to get rows that have the value for a specific field within a range. This could be fetching items from an inventory that have their available quantity between a range of 10 to 20.

3. Searching text within a range: You can use the BETWEEN operator to get rows using a range that contains two strings. In a table that contains records of students and their grades, for example, you can select all records for students with grades A to C.

When to not use the BETWEEN operator

A good case of when you should not use the BETWEEN operator is when one or both ends of the ranges should not be included in the result. That’s to say, you want to get rows between A to C but exclude A and C. The result in this case will be only rows with B. If you want to exclude the lower value in the range, fetching rows between 4 to 8 should return only rows with 5, 6, 7, and 8 while omitting the 4 since it’s the lower value.

How to use the SQL BETWEEN operator

In this section, we’ll walk you through how to use the BETWEEN operator in MySQL and MS SQL Server.

Step 1: Create an SQL table

In order to follow along with the examples we’re about to show you, start by creating a new table in your database.

Run the following query to create a table in MySQL or SQL Server: MySQL:

CREATE TABLE students_record(id int AUTO_INCREMENT PRIMARY KEY, full_name varchar(100) NOT NULL, grade varchar(20) NOT NULL, subject varchar(150) NOT NULL, date_of_birth date NOT NULL);

SQL Server:

CREATE TABLE students_record(id int PRIMARY KEY IDENTITY(1,1), full_name varchar(100) NOT NULL, grade varchar(20) NOT NULL, subject varchar(150) NOT NULL, date_of_birth date NOT NULL);

The above queries will create a new students_record table. This table has id, full_name, grade, subject, and date_of_birth columns.

Next, populate the table with the following data:

Step 2: Select students within a specific grade range

In this step, we’ll show you how to write a query that will select all students with grades B to D.

To do that, run the following SQL query:

SELECT * FROM students_record WHERE grade BETWEEN 'B' AND 'D';

The following table shows the result for the query:

When the BETWEEN operator uses a text value as the range, the search is done in lexicographical order. To better understand this, run the following query:

SELECT * FROM students_record WHERE full_name BETWEEN 'Mary West' AND 'Phil Jack';

The following table shows the result for the query:

The result of the query returns names from “Mary West” to “Phil Jack”. This is because only values from the full_name column whose first character is in greater order than M (with M inclusive) are selected all the way to “Phil.” Notice that the query stops at the closely related “Phoden” because the order of the third character “o” is greater than the “i” in “Phil.”

Step 3: Select students born between a date range

To select rows within two date ranges, run the following SQL query:

SELECT * FROM students_record WHERE date_of_birth BETWEEN '1994-01-01' AND '1999-12-31';

The following table shows the result for the query:

You must supply the start and end dates as strings by wrapping them with quotation marks.

Step 4: Select rows outside a range

Using the NOT operator, it’s possible to select only rows that are outside the range in your BETWEEN statement. You can do this by using the NOT BETWEEN operator.

Run the following query to fetch all records for students who were not born between January 1, 1994, and December 31, 1999:

SELECT * FROM students_record WHERE date_of_birth NOT BETWEEN '1994-01-01' AND '1999-12-31';

The following table shows the result for the query:

Step 5: Using BETWEEN with the IN operator

The IN operator takes a list of values separated by commas. Then it evaluates each row to determine whether any of the values are present. Unlike the BETWEEN operator that checks a range, the IN operator requires you to specify all the values you wish to filter by.

In this example, we’ll combine both the IN and BETWEEN operators to filter data on the students_record table in a new way.

To get started, run the following query:

SELECT * FROM students_record WHERE grade BETWEEN 'A' AND 'C' AND date_of_birth IN('1998-11-05', '1994-02-13');

The following table shows the result for the query:

Now let’s go over what the query really does.

The first part of the WHERE clause checks for students with grades between A and C. Then the IN operator further filters the result to only include students that meet the first condition and who were born on 1998-11-05 or 1994-02-13.

What is a time series database?

Time series databases are simply a special group of databases for storing data with time as a key factor. They usually store data as a key and value, where time is the key and the value could be a message.

Earlier in the post, you saw how to use the BETWEEN operator to filter data using a range of dates. Since a time series database prioritizes the period (time) for each value, it’s easy to filter data by two or more time ranges.

Conclusion

In this post, we covered the SQL BETWEEN operator. It’s an SQL operator that makes it easy to filter data by a range.

In addition, we walked you through several practical examples of using the SQL BETWEEN operator to fetch data from a table. The examples included using both text and dates as the range.

You also saw how you can use the BETWEEN operator with the IN operator to add two-level filtering to an SQL select query. They’re easier to use and more human-readable than chaining logical operators like >, <, AND, and = to achieve the same results.

About the author

This post was written by Pius Aboyi. Pius is a mobile and web developer with over 4 years of experience building for the Android platform. He writes code in Java, Kotlin, and PHP. He loves writing about tech and creating how-to tutorials for developers.