MySQL NOT IN

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:

value NOT IN (value1, value2, value2)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):

SELECT 1 NOT IN (1,2,3);Code language: SQL (Structured Query Language) (sql)

Output:

+------------------+
| 1 NOT IN (1,2,3) |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)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):

SELECT 0 NOT IN (1,2,3);Code language: SQL (Structured Query Language) (sql)

Output:

+------------------+
| 0 NOT IN (1,2,3) |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)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:

SELECT NULL NOT IN (1,2,3);Code language: SQL (Structured Query Language) (sql)

Output:

+---------------------+
| NULL NOT IN (1,2,3) |
+---------------------+
|                NULL |
+---------------------+
1 row in set (0.00 sec)Code language: plaintext (plaintext)

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

NOT (value = value1 OR value = value2 OR value = valu3)Code language: SQL (Structured Query Language) (sql)

Or:

value <> value1 AND value <> value2 AND value <> value3Code language: SQL (Structured Query Language) (sql)

MySQL NOT IN operator example

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

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

SELECT 
    officeCode, 
    city, 
    phone
FROM
    offices
WHERE
    country NOT IN ('USA' , 'France')
ORDER BY 
    city;Code language: SQL (Structured Query Language) (sql)

Output:

+------------+--------+------------------+
| officeCode | city   | phone            |
+------------+--------+------------------+
| 7          | London | +44 20 7877 2041 |
| 6          | Sydney | +61 2 9264 2451  |
| 5          | Tokyo  | +81 33 224 5000  |
+------------+--------+------------------+
3 rows in set (0.02 sec)Code language: plaintext (plaintext)

Summary

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