DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH PRINT BOOK
 

(mysql.info) subqueries

Info Catalog (mysql.info) select (mysql.info) data-manipulation (mysql.info) truncate
 
 13.2.8 Subquery Syntax
 ----------------------
 

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
 
 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.
 
 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, 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 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', `UPDATE', and (because subqueries can be
 used in the `SET' clause) `LOAD DATA INFILE'.
 
 A more comprehensive discussion of restrictions on subquery use,
 including performance issues for certain forms of subquery syntax, is
 given in  subquery-restrictions.
 
Info Catalog (mysql.info) select (mysql.info) data-manipulation (mysql.info) truncate
automatically generated byinfo2html