<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="subqueries">
      </a>
      15.2.15 Subqueries
     </h3>
    </div>
   </div>
  </div>
  <div class="toc">
   <dl class="toc">
    <dt>
     <span class="section">
      <a href="scalar-subqueries.html">
       15.2.15.1 The Subquery as Scalar Operand
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="comparisons-using-subqueries.html">
       15.2.15.2 Comparisons Using Subqueries
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="any-in-some-subqueries.html">
       15.2.15.3 Subqueries with ANY, IN, or SOME
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="all-subqueries.html">
       15.2.15.4 Subqueries with ALL
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="row-subqueries.html">
       15.2.15.5 Row Subqueries
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="exists-and-not-exists-subqueries.html">
       15.2.15.6 Subqueries with EXISTS or NOT EXISTS
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="correlated-subqueries.html">
       15.2.15.7 Correlated Subqueries
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="derived-tables.html">
       15.2.15.8 Derived Tables
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="lateral-derived-tables.html">
       15.2.15.9 Lateral Derived Tables
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="subquery-errors.html">
       15.2.15.10 Subquery Errors
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="optimizing-subqueries.html">
       15.2.15.11 Optimizing Subqueries
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="subquery-restrictions.html">
       15.2.15.12 Restrictions on Subqueries
      </a>
     </span>
    </dt>
   </dl>
  </div>
  <a class="indexterm" name="idm46045180116960">
  </a>
  <a class="indexterm" name="idm46045180115888">
  </a>
  <a class="indexterm" name="idm46045180114816">
  </a>
  <a class="indexterm" name="idm46045180113328">
  </a>
  <p>
   A subquery is a
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   statement
      within another statement.
  </p>
  <p>
   All subquery forms and operations that the SQL standard requires
      are supported, as well as a few features that are MySQL-specific.
  </p>
  <p>
   Here is an example of a subquery:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa95095469"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> column1 <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> column1 <span class="token keyword">FROM</span> t2<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   In this example,
   <code class="literal">
    SELECT * FROM t1 ...
   </code>
   is the
   <span class="emphasis">
    <em>
     outer query
    </em>
   </span>
   (or
   <span class="emphasis">
    <em>
     outer
      statement
    </em>
   </span>
   ), and
   <code class="literal">
    (SELECT column1 FROM
      t2)
   </code>
   is the
   <span class="emphasis">
    <em>
     subquery
    </em>
   </span>
   . We say that
      the subquery is
   <span class="emphasis">
    <em>
     nested
    </em>
   </span>
   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.
  </p>
  <p>
   The main advantages of subqueries are:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      They allow queries that are
      <span class="emphasis">
       <em>
        structured
       </em>
      </span>
      so
          that it is possible to isolate each part of a statement.
     </p>
    </li>
    <li class="listitem">
     <p>
      They provide alternative ways to perform operations that would
          otherwise require complex joins and unions.
     </p>
    </li>
    <li class="listitem">
     <p>
      Many people find subqueries 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
      <span class="quote">
       “
       <span class="quote">
        Structured Query Language.
       </span>
       ”
      </span>
     </p>
    </li>
   </ul>
  </div>
  <p>
   Here is an example statement that shows the major points about
      subquery syntax as specified by the SQL standard and supported in
      MySQL:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa48806874"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">DELETE</span> <span class="token keyword">FROM</span> t1
