How To Use the BETWEEN and IN Operators in SQL

Introduction

In certain Structured Query Language (SQL) statements, WHERE clauses can be used to limit what rows the given operation will affect. They do this by defining specific criteria that each row must meet for it to be impacted, known as a search condition. Search conditions are made up of one or more predicates, or special expressions that evaluate to either “true,” “false,” or “unknown,” and operations only affect those rows for which every predicate in the WHERE clause evaluates to “true.”

SQL allows users to retrieve granular result sets by providing a variety of different types of predicates, each of which use a specific operator to evaluate rows. This guide will outline two types of predicates: range predicates which use the BETWEEN operator, and set membership predicates which use the IN operator.

Prerequisites

To follow this guide, you will need a computer running some type of relational database management system (RDBMS) that uses SQL. The instructions and examples in this guide were validated using the following environment:

• A server running Ubuntu 20.04, with a non-root user with administrative privileges and a firewall configured with UFW, as described in our initial server setup guide for Ubuntu 20.04.
• MySQL installed and secured on the server, as outlined in How To Install MySQL on Ubuntu 20.04. This guide was verified with a newly-created user, as described in Step 3.
• You’ll also need a database with some tables loaded with sample data which you can use to practice using different WHERE clause predicates. We encourage you to go through the following Connecting to MySQL and Setting up a Sample Database section for details on how to connect to a MySQL server and create the testing database used in examples throughout this guide.

Connecting to MySQL and Setting up a Sample Database

If your SQL database system runs on a remote server, SSH into your server from your local machine:

ssh sammy@your_server_ip

Then open up the MySQL server prompt, replacing sammy with the name of your MySQL user account:

mysql -u sammy -p

From the prompt, create a database named between_in_db:

CREATE DATABASE between_in_db;

If the database was created successfully, you’ll receive output like this:

Output
Query OK, 1 row affected (0.01 sec)

To select the between_in_db database, run the following USE statement:

USE between_in_db;

Output
Database changed

After selecting between_in_db, create a table within it.

To follow along with the examples used in this guide, imagine that you manage a company’s sales team. This company only sells three products: widgets, doodads, and gizmos. You begin tracking the number of units of each product each member of your team has sold in an SQL database. You decide that this database will have one table with four columns:

• name: the names of each member of your sales team, expressed using the varchar data type with a maximum of 20 characters
• widgets: the total number of widgets each salesperson has sold, expressed with the int data type
• doodads: the number of doodads each salesperson has sold, also expressed as an int
• gizmos: the number of gizmos each salesperson has sold, again expressed as an int

Run the following CREATE TABLE statement to create a table named sales that has these four columns:

CREATE TABLE sales (
name varchar(20),
widgets int,
doodads int,
gizmos int
);

Output
Query OK, 0 rows affected (0.01 sec)

Then load the sales table with some sample data. Run the following INSERT INTO operation to add seven rows of data representing the team’s salespeople and the number of each product that they’ve sold:

INSERT INTO sales
VALUES
('Tyler', 12, 22, 18),
('Blair', 19, 8, 13),
('Lynn', 7, 29, 3),
('Boris', 16, 16, 15),
('Lisa', 17, 2, 31),
('Maya', 5, 9, 7),
('Henry', 14, 2, 0);

With that, you’re ready to follow the rest of the guide and begin learning how to use the BETWEEN and IN operators to filter data.

Understanding WHERE Clause Predicates

In any SQL operation that reads data from an existing table, you can follow the FROM clause with a WHERE clause to limit what data the operation will affect. WHERE clauses do this by defining a search condition; any row that doesn’t meet the search condition is excluded from the operation, but any row that does is included.

A search condition is made up of one or more predicates, or expressions that can evaluate one or more value expressions and return a result of either “true,” “false,” or “unknown.” In SQL, a value expression — also sometimes referred to as a scalar expression — is any expression that will return a single value. A value expression can be a literal value, like a string or numeric value, a mathematical expression, or a column name. Note that it’s almost always the case that at least one value expression in a WHERE clause predicate is the name of a column in the table referenced in the operation’s FROM clause.

