MySQL | NOT IN() Function

MySQL is an effective and powerful RDBMS, having structured data stored in the form of tables. To manipulate these tables, there are many built-in functions in MySQL. One such function is the “NOT IN()” function, which aids in filtering data that does not match the specified values in the condition.

This post will discuss the “NOT IN()” function in MySQL along with its examples.

What is the “NOT IN()” Function?

The “NOT IN()” function in MySQL is used to check if the value exists in the list of values or not. It will return “TRUE” or “1” if it is not available. Otherwise, it will return “False” or “0”. This function can also be used to filter the data that does not meet the specified value.

Syntax of the “NOT IN()” Function in MySQL

The “NOT IN()” function in MySQL syntax is given below:

[VALUE] NOT IN ([value1], [value2], [VALUE], ...)

The syntax of the “NOT IN()” function means the same as this:

NOT ([VALUE] = [value1] OR [VALUE] = [value2] OR [VALUE] = [value3] OR ......)

Let’s see examples of the “NOT IN()” function in MySQL.

Example 1: Verify Non-Existence Using the “NOT IN()” Function

The user can provide a value to check in an expression containing multiple values to return output based on its non-existence. To demonstrate, execute this query:

SELECT 0 NOT IN (1,2,3,4,5);

The output will return “1” if the value “0” does not exist in the provided values:

Let’s see another example, but this time, provide a value that exists in the expression, run this query:

SELECT 3 NOT IN (1,2,3,4,5);

The output returns “0” because the value exists in the provided example:

Example 2: Filter Records Form the Table Using Simple Condition Having the “NOT IN()” Function

The “NOT IN()” function can be used in the “WHERE” clause in the “SELECT” statement to filter records that do not match the specified values in the condition.

Run this query to extract records where the “id” of the table “employee” is not equal to “1”, “3”, “5” and “6”:

SELECT *
FROM employee
WHERE id NOT IN (1, 3, 5, 6);

The output have filtered the records successfully by excluding the records that fulfilled the condition:

Let’s see another example to filter the data, where the value of “City” is not equal to “Berlin”, “London”, “Buenos Aires” and “Graz”. Run the given below query:

SELECT *
FROM Customer
WHERE City NOT IN ('Berlin', 'London', 'Buenos Aires', 'Graz');

The output has filtered the data from the table where the “City” value fulfills the condition:

Example 3: Filter Records Form the Table Using Compound Condition Having the “NOT IN()” Function

The “NOT IN()” function can be used inside the compound Condition. The compound statement consists of more than one condition with the logical operators placed between these conditions.

For example, the user wants to filter data from a table named “product”, where the “id” is less than “15” and “UnitPrice” is not equal to “18”, “22”, or “25”. By running the query:

SELECT *
FROM Product
WHERE id < 15
AND UnitPrice NOT IN (18,22,25);

Note: The “AND” operator returns “TRUE” when both conditions are fulfilled.

The output is filtering records that match the defined compound condition:

You have successfully extracted the data by filtering out the data that meets the condition.

Conclusion

The “NOT IN()” function is used to check for the non-existence of a value in a list of values. You can use this in the simple “SELECT” statement to get “1” if the value does not exist in the list of values otherwise returns “0”. It can also be used to filter data from the table where the data does not exist that fulfills a condition. This post discussed the usage of the “NOT IN()” function in MySQL.