Subqueries vs joins

A "correlated subquery" (i.e., one in which the where condition depends on values obtained from the rows of the containing query) will execute once for each row. A non-correlated subquery (one in which the where condition is independent of the containing query) will execute once at the beginning. The SQL engine makes this distinction automatically.

But, yeah, explain-plan will give you the dirty details.

You are running the subquery once for every row whereas the join happens on indexes.

Here's an example of how subqueries are evaluated in MySQL 6.0.

The new optimizer will convert this kind of subqueries into joins.