Why Avoid IN and NOT IN in SQL? Performance Issues and Common Pitfalls

In many SQL queries developers habitually use IN and NOT IN , but these operators often cause severe performance degradation because they cannot leverage indexes, especially on large tables.

Example: select * from t1 where phone not in (select phone from t2) on two 1.5‑million‑row tables caused the query to stall, while rewriting it with NOT EXISTS reduced execution time to about 20 seconds.

Beyond speed, IN / NOT IN are prone to logical errors. A typo that references the wrong column (e.g., using id1 instead of id2 ) may silently return incorrect rows without raising an error, because the sub‑query’s column mismatch is ignored.

Another classic pitfall occurs when the sub‑query returns NULL values. With NOT IN , any NULL in the set makes the whole predicate evaluate to UNKNOWN, so rows that should be returned (e.g., id 3) are filtered out, leading to empty results.

To avoid these issues, the article recommends:

Replacing IN / NOT IN with EXISTS or NOT EXISTS , which correctly handle NULLs and allow index usage.

Using JOIN (inner or left join with IS NULL ) as an alternative for set‑based filtering.

It also notes that IN can still be appropriate when the set is a small, constant list (e.g., IN (0,1,2) ).