MySQL IN Query

In the world of databases, queries are one of the constant things we all implement. Although SQL has ways and conventions for performing specific queries, sometimes it requires us to set custom conditions.

One of the popular and useful conditional operators in SQL is the IN operator. Using the IN operator, we can get a Boolean value if a specific value is in a list.

This tutorial will walk you through the IN operator and how to use it to create customized conditions for SQL queries.
Before we begin, we assume that you have a MySQL server installed and configured on your system, and you have a sample database with which to work on.

To get a sample version of a MySQL database, consider the Sakila database from the resource provided below:

https://dev.mysql.com/doc/index-other.html

Basic Usage

If you are not familiar, MySQL IN statement allows you to determine if a value is within a set of values. It mainly returns a Boolean-like value with 1 (true) if the value is in the set and 0 (false) if the value is not in the set.

The general syntax of the IN statement is:

SELECT column_name FROM table_name WHERE (expression | column_name) IN (“value1, value2…)

As you can see from the above syntax, the list of values is separated with commas inside the IN operator.

You can use either an expression or a column name with the IN operator inside the WHERE statement.

NOTE: Avoid combining quoted values such as strings and unquoted values such as numbers since the comparison differs for various types. An example of invalid use of the IN query is shown below:

SELECT column_name FROM table_name WHERE value1 IN (“a”, 10, 10);

Once a query as the one above is executed:

The values are evaluated based on the type of the specified column or expression result.
Then, the values are sorted, and finally, a search is completed using a binary search. This ensures that the search is fast with minimal errors.

NULL values return a NULL value.

Example Use Case

Let us illustrate how to use the IN operator using examples.

Let us start with a simple comparison that does not require a database. Consider the following statement:

SELECT 10 IN (5, 15, 25, 35);

If you execute the above query in a MySQL shell, you will get 0 (false), indicating that the value 10 is not in the provided set of values.

mysql> SELECT 10 IN (5, 15, 25, 35);
+-----------------------+
| 10 IN (5, 15, 25, 35) |
+-----------------------+
| 0 |
+-----------------------+
1 row in set (0.00 sec)

The case is also true if the value we are looking for is in the set. In this case, a 1 (true) is returned as illustrated in the query below:

SELECT 35 IN (5, 15,25,35);

The output will be as shown below:

mysql> SELECT 35 IN (5, 15,25,35);
+---------------------+
| 35 IN (5, 15,25,35) |
+---------------------+
| 1 |
+---------------------+
1 row in set (0.00 sec)

Suppose you have a table called film (see Sakila database) with rows as shown below:

We use the IN operator to find out the titles that have a rental duration of 3, as shown in the query below:

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

Once the above query executes, you will get all the films (limited to 5) where the rental_duration is equal to 3. Here is the sample output, as shown below:

mysql> SELECT film_id, title,rental_duration, rating FROM film WHERE rental_duration IN (3) LIMIT 5;
+---------+-----------------+-----------------+--------+
| film_id | title | rental_duration | rating |
+---------+-----------------+-----------------+--------+
| 2 | ACE GOLDFINGER | 3 | PG-13 |
| 6 | AGENT TRUMAN | 3 | PG |
| 9 | ALABAMA DEVIL | 3 | PG-13 |
| 17 | ALONE TRIP | 3 | R |
| 21 | AMERICAN CIRCUS | 3 | R |
+---------+-----------------+-----------------+--------+

As you can see from the above example, we can use the IN operator to customize our result.

Conclusion

This tutorial has shown you how to use and implement the MySQL IN operator to get customized results.

MySQL IN Query

Basic Usage

Example Use Case

Conclusion

About the author

John Otieno