Is EXISTS faster than IN? What does the optimizer actually do with each one?
Usually there is no difference at all, because a good planner turns the two forms into the same thing.
Here is what the engine does with each one.
With IN, the engine builds a set out of the whole subquery and then checks each row of the outer query against that set. With EXISTS, the engine looks for the first match for each row of the outer query and stops as soon as it finds one.
Both of those are called semi joins, and both of them give the same answer. The difference is in how the work is done.
-- The IN version. The engine builds the whole set first.
SELECT c.id, c.name FROM customers c
WHERE c.id IN (SELECT o.customer_id FROM orders o);
-- The EXISTS version. The engine can stop at the first match.
SELECT c.id, c.name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
So what is the optimizer actually doing? In PostgreSQL and in MySQL 8 and in SQL Server, the planner rewrites an IN subquery into the same semi join that EXISTS produces. So the two plans come out the same and the two run at the same speed.
There is one case where they really are different, and that is NOT IN. The planner cannot rewrite that one, because of the missing values, and a NOT IN with a subquery is often much slower than a NOT EXISTS. It is also wrong in the way we looked at earlier, so it is worth avoiding whatever the speed.
So the rule to remember is this. Write whichever one reads better, and let the planner make them the same. Do not write NOT IN.
The next two questions in this topic look at the plan and at the size of the subquery.