In this tutorial, we will explore the WHERE IN clause to discover how we can use it to filter out the results from a given table or result set.
SQL WHERE IN Clause
The following shows the basic syntax of the WHERE IN clause in SQL:
FROM table_name
WHERE column_name IN (value1, value2, ...);
We start with a basic “select” statement followed by the columns that we wish to include in the result set.
Next, we specify the table from which we wish to retrieve the results. Lastly, we specify the filter condition using the WHERE clause followed by the name of the column on which we wish to filter. After the IN clause, we specify a list of value that we want to use for filtering.
Example 1: Filter a Single Result
To better demonstrate how to use the WHERE IN clause, let us look at an example. Consider the “film” table from the Sakila sample database.
Suppose we wish to retrieve all the films with a rating of PG or PG-13. We can use the WHERE IN clause as follows:
FROM film
WHERE rating IN ('PG');
In this case, we provide a list of a single value that we wish to retrieve in the IN clause.
Example 2: Filter Multiple Values
We can also specify more than one item in the list of values. For example, to retrieve the films with a list with a rating of PG and PG-13, we can run the query as follows:
FROM film
WHERE rating IN ('PG', 'PG-13');
The resulting output is as follows:
Example 3: Filter with Subquery
We can also use the WHERE IN in a subquery which can allow us to filter out the results from a given result set.
Suppose we wish to filter the films based on the language. For example, to retrieve the films in the English and Japanese languages, we can use the WHERE IN within a subquery as follows:
FROM film f
WHERE language_id IN (
SELECT language_id
FROM language
WHERE name IN ('English', 'Japanese')
);
In this example, we create a subquery that retrieves the “language_id” values for the English and Japanese languages from the “languages” table. In the main query, we select the films based on the resulting “language_id” values.
Conclusion
In this post, we learned how to work with the WHERE IN clause in SQL to filter out the results that match a single or multiple values in a given list.
