MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
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.
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. See Section 13.2.2, “DELETE Statement”, and
Section 13.2.11, “UPDATE Statement”.