Why NOT IN Can Be Inefficient and Incorrect: Use EXISTS, NOT EXISTS, or JOIN Instead

WHY?

Using IN and NOT IN can lead to poor performance and incorrect results.

1. Low efficiency

When tables contain large data, NOT IN cannot use indexes, causing long execution times. For example:

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

Replacing with NOT EXISTS reduces the query time dramatically:

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

2. Easy to produce wrong results

Typographical errors in column names can cause silent logical errors, as shown with test tables.

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);
-- correct usage
select id1 from test1 where id1 in (select id2 from test2);
-- wrong column name, returns unexpected rows
select id1 from test1 where id1 in (select id1 from test2);

Additionally, NOT IN with NULL values returns no rows 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);

HOW?

1. Use EXISTS or NOT EXISTS

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 JOIN

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 can still be used for small, fixed sets, e.g., IN (0, 1, 2).