(mysql.info) rewriting-subqueries

Info Catalog

(mysql.info) optimizing-subqueries

(mysql.info) subqueries

 
 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.0, 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.
 

Info Catalog

(mysql.info) optimizing-subqueries

(mysql.info) subqueries