<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="subquery-restrictions">
      </a>
      15.2.15.12 Restrictions on Subqueries
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045179608400">
  </a>
  <a class="indexterm" name="idm46045179606944">
  </a>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      In general, you cannot modify a table and select from the
            same table in a subquery. For example, this limitation
            applies to statements of the following forms:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa53312231"><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> t <span class="token keyword">WHERE</span> <span class="token punctuation">.</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 punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">FROM</span> t <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">UPDATE</span> t <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">WHERE</span> col <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">FROM</span> t <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
{<span class="token keyword">INSERT</span><span class="token operator">|</span><span class="token keyword">REPLACE</span>} <span class="token keyword">INTO</span> t <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">FROM</span> t <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      Exception: The preceding prohibition does not apply if for
            the modified table you are using a derived table and that
            derived table is materialized rather than merged into the
            outer query. (See
      <a class="xref" href="derived-table-optimization.html" title="10.2.2.4 Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization">
       Section 10.2.2.4, “Optimizing Derived Tables, View References, and Common Table Expressions
          with Merging or Materialization”
      </a>
      .) Example:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa1400521"><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">UPDATE</span> t <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">WHERE</span> col <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">FROM</span> t<span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> dt <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      Here the result from the derived table is materialized as a
            temporary table, so the relevant rows in
      <code class="literal">
       t
      </code>
      have already been selected by the time
            the update to
      <code class="literal">
       t
      </code>
      takes place.
     </p>
     <p>
      In general, you may be able to influence the optimizer to
            materialize a derived table by adding a
      <a class="link" href="optimizer-hints.html#optimizer-hints-table-level" title="Table-Level Optimizer Hints">
       <code class="literal">
        NO_MERGE
       </code>
      </a>
      optimizer hint.
            See
      <a class="xref" href="optimizer-hints.html" title="10.9.3 Optimizer Hints">
       Section 10.9.3, “Optimizer Hints”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Row comparison operations are only partially supported:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         For
         <code class="literal">
          <em class="replaceable">
           <code>
            expr
           </code>
          </em>
          [NOT] IN
          <em class="replaceable">
           <code>
            subquery
           </code>
          </em>
         </code>
         ,
         <em class="replaceable">
          <code>
           expr
          </code>
         </em>
         can be an
         <em class="replaceable">
          <code>
           n
          </code>
         </em>
         -tuple (specified using row
                constructor syntax) and the subquery can return rows of
         <em class="replaceable">
          <code>
           n
          </code>
         </em>
         -tuples. The permitted
                syntax is therefore more specifically expressed as
         <code class="literal">
          <em class="replaceable">
           <code>
            row_constructor
           </code>
          </em>
          [NOT] IN
          <em class="replaceable">
           <code>
            table_subquery
           </code>
          </em>
         </code>
        </p>
       </li>
       <li class="listitem">
        <p>
         For
         <code class="literal">
          <em class="replaceable">
           <code>
            expr
           </code>
          </em>
          <em class="replaceable">
           <code>
            op
           </code>
          </em>
          {ALL|ANY|SOME}
          <em class="replaceable">
           <code>
            subquery
           </code>
          </em>
         </code>
         ,
         <em class="replaceable">
          <code>
           expr
          </code>
         </em>
         must be a scalar value
                and the subquery must be a column subquery; it cannot
                return multiple-column rows.
        </p>
       </li>
      </ul>
     </div>
     <p>
      In other words, for a subquery that returns rows of
      <em class="replaceable">
       <code>
        n
       </code>
      </em>
      -tuples, this is supported:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa58957960"><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 punctuation">(</span><em class="replaceable">expr_1</em><span class="token punctuation">,</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">,</span> <em class="replaceable">expr_n</em><span class="token punctuation">)</span> <span class="token punctuation">[</span><span class="token operator">NOT</span><span class="token punctuation">]</span> <span class="token keyword">IN</span> <em class="replaceable">table_subquery</em></code></pre>
     </div>
     <p>
      But this is not supported:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa4070993"><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 punctuation">(</span><em class="replaceable">expr_1</em><span class="token punctuation">,</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">,</span> <em class="replaceable">expr_n</em><span class="token punctuation">)</span> <em class="replaceable">op</em> {<span class="token keyword">ALL</span><span class="token operator">|</span><span class="token keyword">ANY</span><span class="token operator">|</span><span class="token keyword">SOME</span>} <em class="replaceable">subquery</em></code></pre>
     </div>
     <p>
      The reason for supporting row comparisons for
      <code class="literal">
       IN
      </code>
      but not for the others is that
      <code class="literal">
       IN
      </code>
      is implemented by rewriting it as a
            sequence of
      <a class="link" href="comparison-operators.html#operator_equal">
       <code class="literal">
        =
       </code>
      </a>
      comparisons and
      <a class="link" href="logical-operators.html#operator_and">
       <code class="literal">
        AND
       </code>
      </a>
      operations.
            This approach cannot be used for
      <code class="literal">
       ALL
      </code>
      ,
      <code class="literal">
       ANY
      </code>
      , or
      <code class="literal">
       SOME
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      MySQL does not support
      <code class="literal">
       LIMIT
      </code>
      in
            subqueries for certain subquery operators:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa93192166"><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 prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1
       <span class="token keyword">WHERE</span> s1 <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> s2 <span class="token keyword">FROM</span> t2 <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> s1 <span class="token keyword">LIMIT</span> <span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">ERROR 1235 (42000)<span class="token punctuation">:</span> This version of MySQL doesn't yet support
 'LIMIT &amp; IN/ALL/ANY/SOME subquery'</span></code></pre>
     </div>
     <p>
      See
      <a class="xref" href="subquery-errors.html" title="15.2.15.10 Subquery Errors">
       Section 15.2.15.10, “Subquery Errors”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      MySQL permits a subquery to refer to a stored function that
            has data-modifying side effects such as inserting rows into
            a table. For example, if
      <code class="literal">
       f()
      </code>
      inserts
            rows, the following query can modify data:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa9320760"><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 punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">WHERE</span> x <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> f<span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      This behavior is an extension to the SQL standard. In MySQL,
            it can produce nondeterministic results because
      <code class="literal">
       f()
      </code>
      might be executed a different number
            of times for different executions of a given query depending
            on how the optimizer chooses to handle it.
     </p>
     <p>
      For statement-based or mixed-format replication, one
            implication of this indeterminism is that such a query can
            produce different results on the source and its replicas.
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
