Still Using IN and NOT IN in SQL? Here’s Why You Should Think Twice

IN is not forbidden, NOT IN needs caution

First, the conclusion: IN can be used safely, but NOT IN is the construct that truly requires careful handling.

Why the advice "don’t use IN, use EXISTS" is only half‑true

Many people reflexively repeat the mantra "don’t use IN, use EXISTS " when discussing SQL optimization. The real determinant of a query’s quality is not the keyword itself but the data volume, indexes, NULL values, optimizer rewrites, and the final execution plan.

Small sets: IN is normal, no need to over‑react

When the set is small, using IN is perfectly reasonable; large subqueries should be avoided. If NOT IN encounters a NULL, the result may deviate from expectations, often silently returning an empty set, which is hard to debug.

SELECT id, order_no, status
FROM orders
WHERE status IN ('PAID', 'SHIPPED', 'FINISHED'); -- small constant set, clear semantics, optimizer handles it well

There is no need to force such SQL to use EXISTS. With a small set and appropriate indexes, the optimizer can perform index filtering efficiently.

Large subqueries: EXISTS often matches the intent

If the requirement is “find users who have orders”, the question is essentially “does a matching order exist for the user?”. In this scenario EXISTS aligns better with the semantics.

SELECT u.id, u.name
FROM users u
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
      AND o.pay_status = 'SUCCESS'
);

EXISTS

cares only about the existence of a matching row; many databases can transform it into a semi‑join, stopping the scan as soon as a match is found.

NOT IN’s biggest danger: NULL

The most problematic aspect of NOT IN is not performance but the way NULL contaminates the logic. Consider the following example:

-- Find users who have never placed an order
SELECT u.id, u.name
FROM users u
WHERE u.id NOT IN (
    SELECT o.user_id
    FROM orders o -- a single NULL here can make the outer result empty
);

Because SQL uses three‑valued logic (true, false, unknown), the expression 5 <> NULL evaluates to unknown. Consequently the whole WHERE clause becomes unknown and the row is filtered out.

This silent failure is especially dangerous in reporting, permission filtering, or risk‑control scenarios.

Anti‑correlated queries: prefer NOT EXISTS

For “users without orders”, the author recommends writing it with NOT EXISTS:

SELECT u.id, u.name
FROM users u
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
);

NOT EXISTS

checks only whether a matching row exists; other rows’ user_id being NULL does not affect the decision. As long as there is no row where o.user_id = u.id, the outer query returns the user.

If you must use NOT IN, explicitly exclude NULL in the subquery.

SELECT u.id, u.name
FROM users u
WHERE u.id NOT IN (
    SELECT o.user_id
    FROM orders o
    WHERE o.user_id IS NOT NULL
);

The author’s habit is to default to NOT EXISTS for anti‑correlated queries, not because it is always the fastest, but because it is less prone to hidden logical errors.

Indexes are the key to performance

Do not focus solely on IN or EXISTS. Without proper indexes, any query can be slow.

CREATE INDEX idx_orders_user_status ON orders(user_id, pay_status); -- user_id for join, pay_status for filtering

This index lets the database quickly locate rows by user_id and then filter by pay_status. Without it, the inner orders table may be scanned repeatedly, causing performance degradation on large data sets.

When troubleshooting, always examine the execution plan instead of guessing.

EXPLAIN
SELECT u.id, u.name
FROM users u
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
      AND o.pay_status = 'SUCCESS'
);

Check the type, key, and rows columns to confirm whether an index is used and whether the scanned row count is reasonable. If key is empty or rows is huge, the slowness is likely due to missing or poorly designed indexes, not the choice between IN and EXISTS.

Practical judgment standards

For fixed small lists, use IN – clear and safe.

To test existence, prefer EXISTS – semantics are precise.

To test non‑existence, prefer NOT EXISTS, especially when the subquery may contain NULL.

When you see NOT IN with a subquery, first verify whether the subquery can return NULL, then inspect the execution plan.

SQL optimization is not about memorizing slogans; it is about understanding data volume, indexes, NULL semantics, and the actual execution plan.