13.2.8.11. Rewriting Subqueries as Joins for Earlier MySQL Versions
In previous versions of MySQL (prior to MySQL 4.1), only
nested queries of the form INSERT ... SELECT
... and REPLACE ... SELECT ...
were supported. Although this is not the case in MySQL
5.1, it is still true that there are sometimes
other ways to test membership in a set of values. It is also
true that 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 be rewritten using IN():
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.
MySQL Server supports multiple-table DELETE
statements that can be used to efficiently delete rows based
on information from one table or even from many tables at the
same time. Multiple-table UPDATE statements
are also supported.
