Why IN and NOT IN Can Kill Your SQL Performance and How to Fix It

WHY?

IN and NOT IN are common keywords, but they should be avoided when possible.

1. Low efficiency

In a project, two tables t1 and t2 each have 1.5 million rows (~600 MB). The query using NOT IN runs extremely slow because NOT IN cannot use the index.

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

After changing to NOT EXISTS the query finishes in about 20 seconds.

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

2. Easy to make mistakes, wrong results

Example with tables test1(id1) and test2(id2). Using IN correctly returns matching ids.

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);

If the inner query mistakenly selects id1 instead of id2, the query still runs without error and returns unexpected rows.

select id1 from test1 where id1 in (select id1 from test2);

Similarly, NOT IN can produce empty results when the subquery contains NULL because NULL does not compare equal to any value.

insert into test2 (id2) values (NULL);
select id1 from test1 where id1 not in (select id2 from test2);

The result is empty, not the expected missing ids.

Tip: avoid allowing NULLs in columns used for such comparisons.

HOW?

1. Use EXISTS or NOT EXISTS instead

select * from test1 where EXISTS (select * from test2 where id2 = id1);
select * from test1 where NOT EXISTS (select * from test2 where id2 = id1);

2. Use JOINs

select id1 from test1 INNER JOIN test2 ON id2 = id1;
select id1 from test1 LEFT JOIN test2 ON id2 = id1 where id2 IS NULL;

In cases where the set is known and small, IN can still be used, e.g., IN (0,1,2).