Why You Should Avoid IN/NOT IN in SQL and Use EXISTS, NOT EXISTS, or JOIN Instead

In this technical note a senior architect shares practical experiences with the IN and NOT IN operators in SQL, showing why they should be avoided in most cases.

WHY?

The author observed two major problems when using IN/NOT IN:

Low efficiency : A query that scans 1.5 million rows from two tables using WHERE phone NOT IN (SELECT phone FROM t2) took more than ten minutes because the NOT IN clause cannot use the index on phone . Rewriting the query with NOT EXISTS reduced the execution time to about 20 seconds.

Potentially wrong results : A simple typo that replaces id2 with id1 in an IN sub‑query does not raise an error; it silently returns incorrect rows. Moreover, when the sub‑query contains NULL values, NOT IN yields an empty result set because NULL does not compare equal to any non‑NULL value.

HOW?

1. Replace IN/NOT IN with EXISTS/NOT EXISTS

select * from t1 where not EXISTS (select phone from t2 where t1.phone = t2.phone)

2. Replace IN/NOT IN with JOIN

-- inner join to get matching rows
select id1 from test1 INNER JOIN test2 ON test1.id1 = test2.id2;

-- left join to find rows in test1 that have no match in test2
select id1 from test1 LEFT JOIN test2 ON test1.id1 = test2.id2 WHERE test2.id2 IS NULL;

Both approaches allow the optimizer to use indexes and avoid the pitfalls of IN/NOT IN.

The author also notes that IN can still be appropriate when the set of values is small and known in advance, such as IN (0, 1, 2) .

Finally, a short interview checklist (BAT) is mentioned, encouraging readers to scan a QR code for a collection of real‑world interview questions.