<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="correlated-subqueries">
      </a>
      15.2.15.7 Correlated Subqueries
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045179843952">
  </a>
  <a class="indexterm" name="idm46045179842880">
  </a>
  <p>
   A
   <span class="emphasis">
    <em>
     correlated subquery
    </em>
   </span>
   is a subquery that
        contains a reference to a table that also appears in the outer
        query. For example:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa87575520"><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 keyword">ANY</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> column1 <span class="token keyword">FROM</span> t2
                       <span class="token keyword">WHERE</span> t2<span class="token punctuation">.</span>column2 <span class="token operator">=</span> t1<span class="token punctuation">.</span>column2<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Notice that the subquery contains a reference to a column of
   <code class="literal">
    t1
   </code>
   , even though the subquery's
   <code class="literal">
    FROM
   </code>
   clause does not mention a table
   <code class="literal">
    t1
   </code>
   . So, MySQL looks outside the subquery, and
        finds
   <code class="literal">
    t1
   </code>
   in the outer query.
  </p>
  <p>
   Suppose that table
   <code class="literal">
    t1
   </code>
   contains a row where
   <code class="literal">
    column1 = 5
   </code>
   and
   <code class="literal">
    column2 =
        6
   </code>
   ; meanwhile, table
   <code class="literal">
    t2
   </code>
   contains a
        row where
   <code class="literal">
    column1 = 5
   </code>
   and
   <code class="literal">
    column2 =
        7
   </code>
   . The simple expression
   <code class="literal">
    ... WHERE column1 =
        ANY (SELECT column1 FROM t2)
   </code>
   would be
   <code class="literal">
    TRUE
   </code>
   , but in this example, the
   <code class="literal">
    WHERE
   </code>
   clause within the subquery is
   <code class="literal">
    FALSE
   </code>
   (because
   <code class="literal">
    (5,6)
   </code>
   is
        not equal to
   <code class="literal">
    (5,7)
   </code>
   ), so the expression as a
        whole is
   <code class="literal">
    FALSE
   </code>
   .
  </p>
  <p>
   <span class="bold">
    <strong>
     Scoping rule:
    </strong>
   </span>
   MySQL evaluates
        from inside to outside. For example:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa84418658"><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> column1 <span class="token keyword">FROM</span> t1 <span class="token keyword">AS</span> x
  <span class="token keyword">WHERE</span> x<span class="token punctuation">.</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 keyword">AS</span> x
    <span class="token keyword">WHERE</span> x<span class="token punctuation">.</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> t3
      <span class="token keyword">WHERE</span> x<span class="token punctuation">.</span>column2 <span class="token operator">=</span> t3<span class="token punctuation">.</span>column1<span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   In this statement,
   <code class="literal">
    x.column2
   </code>
   must be a column
        in table
   <code class="literal">
    t2
   </code>
   because
   <code class="literal">
    SELECT column1
        FROM t2 AS x ...
   </code>
   renames
   <code class="literal">
    t2
   </code>
   . It is
        not a column in table
   <code class="literal">
    t1
   </code>
   because
   <code class="literal">
    SELECT column1 FROM t1 ...
   </code>
   is an outer query
        that is
   <span class="emphasis">
    <em>
     farther out
    </em>
   </span>
   .
  </p>
  <p>
   The optimizer can transform a correlated scalar subquery to a
        derived table when the
   <a class="link" href="switchable-optimizations.html#optflag_subquery-to-derived">
    <code class="literal">
     subquery_to_derived
    </code>
   </a>
   flag of
        the
   <a class="link" href="server-system-variables.html#sysvar_optimizer_switch">
    <code class="literal">
     optimizer_switch
    </code>
   </a>
   variable
        is enabled. Consider the query shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa8871677"><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> <span class="token punctuation">(</span> <span class="token keyword">SELECT</span> a <span class="token keyword">FROM</span> t2 
              <span class="token keyword">WHERE</span> t2<span class="token punctuation">.</span>a<span class="token operator">=</span>t1<span class="token punctuation">.</span>a <span class="token punctuation">)</span> <span class="token operator">&gt;</span> <span class="token number">0</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   To avoid materializing several times for a given derived table,
        we can instead materialize—once—a derived table
        which adds a grouping on the join column from the table
        referenced in the inner query (
   <code class="literal">
    t2.a
   </code>
   ) and then
        an outer join on the lifted predicate (
   <code class="literal">
    t1.a =
        derived.a
   </code>
   ) in order to select the correct group to
        match up with the outer row. (If the subquery already has an
        explicit grouping, the extra grouping is added to the end of the
        grouping list.) The query previously shown can thus be rewritten
        like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa61085932"><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> t1<span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">FROM</span> t1 
    <span class="token keyword">LEFT</span> <span class="token keyword">OUTER</span> <span class="token keyword">JOIN</span>
        <span class="token punctuation">(</span><span class="token keyword">SELECT</span> a<span class="token punctuation">,</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 keyword">AS</span> ct <span class="token keyword">FROM</span> t2 <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> a<span class="token punctuation">)</span> <span class="token keyword">AS</span> derived
    <span class="token keyword">ON</span>  t1<span class="token punctuation">.</span>a <span class="token operator">=</span> derived<span class="token punctuation">.</span>a 
        <span class="token operator">AND</span> 
        REJECT_IF<span class="token punctuation">(</span>
            <span class="token punctuation">(</span>ct <span class="token operator">&gt;</span> <span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
            <span class="token string">"ERROR 1242 (21000): Subquery returns more than 1 row"</span>
            <span class="token punctuation">)</span>
    <span class="token keyword">WHERE</span> derived<span class="token punctuation">.</span>a <span class="token operator">&gt;</span> <span class="token number">0</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   In the rewritten query,
   <code class="literal">
    REJECT_IF()
   </code>
   represents an internal function which tests a given condition
        (here, the comparison
   <code class="literal">
    ct &gt; 1
   </code>
   ) and raises a
        given error (in this case,
   <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_subquery_no_1_row" target="_top">
    <code class="literal">
     ER_SUBQUERY_NO_1_ROW
    </code>
   </a>
   ) if the
        condition is true. This reflects the cardinality check that the
        optimizer performs as part of evaluating the
   <code class="literal">
    JOIN
   </code>
   or
   <code class="literal">
    WHERE
   </code>
   clause,
        prior to evaluating any lifted predicate, which is done only if
        the subquery does not return more than one row.
  </p>
  <p>
   This type of transformation can be performed, provided the
        following conditions are met:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The subquery can be part of a
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      list,
      <code class="literal">
       WHERE
      </code>
      condition, or
      <code class="literal">
       HAVING
      </code>
      condition, but cannot be part of a
      <a class="link" href="join.html" title="15.2.13.2 JOIN Clause">
       <code class="literal">
        JOIN
       </code>
      </a>
      condition, and cannot
            contain a
      <code class="literal">
       LIMIT
      </code>
      or
      <code class="literal">
       OFFSET
      </code>
      clause. In addition, the subquery
            cannot contain any set operations such as
      <a class="link" href="union.html" title="15.2.18 UNION Clause">
       <code class="literal">
        UNION
       </code>
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      The
      <code class="literal">
       WHERE
      </code>
      clause may contain one or more
            predicates, combined with
      <code class="literal">
       AND
      </code>
      . If the
      <code class="literal">
       WHERE
      </code>
      clause contains an
      <code class="literal">
       OR
      </code>
      clause, it cannot be transformed. At
            least one of the
      <code class="literal">
       WHERE
      </code>
      clause predicates
            must be eligible for transformation, and none of them may
            reject transformation.
     </p>
    </li>
    <li class="listitem">
     <p>
      To be eligible for transformation, a
      <code class="literal">
       WHERE
      </code>
      clause predicate must be an
            equality predicate in which each operand should be a simple
            column reference. No other predicates—including other
            comparison predicates—are eligible for transformation.
            The predicate must employ the equality operator
      <a class="link" href="comparison-operators.html#operator_equal">
       <code class="literal">
        =
       </code>
      </a>
      for making
            the comparison; the null-safe
      <a class="link" href="comparison-operators.html#operator_equal-to">
       <code class="literal">
        &lt;=&gt;
       </code>
      </a>
      operator is not supported in this context.
     </p>
    </li>
    <li class="listitem">
     <p>
      A
      <code class="literal">
       WHERE
      </code>
      clause predicate that contains
            only inner references is not eligible for transformation,
            since it can be evaluated before the grouping. A
      <code class="literal">
       WHERE
      </code>
      clause predicate that contains only
            outer references is eligible for transformation, even though
            it can be lifted up to the outer query block. This is made
            possible by adding a cardinality check without grouping in
            the derived table.
     </p>
    </li>
    <li class="listitem">
     <p>
      To be eligible, a
      <code class="literal">
       WHERE
      </code>
      clause predicate
            must have one operand that contains only inner references
            and one operand that contains only outer references. If the
            predicate is not eligible due to this rule, transformation
            of the query is rejected.
     </p>
    </li>
    <li class="listitem">
     <p>
      A correlated column can be present only in the
            subquery's
      <code class="literal">
       WHERE
      </code>
      clause (and not in
            the
      <code class="literal">
       SELECT
      </code>
      list, a
      <code class="literal">
       JOIN
      </code>
      or
      <code class="literal">
       ORDER BY
      </code>
      clause, a
      <code class="literal">
       GROUP BY
      </code>
      list, or a
      <code class="literal">
       HAVING
      </code>
      clause). Nor can there be any
            correlated column inside a derived table in the
            subquery's
      <code class="literal">
       FROM
      </code>
      list.
     </p>
    </li>
    <li class="listitem">
     <p>
      A correlated column can not be contained in an aggregate
            function's list of arguments.
     </p>
    </li>
    <li class="listitem">
     <p>
      A correlated column must be resolved in the query block
            directly containing the subquery being considered for
            transformation.
     </p>
    </li>
    <li class="listitem">
     <p>
      A correlated column cannot be present in a nested scalar
            subquery in the
      <code class="literal">
       WHERE
      </code>
      clause.
     </p>
    </li>
    <li class="listitem">
     <p>
      The subquery cannot contain any window functions, and must
            not contain any aggregate function which aggregates in a
            query block outer to the subquery. A
      <a class="link" href="aggregate-functions.html#function_count">
       <code class="literal">
        COUNT()
       </code>
      </a>
      aggregate function,
            if contained in the
      <code class="literal">
       SELECT
      </code>
      list element
            of the subquery, must be at the topmost level, and cannot be
            part of an expression.
     </p>
    </li>
   </ul>
  </div>
  <p>
   See also
   <a class="xref" href="derived-tables.html" title="15.2.15.8 Derived Tables">
    Section 15.2.15.8, “Derived Tables”
   </a>
   .
  </p>
 </div>
 <br/>
</div>
