Why IN/NOT IN Slows Queries and How to Use EXISTS or JOIN Instead

01 WHY?

IN and NOT IN are common SQL keywords, but they should be avoided because they are inefficient and can produce wrong results.

1. Low efficiency

Example: two tables t1 and t2 each contain about 1.5 million rows. The following query using NOT IN cannot use the index on phone and runs for many minutes.

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

Replacing the query 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

Using IN with a sub‑query can silently return wrong data, especially when column names are mismatched or NULL values are present.

Example tables test1(id1) and test2(id2):

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

If you mistakenly write id1 instead of id2, the query still runs and returns 1, 2, 3 without error.

When test2 contains a NULL value, NOT IN returns no rows because NULL is not equal to any non‑NULL value.

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

Result is empty due to the NULL.

02 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 approaches avoid the pitfalls of IN/NOT IN while delivering better performance.

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