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:

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.

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:

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.

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

Example 3

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

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:

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

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.

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

Example 5 – Negation

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

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

An example output is below:

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.