Introduction

NOT IN is a useful MySQL filter when you want rows whose values are not in a given set. The tricky part is null behavior and performance with subqueries. Correct usage requires understanding how NOT IN interacts with nulls and when alternatives such as NOT EXISTS are safer.

Basic NOT IN Syntax

Simple value exclusion is straightforward.

SELECT id, email
FROM users
WHERE status NOT IN ('disabled', 'deleted');

This returns active statuses while excluding listed values.

For numeric ids:

SELECT order_id
FROM orders
WHERE customer_id NOT IN (10, 11, 12);

This pattern is clear when exclusion list is small and static.

NOT IN with Subqueries

You can exclude rows based on another table.

1SELECT p.id, p.name
2FROM products p
3WHERE p.id NOT IN (
4    SELECT product_id
5    FROM discontinued_products
6);

This works only if subquery does not return null values in product_id.

Null Trap in NOT IN

If subquery returns at least one null, NOT IN comparisons become unknown and may return no rows unexpectedly.

Problem pattern:

SELECT p.id
FROM products p
WHERE p.id NOT IN (SELECT product_id FROM discontinued_products);

If discontinued_products.product_id includes null, result can be empty.

Fix by filtering nulls:

1SELECT p.id
2FROM products p
3WHERE p.id NOT IN (
4    SELECT product_id
5    FROM discontinued_products
6    WHERE product_id IS NOT NULL
7);

This is mandatory for correct semantics.

Prefer NOT EXISTS for Null Safety

NOT EXISTS usually avoids null pitfalls and performs well with proper indexing.

1SELECT p.id, p.name
2FROM products p
3WHERE NOT EXISTS (
4    SELECT 1
5    FROM discontinued_products d
6    WHERE d.product_id = p.id
7);

This is often the best default for exclusion by relation.

LEFT JOIN Alternative

Another common exclusion pattern is join plus null check.

1SELECT p.id, p.name
2FROM products p
3LEFT JOIN discontinued_products d
4    ON d.product_id = p.id
5WHERE d.product_id IS NULL;

This can be easier to read for teams already using join heavy query style.

Performance Considerations

For large tables, index the compared columns.

• products.id
• discontinued_products.product_id

Inspect plans with:

1EXPLAIN
2SELECT p.id
3FROM products p
4WHERE NOT EXISTS (
5    SELECT 1
6    FROM discontinued_products d
7    WHERE d.product_id = p.id
8);

Compare execution plans for NOT IN, NOT EXISTS, and LEFT JOIN on your actual dataset.

Dynamic Exclusion Lists from Application Code

If exclusion values come from application input, parameterize query safely.

Python example using placeholders:

1ids = [10, 11, 12]
2placeholders = ','.join(['%s'] * len(ids))
3sql = f"SELECT order_id FROM orders WHERE customer_id NOT IN ({placeholders})"
4
5cursor.execute(sql, ids)
6rows = cursor.fetchall()
7print(rows)

Never build SQL by direct string concatenation with unsanitized input.

Common Pitfalls

A common pitfall is forgetting null filtering in subqueries used with NOT IN, causing unexpectedly empty result sets.

Another issue is using huge literal lists in SQL text repeatedly. Large lists can hurt parse time and plan quality; staging values in temporary tables can be better.

A third issue is assuming NOT IN and NOT EXISTS always behave identically. Null handling differences can change correctness.

Teams also skip EXPLAIN and index checks, then blame operator choice when the real issue is missing indexes.

Summary

• NOT IN works well for small known exclusion sets
• Subquery null values can break NOT IN results silently
• Filter nulls or use NOT EXISTS for safer exclusion logic
• Index compared columns and inspect execution plans
• Parameterize dynamic exclusion queries to keep SQL safe and maintainable