When running SQL queries that contain a WHERE clause, the DBMS will apply the search condition to every row in the logical table defined by the FROM clause. It will then return only the rows for which every predicate in the search condition evaluates to “true.”

The SQL standard defines 18 types of predicates, although not every RDBMS includes each of them in its implementation of SQL. Here are five of the most commonly used predicate types, as well as a brief explanation of each one and the operators they use:

Comparison: Comparison predicates compare one value expression with another; in queries, it’s almost always the case that at least one of these value expressions is the name of a column. The six comparison operators are:

• =: tests whether the two values are equivalent
• <>: tests whether two values are not equivalent
• <: tests whether the first value is less than the second
• >: tests whether the first value is greater than the second
• <=: tests whether the first value is less than or equal to the second
• >=: tests whether the first value is greater than or equal to the second

Null: Predicates that use the IS NULL operator test whether values in a given column are Null Range: Range predicates use the BETWEEN operator to test whether one value expression falls between two others Membership: This type of predicate uses the IN operator to test whether a value is a member of a given set Pattern Match: Pattern matching predicates use the LIKE operator to test whether a value matches a string pattern

As mentioned in the introduction, this guide focuses on outlining how to use SQL’s BETWEEN and IN operators to filter data. If you’d like to learn how to use the comparison or IS NULL operators, we encourage you to check out this guide on How To Use Comparison and IS NULL Operators in SQL. Alternatively, if you’d like to learn how to use the LIKE operator to filter data based on a string pattern containing wildcard characters, follow our guide on How To Use Wildcards in SQL. Lastly, if you’d like to learn more about WHERE clauses generally, you may be interested in our tutorial on How To Use WHERE Clauses in SQL.

Range Predicates

Range predicates use the BETWEEN operator to test whether one value expression falls between two other value expressions. A WHERE clause that includes a range predicate in its search condition will follow this general syntax:

SELECT column_list
FROM table_name
WHERE column_name BETWEEN value_expression1 AND value_expression2;

Following the WHERE keyword is a value expression which, in most SQL operations, is the name of a column. Because the database system applies search conditions to each row in sequence, providing a column name as a value expression in a search condition tells the RDBMS to use each row’s value from that column as the value expression for that row’s iteration of the search condition.

After the column name comes the BETWEEN operator and two more value expressions separated by AND. The search condition will resolve to “true” for any rows whose value from the specified column is greater than or equal to the first of the two values separated by AND, but less than or equal to the second.

To illustrate how range predicates work, run the following query. This will return the name and widgets columns of any rows whose widgets value is between 14 and 19, inclusive:

SELECT name, widgets
FROM sales
WHERE widgets BETWEEN 14 AND 19;

Output
+-------+---------+
| name  | widgets |
+-------+---------+
| Blair |      19 |
| Boris |      16 |
| Lisa  |      17 |
| Henry |      14 |
+-------+---------+
4 rows in set (0.00 sec)

Keep in mind that the range you define after the BETWEEN operator can consist of any pair of value expressions, including column names.

The following query returns every column from the sales table. Rather than listing out every column to return, it instead follows the SELECT keyword with an asterisk (*); this is SQL shorthand for “every column.” This query’s WHERE clause limits it to returning only rows whose gizmos value is greater than its doodads value but less than its widgets value:

SELECT *
FROM sales
WHERE gizmos BETWEEN doodads AND widgets;

Only one member of the sales team has a gizmos value that falls between their widgets and doodads values, so only that row appears in the result set:

Output
+-------+---------+---------+--------+
| name  | widgets | doodads | gizmos |
+-------+---------+---------+--------+
| Blair |      19 |       8 |     13 |
+-------+---------+---------+--------+
1 row in set (0.00 sec)

Be aware of the order in which you list the value expressions that define the range: the first value after the BETWEEN operator is always the lower end of the range and the second is always the upper end. The following query is identical to the previous one, except that it flips the order of the columns defining each end of the range:

SELECT *
FROM sales
WHERE gizmos BETWEEN widgets AND doodads;

This time, the query returns the two rows where the gizmos value is greater than or equal to the row’s widgets value but less than or equal its doodads value. As this output indicates, changing the order like this will return a completely different result set:

