Skip navigation links
12.2.8.1 The Subquery as Scalar Operand »
Section Navigation      [Toggle]

12.2.8. Subquery Syntax

[+/-]

12.2.8.1. The Subquery as Scalar Operand
12.2.8.2. Comparisons Using Subqueries
12.2.8.3. Subqueries with ANY, IN, and SOME
12.2.8.4. Subqueries with ALL
12.2.8.5. Row Subqueries
12.2.8.6. EXISTS and NOT EXISTS
12.2.8.7. Correlated Subqueries
12.2.8.8. Subqueries in the FROM clause
12.2.8.9. Subquery Errors
12.2.8.10. Optimizing Subqueries
12.2.8.11. Rewriting Subqueries as Joins for Earlier MySQL Versions

A subquery is a SELECT statement within another statement.

Starting with MySQL 4.1, all subquery forms and operations that the SQL standard requires are supported, as well as a few features that are MySQL-specific.

With MySQL versions prior to 4.1, it was necessary to work around or avoid the use of subqueries. In many cases, subqueries can successfully be rewritten using joins and other methods. See Section 12.2.8.11, “Rewriting Subqueries as Joins for Earlier MySQL Versions”.

Here is an example of a subquery: 

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

In this example, SELECT * FROM t1 ... is the outer query (or outer statement), and (SELECT column1 FROM t2) is the subquery. We say that the subquery is nested within the outer query, and in fact it is possible to nest subqueries within other subqueries, to a considerable depth. A subquery must always appear within parentheses.

The main advantages of subqueries are:

  • They allow queries that are structured so that it is possible to isolate each part of a statement.

  • They provide alternative ways to perform operations that would otherwise require complex joins and unions.

  • They are, in many people's opinion, more readable than complex joins or unions. Indeed, it was the innovation of subqueries that gave people the original idea of calling the early SQL “Structured Query Language.”

Here is an example statement that shows the major points about subquery syntax as specified by the SQL standard and supported in MySQL: 

DELETE FROM t1
WHERE s11 > ANY
 (SELECT COUNT(*) /* no hint */ FROM t2
  WHERE NOT EXISTS
   (SELECT * FROM t3
    WHERE ROW(5*t2.s1,77)=
     (SELECT 50,11*s1 FROM t4 UNION SELECT 50,77 FROM
      (SELECT * FROM t5) AS t5)));

A subquery can return a scalar (a single value), a single row, a single column, or a table (one or more rows of one or more columns). These are called scalar, column, row, and table subqueries. Subqueries that return a particular kind of result often can be used only in certain contexts, as described in the following sections.

There are few restrictions on the type of statements in which subqueries can be used. A subquery can contain any of the keywords or clauses that an ordinary SELECT can contain: DISTINCT, GROUP BY, ORDER BY, LIMIT, joins, index hints, UNION constructs, comments, functions, and so on.

One restriction is that a subquery's outer statement must be one of: SELECT, INSERT, UPDATE, DELETE, SET, or DO. Another restriction is that currently you cannot modify a table and select from the same table in a subquery. This applies to statements such as DELETE, INSERT, REPLACE, and UPDATE.

A more comprehensive discussion of restrictions on subquery use, including performance issues for certain forms of subquery syntax, is given in Section C.1, “Restrictions on Subqueries”.


User Comments

Posted by Are you mortal? Then prepare to die. on January 5 2005 11:14pm[Delete] [Edit]


Ever wanted to turn an AUTO_INCRIMENT primary key into one of those 'rolling ID' columns? i.e. the type which changes back to ID = 1 when some other part of your (new) PK changes... Use a subquery!

Suppose you have this

TABLE t1...

AUTO_INCR_PK <-> X
1 <-> A
2 <-> A
3 <-> A
4 <-> B
5 <-> B
6 <-> B
7 <-> C
8 <-> C
9 <-> D

Try this cool sub-query!

CREATE TABLE t2 ( ID, X, PK(ID,X) );

INSERT INTO t2;
SELECT
a.X
a.AUTO_INCR_PK -
b.FIRST_KEY_IN_SERIES AS ID
FROM
t1
INNER JOIN
(
SELECT
X,
MIN(AUTO_INCR_PK) AS FIRST_KEY_IN_SERIES
FROM
t1
GROUP BY
X
) AS b
USING
(X)
;

