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
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
IN operator always goes with the
WHERE clause. The syntax is as follows:
SELECT expression FROM table_name WHERE column_name IN (value1, value2, ...);
Examples of the MySQL IN Operator
Let us consider the following 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’);
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:
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.
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);
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.
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:
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
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.