<span class="token keyword">WHERE</span> s11 <span class="token operator">&gt;</span> <span class="token keyword">ANY</span>
 <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token comment" spellcheck="true">/* no hint */</span> <span class="token keyword">FROM</span> t2
  <span class="token keyword">WHERE</span> <span class="token operator">NOT</span> <span class="token keyword">EXISTS</span>
   <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t3
    <span class="token keyword">WHERE</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">5</span><span class="token operator">*</span>t2<span class="token punctuation">.</span>s1<span class="token punctuation">,</span><span class="token number">77</span><span class="token punctuation">)</span><span class="token operator">=</span>
     <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token number">50</span><span class="token punctuation">,</span><span class="token number">11</span><span class="token operator">*</span>s1 <span class="token keyword">FROM</span> t4 <span class="token keyword">UNION</span> <span class="token keyword">SELECT</span> <span class="token number">50</span><span class="token punctuation">,</span><span class="token number">77</span> <span class="token keyword">FROM</span>
      <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t5<span class="token punctuation">)</span> <span class="token keyword">AS</span> t5<span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   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.
  </p>
  <p>
   There are few restrictions on the type of statements in which
      subqueries can be used. A subquery can contain many of the
      keywords or clauses that an ordinary
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   can contain:
   <code class="literal">
    DISTINCT
   </code>
   ,
   <code class="literal">
    GROUP BY
   </code>
   ,
   <code class="literal">
    ORDER BY
   </code>
   ,
   <code class="literal">
    LIMIT
   </code>
   , joins,
      index hints,
   <a class="link" href="union.html" title="15.2.18 UNION Clause">
    <code class="literal">
     UNION
    </code>
   </a>
   constructs,
      comments, functions, and so on.
  </p>
  <p>
   <a class="link" href="table.html" title="15.2.16 TABLE Statement">
    <code class="literal">
     TABLE
    </code>
   </a>
   and
   <a class="link" href="values.html" title="15.2.19 VALUES Statement">
    <code class="literal">
     VALUES
    </code>
   </a>
   statements can be used in
      subqueries. Subqueries using
   <code class="literal">
    VALUES
   </code>
   are
      generally more verbose versions of subqueries that can be
      rewritten more compactly using set notation, or with
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   or
   <code class="literal">
    TABLE
   </code>
   syntax; assuming that table
   <code class="literal">
    ts
   </code>
   is created using
      the statement
   <a class="link" href="create-table-select.html" title="15.1.20.4 CREATE TABLE ... SELECT Statement">
    <code class="literal">
     CREATE TABLE
      ts VALUES ROW(2), ROW(4), ROW(6)
    </code>
   </a>
   , the statements shown
      here are all equivalent:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa78820006"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> tt
    <span class="token keyword">WHERE</span> b <span class="token operator">&gt;</span> <span class="token keyword">ANY</span> <span class="token punctuation">(</span><span class="token keyword">VALUES</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">6</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> tt
    <span class="token keyword">WHERE</span> b <span class="token operator">&gt;</span> <span class="token keyword">ANY</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> ts<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> tt
    <span class="token keyword">WHERE</span> b <span class="token operator">&gt;</span> <span class="token keyword">ANY</span> <span class="token punctuation">(</span><span class="token keyword">TABLE</span> ts<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Examples of
   <a class="link" href="table.html" title="15.2.16 TABLE Statement">
    <code class="literal">
     TABLE
    </code>
   </a>
   subqueries are
      shown in the sections that follow.
  </p>
  <p>
   A subquery's outer statement can be any one of:
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   ,
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT
    </code>
   </a>
   ,
   <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
    <code class="literal">
     UPDATE
    </code>
   </a>
   ,
   <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
    <code class="literal">
     DELETE
    </code>
   </a>
   ,
   <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
    <code class="literal">
     SET
    </code>
   </a>
   , or
   <a class="link" href="do.html" title="15.2.3 DO Statement">
    <code class="literal">
     DO
    </code>
   </a>
   .
  </p>
  <p>
   For information about how the optimizer handles subqueries, see
   <a class="xref" href="subquery-optimization.html" title="10.2.2 Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions">
    Section 10.2.2, “Optimizing Subqueries, Derived Tables, View References, and Common Table
        Expressions”
   </a>
   . For a discussion of
      restrictions on subquery use, including performance issues for
      certain forms of subquery syntax, see
   <a class="xref" href="subquery-restrictions.html" title="15.2.15.12 Restrictions on Subqueries">
    Section 15.2.15.12, “Restrictions on Subqueries”
   </a>
   .
  </p>
 </div>
 <br/>
</div>
