MySQL IN Operator

By /
SQL IN Operator

Suppose you are a boarding school administrator and you want to know which of your students are from Pune, Nagpur, and Mumbai from an SQL table. You can always use multiple OR operators but there is another, more effective way.

We are looking at students in Pune, Nagpur, and Mumbai. And MySQL, like the beautiful language it is, makes you have an IN operator.

The MySQL IN Operator is used along with the WHERE Clause to specify multiple conditions. It’s a good substitute if you are using multiple OR operators on the same column.

Syntax of the MySQL IN Operator

Remember, IN operator always goes with the WHERE clause. The syntax is as follows:

SELECT expression FROM table_name WHERE column_name IN (value1, value2, ...);Code language: SQL (Structured Query Language) (sql)

Examples of the MySQL IN Operator

Let us consider the following Students table.

In Operator Students Table
Students Table

Using Multiple Values with the IN Operator

Now that we know the syntax of the IN operator’s usage, let us get a list of Students from Pune, Mumbai, and Nagpur. We will do it using the following query:

SELECT * FROM Students WHERE City IN (‘Pune’, ‘Mumbai’, ‘Nagpur’);Code language: SQL (Structured Query Language) (sql)

A quick note, you need to put in single/double quotes around text values. There is no need for quotes in the case of numerical values. You will get the following output: 

In Operator Multiple Values
Multiple Values with IN Operator

As you can see, we get records of only those students who are from Pune, Mumbai or Nagpur.

Using the IN Operator with Nested Queries

Suppose there is an online examination for the students and the results of the online exam are stored in a MySQL table called Marks.

In Operator Marks Table
Marks Table

It seems that the table only stores the ID of the student along with their marks in English, Maths, and Science. A student passes a subject if he/she scores more than 40 in that subject. Let us use the above tables to retrieve the details of students who failed in maths.

For this, we will have to use a nested query along with the IN operator. We can do it as follows,

SELECT * FROM Students WHERE ID IN (SELECT StudentID FROM Marks WHERE Maths<40);Code language: SQL (Structured Query Language) (sql)

Yes, that is quite complex. Let us break down the flow in the query to understand it better. The below diagram shows how the above query executes.

In Operator Nested Query Explanation

As you can see, the inner query executes first and outputs the IDs 2 and 4 as students with the said ID have marks in Maths lower than 40. These values are passed in the outer query which retrieves the details of the students with ID – 2 and 4.

We get the output as follows: 

In Operator Nested Query
IN Operator using Nested Query

Well, looks like those two are in trouble but fear not, you aren’t since you just know everything there is to know about the IN operator.

Conclusion

The IN operator helps you in writing readable queries by omitting the need for multiple OR operators if the action is on the same column. I would encourage you to check out the references for further reading.

References