Differences Between IN and EXISTS in MySQL Queries

Difference Between IN and EXISTS

This article analyzes the behavior of IN and EXISTS subqueries in MySQL and draws conclusions about their performance characteristics.

IN query analysis

Example query: SELECT * FROM A WHERE id IN (SELECT id FROM B);

The subquery SELECT id FROM B is executed first, its result set is cached, and then each row of A is checked against the cache. If B is large, the cache can become costly because all B rows are materialized.

Performance illustration:

When A has 100 rows and B has 1,000 rows, up to 100 × 1,000 comparisons may be performed, leading to poor efficiency.

When A has 1,000 rows and B has 100 rows, the inner loop runs only 1,000 × 100 times, which is more efficient.

Conclusion: IN is suitable when the subquery returns a relatively small result set (i.e., B is smaller than A ), because it relies on an in‑memory cache.

EXISTS query analysis

Syntax: SELECT * FROM A WHERE EXISTS (SELECT 1 FROM B WHERE B.id = A.id);

The EXISTS subquery is evaluated for each row of A and stops as soon as it finds a matching row in B . It returns a Boolean value and does not cache the subquery result.

Example execution flow:

First, SELECT * FROM A retrieves rows from A .

For each row of A , the subquery SELECT 1 FROM B WHERE B.id = A.id is run to test existence.

When B is larger than A , EXISTS tends to be faster because it avoids materializing a large intermediate set.

Performance illustration:

If A has 100 rows and B has 1,000 rows, EXISTS performs 100 existence checks, which is efficient even though B is large.

If A has 10,000 rows and B has 100 rows, EXISTS still runs 10,000 checks, and in this case IN may be preferable because the in‑memory comparison is cheaper than repeated database lookups.

Overall conclusions

IN is best when the subquery returns a smaller set than the outer query (small‑table‑driven‑large‑table principle). EXISTS is advantageous when the inner table is larger, as it only needs to confirm existence without caching. When both tables are of similar size, the performance difference is negligible, and either can be used.