DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH PRINT BOOK
 

(mysql.info.gz) Rewriting subqueries

Info Catalog (mysql.info.gz) Optimizing subqueries (mysql.info.gz) Subqueries
 
 13.1.8.11 Rewriting Subqueries as Joins for Earlier MySQL Versions
 ..................................................................
 
 Before MySQL 4.1, only nested queries of the form `INSERT ... SELECT
 ...' and `REPLACE ... SELECT ...' are supported.  The `IN()' construct
 can be used in other contexts to test membership in a set of values.
 
 It is often possible to rewrite a query without a subquery:
 
      SELECT * FROM t1 WHERE id IN (SELECT id FROM t2);
 
 This 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 in those
 bygone days. Today, MySQL Server and many other modern database systems
 offer a whole range of outer join types.
 
 For more complicated subqueries, you can often create temporary tables
 to hold the subquery.  In some cases, however, this option will not
 work. The most frequently encountered of these cases arises with
 `DELETE' statements, for which standard SQL does not support joins
 (except in subqueries).  For this situation, there are three options
 available:
 
    * The first option is to upgrade to MySQL 4.1, which does support
      subqueries in `DELETE' statements.
 
    * The second option is to use a procedural programming language
      (such as Perl or PHP) to submit a `SELECT' query to obtain the
      primary keys for the records to be deleted, and then use these
      values to construct the `DELETE' statement (`DELETE FROM ... WHERE
      key_col IN (key1, key2, ...)').
 
    * The third option is to use interactive SQL to construct a set of
      `DELETE' statements automatically, using the MySQL extension
      `CONCAT()' (in lieu of the standard `||' operator).  For example:
 
           SELECT
             CONCAT('DELETE FROM tab1 WHERE pkid = ', "'", tab1.pkid, "'", ';')
             FROM tab1, tab2
            WHERE tab1.col1 = tab2.col2;
 
      You can place this query in a script file, use the file as input
      to one instance of the `mysql' program, and use the program output
      as input to a second instance of `mysql':
 
           shell> mysql --skip-column-names mydb < myscript.sql | mysql mydb
 
 MySQL Server 4.0 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 as of MySQL 4.0.
 
Info Catalog (mysql.info.gz) Optimizing subqueries (mysql.info.gz) Subqueries
automatically generated byinfo2html