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:
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:
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:
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:
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.
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:
The output will be as shown below:
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:
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:
| 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.
This tutorial has shown you how to use and implement the MySQL IN operator to get customized results.