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.
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 UPDATE
and DELETE
operations.
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:
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.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.
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:
OutputQuery OK, 1 row affected (0.01 sec)
To select the between_in_db
database, run the following USE
statement:
- USE between_in_db;
OutputDatabase 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 characterswidgets
: the total number of widgets each salesperson has sold, expressed with the int
data typedoodads
: 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
- );
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
- 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.
WHERE
Clause PredicatesIn 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 secondNull: 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 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 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.
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
- 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.
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.
Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.
Structured Query Language — commonly known as SQL — is a language used to define, control, manipulate, and query data held in a relational database. SQL has been widely adopted since it was first developed in the 1970s, and today it’s the predominant language used to manage relational database management systems.
Ideal for managing structured data (data that can fit neatly into an existing data model), SQL is an essential tool for developers and system administrators in a wide variety of contexts. Also, because of its maturity and prevalence, candidates with SQL experience are highly sought after for jobs across a number of industries.
This series is intended to help you get started with using SQL. It includes a mix of conceptual articles and tutorials which provide introductions to various SQL concepts and practices. You can also use the entries in this series for reference while you continue to hone your skills with SQL.
Note: Please be aware that the tutorials in this series use MySQL in examples, but many RDBMSs use their own unique implementations of SQL. Although the commands outlined in this tutorial will work on most RDBMSs, the exact syntax or output may differ if you test them on a system other than MySQL.
This textbox defaults to using Markdown to format your answer.
You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!
Sign up for Infrastructure as a Newsletter.
Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.
Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.