Skip navigation links
6.4.3 Sintaxe INSERT »
Section Navigation      [Toggle]

6.4.2.11. Rewriting Subqueries for Earlier MySQL Versions

Up to version 4.0, only nested queries of the form INSERT ... SELECT ... and REPLACE ... SELECT ... are supported. The IN() construct can be used in other contexts.

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 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 optimise 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 joins 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 version 4.1.

  • 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 ... 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 and redirect input from it to the mysql command-line interpreter, piping its output back to a second instance of the interpreter: 

    shell> mysql --skip-column-names mydb < myscript.sql | mysql mydb

MySQL Server 4.0 supports multiple-table DELETEs 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 UPDATEs are also supported from version 4.0.

This is a translation of the MySQL Reference Manual that can be found at dev.mysql.com. The original Reference Manual is in English, and this translation is not necessarily as up to date as the English version.