Output
+-------+---------+---------+--------+
| name  | widgets | doodads | gizmos |
+-------+---------+---------+--------+
| Tyler |      12 |      22 |     18 |
| Maya  |       5 |       9 |      7 |
+-------+---------+---------+--------+
2 rows in set (0.00 sec)

Like the <, >, <=, and >= comparison operators, when used to evaluate a column holding string values the BETWEEN operator will determine whether those values fall between two string values alphabetically.

To illustrate, run the following query which returns the name values from any row in the sales table whose name value is between the letters A and M, alphabetically.

This example uses two string literals as the value expressions that make up either end of the range. Note that these literal values must be wrapped in single or double quotes; otherwise, the DBMS will look for columns named A and M and the query will fail:

SELECT name
FROM sales
WHERE name BETWEEN 'A' AND 'M';

Output
+-------+
| name  |
+-------+
| Blair |
| Lynn  |
| Boris |
| Lisa  |
| Henry |
+-------+
5 rows in set (0.00 sec)

Notice that this result set doesn’t include Maya even though the range provided in the search condition is from A to M. This is because, alphabetically, the letter “M” comes before any string that starts with the letter “M” and has more than one letter, so Maya is excluded from this result set along with any other salespeople whose names do not lie within the given range.

Membership Predicates

Membership predicates allow you to filter query results based on whether a value is a member of a specified set of data. In WHERE clauses, they generally follow this syntax:

. . .
WHERE column_name IN (set_of_data)
. . .

After the WHERE keyword comes a value expression; again, this first value expression is usually the name of a column. Following that is the IN operator, itself followed by a set of data. You can explicitly define this set by listing any number of valid value expressions separated by commas, including literals or column names, or mathematical expressions involving either of these.

To illustrate, run the following query. This will return the name and gizmos columns for every row whose gizmos value is a member of the set defined after the IN operator:

SELECT name, doodads
FROM sales
WHERE doodads IN (1, 2, 11, 12, 21, 22);

Only three members of the sales team’s doodads values scores are equal to any of the values in this set, so only those rows get returned:

Output
+-------+---------+
| name  | doodads |
+-------+---------+
| Tyler |      22 |
| Lisa  |       2 |
| Henry |       2 |
+-------+---------+
3 rows in set (0.00 sec)

Instead of writing out each member of a set yourself, you can derive a set by following the IN operator with a subquery. A subquery — also known as a nested or inner query — is a SELECT statement embedded within one of the clauses of another SELECT statement. A subquery can retrieve information from any table in the same database as the table defined in the FROM clause of the “outer” operation.

As an example of using a subquery to define a set in a membership predicate, run the following statement to create a table named example_set_table that only has one column. This column will be named prime_numbers and will hold values of the int data type:

CREATE TABLE example_set_table (
prime_numbers int
);

Then load this table with a couple rows of sample data. In keeping with the name of the table’s sole column, the following INSERT statement will load ten rows of data into the table, with each holding one of the first ten prime numbers:

INSERT INTO example_set_table
VALUES
(2),
(3),
(5),
(7),
(11),
(13),
(17),
(19),
(23),
(29);

Then run the following query. This returns values from the name and widgets columns from the sales table, and its WHERE clause tests whether each value in the widgets column is in the set derived by the subquery SELECT prime_numbers FROM example_set_table:

SELECT name, widgets
FROM sales
WHERE widgets IN (SELECT prime_numbers FROM example_set_table);

Output
+-------+---------+
| name  | widgets |
+-------+---------+
| Blair |      19 |
| Lynn  |       7 |
| Lisa  |      17 |
| Maya  |       5 |
+-------+---------+
4 rows in set (0.00 sec)

Because only four salespeople have sold a number of widgets equal to any of the prime numbers stored in the example_set_table, this query only returns those four rows.

Conclusion

By following this guide, you learned how to use SQL’s BETWEEN operator to test whether values in a column fall within a given range. You also learned how to use the IN operator to test whether values in a column are members of a set.

While the commands shown here should work on most relational databases, be aware that every SQL database uses its own unique implementation of the language. You should consult your DBMS’s official documentation for a more complete description of each command and their full sets of options.

If you’d like to learn more about working with SQL, we encourage you to check out the other tutorials in this series on How To Use SQL.