MySQL NOT IN Operator

By /
MySQL NOT IN Operator

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.

The MySQL 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,...);Code language: SQL (Structured Query Language) (sql)

Examples of the MySQL NOT IN Operator

Now let us look at some examples. Consider the following ‘Students’ table.

In Operator Students Table
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’);Code language: SQL (Structured Query Language) (sql)

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.

Not In Operator Multiple Values

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.

In Operator Marks Table
Marks Table

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;Code language: SQL (Structured Query Language) (sql)

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);Code language: SQL (Structured Query Language) (sql)

Our output looks as follows:

Not In Operator Nested Queries

Conclusion

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.

References