Skip navigation links
MySQL 3.23, 4.0, 4.1 Reference Manual :: 12 SQL Statement Syntax :: 12.2 Data Manipulation Statements :: 12.2.8 Subquery Syntax :: 12.2.8.11 Rewriting Subqueries as Joins for Earlier MySQL Versions
12.2.9 TRUNCATE TABLE Syntax »
Section Navigation      [Toggle]

12.2.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. Today, MySQL Server and many other modern database systems offer a wide 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 does 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 which obtains the primary keys for the rows to be deleted, and then use these values to construct the appropriate 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.


User Comments

Posted by [name withheld] on January 17 2004 11:11pm[Delete] [Edit]

Here is a rough description of one more method to do something equivalent to a subquery in a DELETE for older versions of MySQL that do not support it. It only works for the case where there is a unique key on the table. It requires making a temporary table and a temporary column on the table from which you wish to delete. However it's all done in a sequence of SQL commands which is a little cleaner than the other methods.

Let's say the table from which you want to delete is "origtable". First create a temporary table with the same structure as origtable (though without autoincrement columns), let's call it "temptable". Then fill it with the rows of origtable that you wish to delete, for example:

INSERT INTO temptable SELECT ... FROM origtable WHERE ...

It's important that all results from the SELECT statement should have unique keys that are present in origtable, in other words don't do something weird here like taking the cosine of the primary key. Next create a temporary new column on origtable to designate which rows you intend to delete, and default this to 1:

ALTER TABLE origtable ADD `tempdeleteflag` int(12) NOT NULL default 1;

Next set tempdeleteflag to 0 for the rows that are present in temptable using REPLACE:

REPLACE origtable SELECT id, col1, col2, ..., colN, 0 FROM temptable;

Now tempdeleteflag should be set to 0 for rows you intended to keep or 1 for rows you inteded to delete, so:

DELETE FROM origtable WHERE tempdeleteflag = 1;

Finally clean up the temporary column:

ALTER TABLE origtable DROP tempdeleteflag;

If you created temptable using TEMPORARY it will go away when your session ends, otherwise drop it now.

As an alternative, you can do the logic the other way here:
default tempdeleteflag to 0, select rows you wish to delete into temptable, then set tempdeleteflag to 1 on rows common to the two tables.


Posted by David Buzz on May 6 2004 3:53am[Delete] [Edit]

In reference to the first user comment on this page, I have 2 comments:
1) GREAT CONCEPT - I use it a lot now!
2) You actually made a typo, and if done exactly as you describe you will delete all the rows you wanted to keep, and keep those you wanted to delete!.

You say to create a "temptable" and fill it with the rows you wish to delete... this should say "fill it with the rows you wish to KEEP", alternatively the following SQL DELETE statement should be inverted so that you delete those "where tempdeleteflag = 0";

Why? Well, you are defaulting the origtable to have a value of 1 in the tempdeleteflag column, and then setting it to 0 for those that you want to DELETE. (0 means delete) obviously then the DELETE statement should delete those that are 0. Alternatively, if you set it to 0 for those that you want to KEEP (0 means keep, then 1 means DELETE) then you DELETE those that are set to 1. Be very careful to get it right.




Posted by anonymous on August 27 2004 3:45pm[Delete] [Edit]

About the above comments, if one can

INSERT INTO temptable SELECT ... FROM origtable WHERE ...

to choose exactly what to DELETE, why not simply

DELETE FROM origtable WHERE <same where clause as above>

and be done with it?

Posted by Are you mortal? Then prepare to die. on November 24 2004 3:26pm[Delete] [Edit]

About the last comment, presumably the select involves a join which a straight delete could not.

A final method is to use a left join to remove those rows you dont want from the results set and insert that results set into a new table and swap that table witht the table you wanted to delete from...

I hate this method so much I am hear reading how to do this....


DELETE FROM
WHERE
ROW(SUNID1,SUNID2) IN (
SELECT
SUNID1,SUNID2
FROM
toExcludeFromInterfaceBreakdown
);


Yay for subselects!

Add your own comment.