Which gives you

TABLE t2 ...

ID <-> X
1 <-> A
2 <-> A
3 <-> A
1 <-> B
2 <-> B
3 <-> B
1 <-> C
2 <-> C
1 <-> D

Cool eh?

Posted by Tayfun Duran on September 10 2005 12:22pm[Delete] [Edit]

If you can't use subquery, you can use this;

$sec1 = mysql_query("SELECT foto FROM profoto WHERE proje=$id");
if ($kyt1 = mysql_fetch_array($sec1)) {$dizi = $kyt1["foto"];} else {$dizi="0";}
while ($kyt1 = mysql_fetch_array($sec1)) {
$dizi = $dizi . "," . $kyt1["foto"];
}
mysql_free_result($sec1);
$sec = mysql_query("SELECT foto.id, foto.dosya, foto.baslik FROM foto WHERE id NOT IN (" . $dizi . ") LIMIT $baslangic, $sayfalama");

First, you create a selection and then you use it in your real selection. This is a kind of subquery :)

Posted by Matthew Walker on September 20 2005 7:39pm[Delete] [Edit]

Actually, if you want ID columns like that, it's even easier. :)

CREATE TABLE `example` (
`column1` BIGINT UNSIGNED NOT NULL ,
`column2` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,
`data` VARCHAR( 255 ) NOT NULL ,
PRIMARY KEY ( `column1` , `column2` )
);

INSERT INTO `example` ( `column1` , `column2` , `data` )
VALUES ('1', '', 'foo'), ('1', '', 'bar'), ('2', '', 'baz');

column1 column2 data
1 1 foo
1 2 bar
2 1 baz

Posted by Marnen Laibow-Koser on February 15 2006 10:22pm[Delete] [Edit]

Unfortunately, AUTO_INCREMENT doesn't work that way with all storage engines. InnoDB just keeps incrementing regardless, even if the field is part of a composite key as in your example. See the page on AUTO_INCREMENT for more info.

Posted by Svavar Lúthersson on June 4 2006 5:28pm[Delete] [Edit]

This order of things also works for count functions. For example:

SELECT *,(SELECT COUNT(*) FROM table2 WHERE table2.field1 = table1.id) AS count FROM table1 WHERE table1.field1 = 'value'

This command will enable you to count fields in table2 based on a column value in table1 and label the result as "count". The value in table1.field1 can be any valid field type.

Posted by ch4dwick m. on May 21 2007 5:02am[Delete] [Edit]

Here's simple insert query I came up with that serves 3 functions in my PHP applications:
1.) retrieves data from the select query
2.) Inserts that row into another table (for record keeping purposes such as purchase orders from shopping carts)
3.) validates the input (using mysql_affected_rows()) and will return 0 if the select failed and will not insert at all.

INSERT INTO table2 (field1, field2, field3, field4) (SELECT 'value1 from user input', field1, field2, field3 from table1)

The 4 fields in table2 will be populated by the 4 fields (including the string) returned by the SELECT sub-query respectively.

I know this MIGHT raise issues with speed of queries but it's better than writing long lines of PHP code that does those 3 things - even with a framework! I can just use the mysql_affected_rows() after that query to see if everything went fine.

NOTE: Make sure the number of fields in the SELECT query is EXACTLY the same number of fields you are about to insert.

Posted by Andy Leon on June 23 2009 4:06pm[Delete] [Edit]

It mentions here that you cannot select from and modify the same table as part of your subquery. There is a workaround listed on the following page:

http://dev.mysql.com/doc/refman/5.1/en/subquery-restrictions.html

that shows you how to use a temporary table. But you can also create a View based on a table, use that for the SELECT statement and then use the regular table name for the UPDATE / DELETE statement.

Posted by Christian Berrigan on March 14 2010 7:32pm[Delete] [Edit]

Updating a table using a subquery. This uses an example of a table of people, and a separate table of votes those people have received. After the votes table has been populated with new votes, the total vote count of each of the people is calculated and updated with one query.

UPDATE people,
(SELECT count(*) as votecount, person_id
FROM votes GROUP BY person_id) as tally
SET people.votecount = tally.votecount
WHERE people.person_id = tally.person_id

Add your own comment.