MySQL NOT IN: Syntax, Examples, and NULL Behavior

Use NOT IN to test whether a value is different from every value in a list or subquery. It is the negation of IN, but NULL can make the result NULL rather than true or false. This matters when NOT IN appears in a WHERE clause.

MySQL NOT IN syntax

expression NOT IN (value_1, value_2, ...)

The list can also come from a subquery:

expression NOT IN (SELECT column_name FROM table_name)

When neither side contains NULL, value NOT IN (a, b) is equivalent to value <> a AND value <> b. If a comparison involves NULL, SQL’s three-valued logic applies: the result can be UNKNOWN, represented by NULL in MySQL. A WHERE clause keeps only rows for which its condition is true.

For example, NOT IN returns 1 for 3 NOT IN (1, 2), 0 for 2 NOT IN (1, 2), and NULL for both NULL NOT IN (1, 2) and 3 NOT IN (1, 2, NULL).

SELECT
    3 NOT IN (1, 2) AS absent,
    2 NOT IN (1, 2) AS present,
    NULL NOT IN (1, 2) AS null_left,
    3 NOT IN (1, 2, NULL) AS null_in_list;

+--------+---------+-----------+--------------+
| absent | present | null_left | null_in_list |
+--------+---------+-----------+--------------+
|      1 |       0 |      NULL |         NULL |
+--------+---------+-----------+--------------+

The NOT IN and NULL pitfall

Suppose a customer table contains three customers and a second table lists blocked customer IDs. The blocked-ID table allows NULL values:

CREATE TABLE customer_demo (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(50) NOT NULL
);

CREATE TABLE blocked_customer_ids (
    customer_id INT NULL
);

INSERT INTO customer_demo (customer_id, customer_name) VALUES
    (101, 'Ava'),
    (102, 'Ben'),
    (103, 'Cara');

INSERT INTO blocked_customer_ids (customer_id) VALUES
    (102),
    (NULL);

This query looks for customers whose IDs are not in the blocked list:

SELECT customer_id, customer_name
FROM customer_demo
WHERE customer_id NOT IN (
    SELECT customer_id
    FROM blocked_customer_ids
);

It returns no rows. Customer 102 is an exact match, so NOT IN is false. For Ava and Cara, the comparison against the NULL in the subquery is unknown; WHERE filters out those rows too.

If the subquery can return NULL, use NOT EXISTS to express the anti-match directly:

SELECT c.customer_id, c.customer_name
FROM customer_demo AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM blocked_customer_ids AS b
    WHERE b.customer_id = c.customer_id
);

This returns Ava and Cara because no matching non-NULL blocked ID exists. See MySQL EXISTS and NOT EXISTS for more subquery examples.

+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
|         101 | Ava           |
|         103 | Cara          |
+-------------+---------------+

Another option is to remove NULL values from the subquery result:

SELECT customer_id, customer_name
FROM customer_demo
WHERE customer_id NOT IN (
    SELECT customer_id
    FROM blocked_customer_ids
    WHERE customer_id IS NOT NULL
);

This also returns Ava and Cara. Use this form only when the NULL values are not meaningful to the query.

When to use NOT IN

• Use NOT IN with a short, explicit list when the values are known to be non-NULL.
• Use NOT IN (subquery) when the subquery column is guaranteed to be non-NULL.
• Use NOT EXISTS when a subquery might return NULL, or when you want to state that no matching row exists.

The IN operator behaves similarly for NULL values, but returns NULL in the corresponding cases. See MySQL IN for its syntax and examples.

For comparison semantics, see the MySQL 8.4 Comparison Functions and Operators, EXISTS and NOT EXISTS subqueries, and subquery behavior with NULL.