MySQL IN Clause

This tutorial will discuss using the MySQL IN query to check if a specific value is within a set of values, which is useful in replacing a set of OR operators.

Basic Syntax

The MySQL IN operator is simple enough. It works by checking if a set has a match for the specified value.

The general syntax for the query is:

SELECT col_names FROM table_name WHERE expression IN (value_sets);

In the syntax above, we specify the function to carry out. It can be a SELECT, INSERT, DELETE, UPDATE, etc.

Next, we specify the name of the table from which to perform the specified action above.

The next part is the WHERE clause, where we specify the expression or value to evaluate. This is the value we test in the sets of values on the right side of the IN clause.

On the right hand of the clause, we specify the values from which to search for the matching expression.

If the IN clause finds a match on the specified set of values, it returns a 1, indicating true, and a 0, indicating false.

MySQL IN Clause Examples

Here are examples to illustrate the IN clause:

Example 1

A simple use case for the IN operator is to check for a single value within a set. For example, we can check if the character ‘A’ is in a set of characters.

SELECT 'A' IN ('A', 'B', 'C', 'D');

In the above example, we check if the character ‘A’ is IN a set of ABCD characters.

The query above should return 1 if true, as shown in the output below:

+-----------------------------+
| 'A' IN ('A', 'B', 'C', 'D') |
+-----------------------------+
| 1 |
+-----------------------------+
1 row in set (0.00 sec)

Example 2

A similar case applies if the value is not in a set of values. For example, the query below will return 0 or false.

SELECT 'Z' IN ('A', 'B', 'C', 'D');

Since Z is not in the set, the statement returns false as shown:

+-----------------------------+
| 'Z' IN ('A', 'B', 'C', 'D') |
+-----------------------------+
| 0 |
+-----------------------------+

Example 3

Let us illustrate the IN query with a more practical example. Let us take the actor table from the sample sakila database.

SELECT * FROM actor LIMIT 10;

+----------+------------+--------------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+--------------+---------------------+
| 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 |
| 2 | NICK | WAHLBERG | 2006-02-15 04:34:33 |
| 3 | ED | CHASE | 2006-02-15 04:34:33 |
| 4 | JENNIFER | DAVIS | 2006-02-15 04:34:33 |
| 5 | JOHNNY | LOLLOBRIGIDA | 2006-02-15 04:34:33 |
| 6 | BETTE | NICHOLSON | 2006-02-15 04:34:33 |
| 7 | GRACE | MOSTEL | 2006-02-15 04:34:33 |
| 8 | MATTHEW | JOHANSSON | 2006-02-15 04:34:33 |
| 9 | JOE | SWANK | 2006-02-15 04:34:33 |
| 10 | CHRISTIAN | GABLE | 2006-02-15 04:34:33 |
+----------+------------+--------------+---------------------+

In the example table above, we have the columns actor_id, first_name, last_name, and last_update.

We can use the IN query to get only the columns where the first name is a set of values.

Consider the query below:

SELECT * FROM actor WHERE first_name IN ('ED', 'BETTE', 'GRACE', 'JOHN', 'NICK');

The query above should return the columns for only the specified actors. An example result is below:

+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+-----------+---------------------+
| 2 | NICK | WAHLBERG | 2006-02-15 04:34:33 |
| 3 | ED | CHASE | 2006-02-15 04:34:33 |
| 6 | BETTE | NICHOLSON | 2006-02-15 04:34:33 |
| 7 | GRACE | MOSTEL | 2006-02-15 04:34:33 |
| 44 | NICK | STALLONE | 2006-02-15 04:34:33 |
| 136 | ED | MANSFIELD | 2006-02-15 04:34:33 |
| 166 | NICK | DEGENERES | 2006-02-15 04:34:33 |
| 179 | ED | GUINESS | 2006-02-15 04:34:33 |
| 192 | JOHN | SUVARI | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+

Example 4

We can also use the IN operator to check for numeric values. Let us change things and use the film table from the sakila database.

Suppose we want to get only the films where the rental_duration is 7.

SELECT film_id, title, rental_duration FROM film WHERE rental_duration IN (7) LIMIT 5;

The example query above should return the columns where the rental_duration is equal to 7.

+---------+------------------+-----------------+
| film_id | title | rental_duration |
+---------+------------------+-----------------+
| 3 | ADAPTATION HOLES | 7 |
| 27 | ANONYMOUS HUMAN | 7 |
| 36 | ARGONAUTS TOWN | 7 |
| 70 | BIKINI BORROWERS | 7 |
| 78 | BLACKOUT PRIVATE | 7 |
+---------+------------------+-----------------+
5 rows in set (0.00 sec)

Example 5 – Negation

MySQL also provides a negation for the IN operator. To use it, add the NOT keyword before the IN as:

SELECT * FROM table_name WHERE expression NOT IN (values);

For example, let us get the films that do not include the letter S in the title.

SELECT film_id, title, rental_duration FROM film WHERE 'S' NOT IN (title) LIMIT 5;

An example output is below:

+---------+------------------+-----------------+
| film_id | title | rental_duration |
+---------+------------------+-----------------+
| 1 | ACADEMY DINOSAUR | 6 |
| 2 | ACE GOLDFINGER | 3 |
| 3 | ADAPTATION HOLES | 7 |
| 4 | AFFAIR PREJUDICE | 5 |
| 5 | AFRICAN EGG | 6 |
+---------+------------------+-----------------+
5 rows in set (0.00 sec)

In Closing

In this article, we focused on using MySQL IN operator to determine if a value is within a set of values. This can help replace chained OR operators, making the query more readable and easier to manage.