Why Avoid IN and NOT IN in SQL Queries: Performance Pitfalls and Better Alternatives

In this article, a senior architect explains why the SQL keywords IN and NOT IN should be avoided in many cases due to performance and correctness issues.

Using two tables t1 and t2 each with about 1.5 million rows, a query with NOT IN fails to use indexes, causing the query to run for minutes, while rewriting it with NOT EXISTS reduces execution time to seconds.

select * from t1 where phone not in (select phone from t2)

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

Common mistakes such as typing the wrong column in an IN sub‑query or having NULL values in the sub‑query can produce wrong results without raising errors, because NULL never compares equal to any value.

create table test1 (id1 int);
create table test2 (id2 int);
insert into test1 (id1) values (1),(2),(3);
insert into test2 (id2) values (1),(2);
select id1 from test1 where id1 in (select id2 from test2);

The root cause is that IN / NOT IN does not handle NULL correctly and cannot leverage indexes, whereas EXISTS / NOT EXISTS can be optimized by the query planner.

Recommended replacements include:

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

Using INNER JOIN or LEFT JOIN with appropriate IS NULL checks.

select * from test1 where EXISTS (select * from test2 where test2.id2 = test1.id1);
select * from test1 where NOT EXISTS (select * from test2 where test2.id2 = test1.id1);
select id1 from test1 inner join test2 on test1.id1 = test2.id2;
select id1 from test1 left join test2 on test1.id1 = test2.id2 where test2.id2 IS NULL;

For small, fixed sets (e.g., IN (0,1,2) ) using IN is still acceptable.