How To Use the BETWEEN and IN Operators in SQL
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
Although this guide will exclusively use
SELECT statements in its examples, the concepts explained here can be used in a number of SQL operations. In particular,
WHERE clauses and their search conditions are critical components of
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
WHEREclause 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.
Note: Please note that many RDBMSs use their own unique implementations of SQL. Although the commands outlined in this tutorial will work on most RDBMSs, including PostgreSQL and SQLite, the exact syntax or output may differ if you test them on a system other than MySQL.
You can also use an interactive terminal that is embedded on this page to experiment with the sample queries in this tutorial. Click the following
Launch an Interactive Terminal! button to get started.
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. If you are using the embedded interactive terminal on this page, note that the password to use when prompted is the word
- mysql -u sammy -p
From the prompt, create a database named
- CREATE DATABASE between_in_db;
If the database was created successfully, you’ll receive output like this:
OutputQuery OK, 1 row affected (0.01 sec)
To select the
between_in_db database, run the following
- USE between_in_db;
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
varchardata type with a maximum of 20 characters
widgets: the total number of widgets each salesperson has sold, expressed with the
doodads: the number of doodads each salesperson has sold, also expressed as an
gizmos: the number of gizmos each salesperson has sold, again expressed as an
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
OutputQuery 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
- ('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
IN operators to filter data.
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
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
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 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;
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
widgets columns of any rows whose
widgets value is between
- 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
- SELECT *
- FROM sales
- WHERE gizmos BETWEEN doodads AND widgets;
Only one member of the sales team has a
gizmos value that falls between their
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)
>= 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
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
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
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 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)
- . . .
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
gizmos columns for every row whose
gizmos value is a member of the set defined after the
- 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.
Note: When writing a subquery to define a set as part of a membership predicate, make sure that you use a scalar subquery, or a subquery that only returns a single column. Database management systems generally disallow subqueries that return multiple columns in a membership predicate, as it wouldn’t be clear to the database system which column it should evaluate as the set.
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
Then run the following query. This returns values from the
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.
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.