(mysql.info.gz) Subqueries

 
 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'.

 
* 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

(mysql.info.gz) Subqueries

Menu