13.2.10.11 Rewriting Subqueries as Joins

Sometimes there are other ways to test membership in a set of
values than by using a subquery. Also, on some occasions, it is
not only possible to rewrite a query without a subquery, but it
can be more efficient to make use of some of these techniques
rather than to use subqueries. One of these is the
IN() construct:

For example, this query:

SELECT * FROM t1 WHERE id IN (SELECT id FROM t2);

Can be rewritten as:

SELECT DISTINCT t1.* FROM t1, t2 WHERE t1.id=t2.id;

The queries:

SELECT * FROM t1 WHERE id NOT IN (SELECT id FROM t2);
SELECT * FROM t1 WHERE NOT EXISTS (SELECT id FROM t2 WHERE t1.id=t2.id);

Can be rewritten as:

SELECT table1.*
FROM table1 LEFT JOIN table2 ON table1.id=table2.id
WHERE table2.id IS NULL;

A LEFT [OUTER] JOIN can be faster than an
equivalent subquery because the server might be able to optimize
it better—a fact that is not specific to MySQL Server
alone. Prior to SQL-92, outer joins did not exist, so subqueries
were the only way to do certain things. Today, MySQL Server and
many other modern database systems offer a wide range of outer
join types.