Why IN and NOT IN Should Be Avoided in SQL Queries: Performance and Null‑Handling Pitfalls

IN and NOT IN are common SQL keywords, but they should be avoided whenever possible because they often result in low efficiency and can produce wrong query results.

In a real‑world case, two tables t1 and t2 each contain about 1.5 million rows. The query select * from t1 where phone not in (select phone from t2) took more than ten minutes even though both phone columns were indexed, because NOT IN cannot use the index.

Replacing the statement with select * from t1 where NOT EXISTS (select phone from t2 where t1.phone = t2.phone) reduced the execution time to about 20 seconds, demonstrating a dramatic performance improvement.

Another issue is that IN/NOT IN can silently produce incorrect results when NULL values are involved. For example, with tables test1(id1) and test2(id2) , using select id1 from test1 where id1 not in (select id2 from test2) after inserting a NULL into test2 returns an empty set instead of the expected missing value, because any comparison with NULL yields UNKNOWN.

To avoid these problems, the article suggests three safer alternatives:

Use EXISTS or NOT EXISTS instead of IN/NOT IN.

Use JOIN (INNER JOIN for matching rows, LEFT JOIN with WHERE id2 IS NULL for non‑matching rows).

When the set of values is known and small, IN can still be used, e.g., IN (0,1,2) .

By following these guidelines, developers can write more efficient and reliable SQL queries.