Why You Should Avoid IN and NOT IN in SQL Queries: Performance Pitfalls and Logical Errors

IN and NOT IN are frequently used SQL keywords, but they should be avoided whenever possible because they can lead to poor performance and subtle logical bugs.

In a scenario where two tables t1 and t2 each contain about 1.5 million rows, a query using WHERE phone NOT IN (SELECT phone FROM t2) forces a full scan and runs for many minutes, while rewriting the query with WHERE NOT EXISTS (SELECT phone FROM t2 WHERE t1.phone = t2.phone) finishes in about 20 seconds by allowing index usage.

Beyond performance, IN can produce wrong results if the subquery references the wrong column. An example with tables test1(id1) and test2(id2) shows that mistakenly writing SELECT id1 FROM test1 WHERE id1 IN (SELECT id1 FROM test2) returns rows that should not match, and the database does not raise an error.

Another danger appears when the subquery contains NULL values. After inserting a NULL into test2 , the query SELECT id1 FROM test1 WHERE id1 NOT IN (SELECT id2 FROM test2) yields an empty result set because any comparison with NULL evaluates to UNKNOWN, eliminating the expected rows.

To avoid these issues, replace IN/NOT IN with EXISTS or NOT EXISTS clauses, or use appropriate JOIN constructions. Example replacements are: SELECT * FROM test1 WHERE EXISTS (SELECT * FROM test2 WHERE id2 = id1) SELECT * FROM test1 WHERE NOT EXISTS (SELECT * FROM test2 WHERE id2 = id1) and SELECT id1 FROM test1 INNER JOIN test2 ON id2 = id1 SELECT id1 FROM test1 LEFT JOIN test2 ON id2 = id1 WHERE id2 IS NULL .

Finally, IN can still be useful for small, fixed sets of constants, such as IN (0, 1, 2) , where the drawbacks discussed do not apply.