Why Avoid IN and NOT IN in SQL Queries? Performance and Pitfalls Explained

IN and NOT IN are frequently used SQL keywords, but they should be avoided whenever possible because they can cause serious performance degradation and produce incorrect results.

1. Efficiency loss – In a project two tables t1 and t2 each contain about 1.5 million rows (≈600 MB). The following query using NOT IN runs for many minutes even though both phone columns are indexed:

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

Switching to NOT EXISTS reduces the execution time to about 20 seconds:

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

2. Easy to write wrong queries – Using IN can silently produce wrong results if the sub‑query references the wrong column. Example tables test1(id1) and test2(id2) :

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 query
select id1 from test1 where id1 in (select id2 from test2);

-- typo: id1 used instead of id2
select id1 from test1 where id1 in (select id1 from test2);

The typo does not raise an error; it simply returns rows 1, 2, 3, which can be misleading.

3. NULL handling in NOT IN – If the sub‑query returns a NULL value, NOT IN yields an empty result set because NULL is not equal to any non‑NULL value. Adding a NULL to test2 and running:

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

produces no rows, even though we expected the value 3 to be returned.

How to avoid these problems

Use EXISTS or NOT EXISTS instead of IN/NOT IN:

select * from test1 where exists (select * from test2 where id2 = id1);
select * from test1 where not exists (select * from test2 where id2 = id1);

Or replace them with JOINs:

-- inner join (equivalent to EXISTS)
select id1 from test1 inner join test2 on test2.id2 = test1.id1;

-- left join with null check (equivalent to NOT EXISTS)
select id1 from test1 left join test2 on test2.id2 = test1.id1 where test2.id2 is null;

IN can still be useful for small, fixed sets such as IN (0,1,2) , but for large tables or when NULLs may appear, prefer EXISTS, NOT EXISTS, or JOINs.