Why Avoid IN and NOT IN in SQL Queries and What to Use Instead

WHY？

IN and NOT IN are common keywords, but why should we avoid them?

1、Efficiency low

In a project, two tables t1 and t2 each contain about 1.5 million rows (≈600 MB). The following query runs for many minutes:

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

The query cannot use the index on phone , so it becomes extremely slow. Rewriting it with 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 make mistakes or get wrong results

Example with IN:

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

This works, but a typo such as using id1 instead of id2 in the sub‑query produces unexpected results without an error:

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

Another pitfall appears with NOT IN when the sub‑query contains NULL . Adding a NULL value to test2 makes the following query return no rows:

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

Because NULL is not equal to any value, the comparison fails and the expected rows are omitted.

Tip: When creating tables, avoid allowing NULLs in columns used for such comparisons.

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;

These alternatives avoid the performance and correctness issues of IN/NOT IN.

PS: IN can still be used for a known, small set of constant values, e.g., IN (0,1,2) .

Source: cnblogs.com/hydor/p/5391556.html

Backend Technical Community Invitation

Join our high‑quality technical exchange group; developers, recruiters, and anyone interested in sharing job referrals are welcome. Please keep discussions civil and focused on technology, job referrals, or industry topics.

Advertisement notice: Do not trust private messages; avoid scams.