The MySQL NOT IN Operator does the exact opposite of the MySQL
IN Operator. The
NOT IN operator checks if a value is not present in a set of values and returns those values.
Suppose you are a boarding school administrator and you want to know which of your students are not from Pune or Mumbai from an SQL table. Of course, this can be done using multiple
OR operators but there is an easier and more readable way – using the
NOT IN operator.
NOT IN operator is used along with the WHERE Clause to specify multiple conditions. If the record is a match for the value that is a part of the
IN operator, then that record is not a part of the result-set.
Syntax of the MySQL NOT IN Operator
The syntax for the
NOT IN operator is as follows,
SELECT expression FROM table_name WHERE column_name NOT IN (value1, value2,...);
Examples of the MySQL NOT IN Operator
Now let us look at some examples. Consider the following ‘Students’ table.
When we use only the
IN operator, we get our result-set with records that match the values given. However, when we use the
NOT IN operator, our result-set contains all the records except the ones that match the values given.
Using Multiple Values with the NOT IN Operator
Let’s get the list of students who are not from Pune and Mumbai. We will do it using the following query,
SELECT * FROM Students WHERE City NOT IN (‘Pune’, ‘Mumbai’);
Let us predict the output before we see what the output can be. As you can see in the above Students table image, students with IDs 1, 4, and 5 are residents of either Pune or Mumbai. So the above query should omit these records and display the output containing only the records of students with IDs 2, 3, and 6.
Let us see the output we get for the above query.
Bingo! Our output matches with what we predicted earlier.
Using the NOT IN Operator with Nested Queries
Now let us look at something more complex. Consider the following table named Marks that stores the results of the students of an online examination.
Suppose students receive Distinction if they score above 90 in a subject. How about retrieving the details of those students who did not get a Distinction in Science? To find the student IDs who did get a distinction in Science, we use the following query,
SELECT StudentID FROM Marks WHERE Science>90;
As per the above Marks table, we see that students with IDs 2, 5, and 6 received a distinction in Science.
Now, using the
NOT IN operator and nesting the above query, we find students who did not receive Distinction in Science as follows:
SELECT * FROM Students WHERE ID NOT IN (SELECT StudentID FROM Marks WHERE Science>90);
Our output looks as follows:
Just like the
IN operator, the
NOT IN operator improves the readability of your queries, removing the need for having multiple
OR operators if the action is on the same column. I would encourage you to check out the references for further reading.