(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