Why IN and NOT IN Can Destroy SQL Performance (And Safer Alternatives)

1. Low Efficiency

In a project with two tables (t1 and t2) each containing about 1.5 million rows, a query using NOT IN fails to use indexes and becomes extremely slow.

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

After switching to NOT EXISTS, the same query finishes in about 20 seconds, demonstrating a dramatic performance improvement.

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

2. Easy to Cause Errors or Wrong Results

Using IN can silently hide mistakes. For example, creating two tables test1(id1 int) and test2(id2 int) and inserting values:

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

The intended query to find ids from test1 that exist in test2 is:

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

If the column name is mistakenly written as id1 inside the subquery, the query still runs without error and returns 1, 2, 3, which is incorrect.

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

Similarly, NOT IN behaves unexpectedly when the subquery contains NULL values. Adding a NULL to test2 and running:

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

returns an empty result set because NULL is not equal to any non‑null value, so the condition fails for all rows.

3. Safer Alternatives

Use EXISTS / 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);

Use JOINs :

-- Inner join (only matching rows)
select id1 from test1 inner join test2 on id2 = id1;

-- Left join with NULL check (rows not in test2)
select id1 from test1 left join test2 on id2 = id1 where id2 IS NULL;

These approaches avoid the performance pitfalls of IN/NOT IN and handle NULL values correctly.

PS: IN can still be used safely for small, fixed sets such as IN (0, 1, 2).