Summary: in this tutorial, you’ll learn how to use the MySQL NOT IN operator to check if a value is not in a list of values.

Introduction to the MySQL NOT IN operator

The NOT operator negates the IN operator:

Code language: SQL (Structured Query Language) (sql)

The NOT IN operator returns one if the value doesn’t equal any value in the list. Otherwise, it returns 0.

The following example uses the NOT IN operator to check if the number 1 is NOT IN the list (1,2,3):

Code language: SQL (Structured Query Language) (sql)

Output:

Code language: plaintext (plaintext)

It returns 0 (false) because 1 is NOT IN the list is false.

The following example uses the NOT IN operator to check if 0 is NOT IN the list (1,2,3):

Code language: SQL (Structured Query Language) (sql)

Output:

Code language: plaintext (plaintext)

The NOT IN operator returns NULL if the value on the left side of the IN operator is NULL. For example:

Code language: SQL (Structured Query Language) (sql)

Output:

Code language: plaintext (plaintext)

Technically, the NOT IN operator is equivalent to the following:

Code language: SQL (Structured Query Language) (sql)

Or:

Code language: SQL (Structured Query Language) (sql)

MySQL NOT IN operator example

We’ll use the offices table from the sample database to illustrates the NOT IN operator:

The following example uses the NOT IN operator to find the offices that do not locate in France and the USA:

Code language: SQL (Structured Query Language) (sql)

Output:

Code language: plaintext (plaintext)

Summary

• Use the MySQL NOT IN to check if a value doesn’t match any value in a list.