(mysql.info.gz) Subqueries
Info Catalog
(mysql.info.gz) SELECT
(mysql.info.gz) Data Manipulation
(mysql.info.gz) TRUNCATE
13.1.8 Subquery Syntax
----------------------
A subquery is a `SELECT' statement inside 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. Rewriting
subqueries.
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_ in the outer query, and in fact it's
possible to nest subqueries within other subqueries, to a great depth.
A subquery must always appear within parentheses.
The main advantages of subqueries are:
* They allow queries that are _structured_ so that it's 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, readable. 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's outer statement can be any one of: `SELECT',
`INSERT', `UPDATE', `DELETE', `SET', or `DO'.
* 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 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'.
Menu
* Scalar subqueries The Subquery as Scalar Operand
* Comparisons using subqueries Comparisons Using Subqueries
* ANY IN SOME subqueries Subqueries with `ANY', `IN', and `SOME'
* ALL subqueries Subqueries with `ALL'
* Row subqueries Row Subqueries
* EXISTS and NOT EXISTS subqueries `EXISTS' and `NOT EXISTS'
* Correlated subqueries Correlated Subqueries
* Unnamed views Subqueries in the `FROM' clause
* Subquery errors Subquery Errors
* Optimizing subqueries Optimizing Subqueries
* Rewriting subqueries Rewriting Subqueries as Joins for Earlier MySQL Versions
Info Catalog
(mysql.info.gz) SELECT
(mysql.info.gz) Data Manipulation
(mysql.info.gz) TRUNCATE
automatically generated byinfo2html