<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="subquery-optimization-with-exists">
      </a>
      10.2.2.3 Optimizing Subqueries with the EXISTS Strategy
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045228149232">
  </a>
  <a class="indexterm" name="idm46045228147744">
  </a>
  <p>
   Certain optimizations are applicable to comparisons that use
          the
   <code class="literal">
    IN
   </code>
   (or
   <code class="literal">
    =ANY
   </code>
   )
          operator to test subquery results. This section discusses
          these optimizations, particularly with regard to the
          challenges that
   <code class="literal">
    NULL
   </code>
   values present. The
          last part of the discussion suggests how you can help the
          optimizer.
  </p>
  <p>
   Consider the following subquery comparison:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa88106484"><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"><em class="replaceable">outer_expr</em> <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <em class="replaceable">inner_expr</em> <span class="token keyword">FROM</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">WHERE</span> <em class="replaceable">subquery_where</em><span class="token punctuation">)</span></code></pre>
  </div>
  <p>
   MySQL evaluates queries
   <span class="quote">
    “
    <span class="quote">
     from outside to inside.
    </span>
    ”
   </span>
   That is, it first obtains the value of the outer expression
   <em class="replaceable">
    <code>
     outer_expr
    </code>
   </em>
   , and then runs the
          subquery and captures the rows that it produces.
  </p>
  <p>
   A very useful optimization is to
   <span class="quote">
    “
    <span class="quote">
     inform
    </span>
    ”
   </span>
   the
          subquery that the only rows of interest are those where the
          inner expression
   <em class="replaceable">
    <code>
     inner_expr
    </code>
   </em>
   is
          equal to
   <em class="replaceable">
    <code>
     outer_expr
    </code>
   </em>
   . This is done
          by pushing down an appropriate equality into the
          subquery's
   <code class="literal">
    WHERE
   </code>
   clause to make it
          more restrictive. The converted comparison looks like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa56355942"><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">EXISTS</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token number">1</span> <span class="token keyword">FROM</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">WHERE</span> <em class="replaceable">subquery_where</em> <span class="token operator">AND</span> <em class="replaceable">outer_expr</em><span class="token operator">=</span><em class="replaceable">inner_expr</em><span class="token punctuation">)</span></code></pre>
  </div>
  <p>
   After the conversion, MySQL can use the pushed-down equality
          to limit the number of rows it must examine to evaluate the
          subquery.
  </p>
  <p>
   More generally, a comparison of
   <em class="replaceable">
    <code>
     N
    </code>
   </em>
   values to a subquery that returns
   <em class="replaceable">
    <code>
     N
    </code>
   </em>
   -value rows is subject to the same
          conversion. If
   <em class="replaceable">
    <code>
     oe_i
    </code>
   </em>
   and
   <em class="replaceable">
    <code>
     ie_i
    </code>
   </em>
   represent corresponding outer
          and inner expression values, this subquery comparison:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa87773366"><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">oe_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">oe_N</em><span class="token punctuation">)</span> <span class="token keyword">IN</span>
  <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <em class="replaceable">ie_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">ie_N</em> <span class="token keyword">FROM</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">WHERE</span> <em class="replaceable">subquery_where</em><span class="token punctuation">)</span></code></pre>
  </div>
  <p>
   Becomes:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa12457591"><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">EXISTS</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token number">1</span> <span class="token keyword">FROM</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">WHERE</span> <em class="replaceable">subquery_where</em>
                          <span class="token operator">AND</span> <em class="replaceable">oe_1</em> <span class="token operator">=</span> <em class="replaceable">ie_1</em>
                          <span class="token operator">AND</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
                          <span class="token operator">AND</span> <em class="replaceable">oe_N</em> <span class="token operator">=</span> <em class="replaceable">ie_N</em><span class="token punctuation">)</span></code></pre>
  </div>
  <p>
   For simplicity, the following discussion assumes a single pair
          of outer and inner expression values.
  </p>
  <p>
   The
   <span class="quote">
    “
    <span class="quote">
     pushdown
    </span>
    ”
   </span>
   strategy just described works if
          either of these conditions is true:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <em class="replaceable">
       <code>
        outer_expr
       </code>
      </em>
      and
      <em class="replaceable">
       <code>
        inner_expr
       </code>
      </em>
      cannot be
      <code class="literal">
       NULL
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      You need not distinguish
      <code class="literal">
       NULL
      </code>
      from
      <code class="literal">
       FALSE
      </code>
      subquery results. If the subquery
              is a part of an
      <a class="link" href="logical-operators.html#operator_or">
       <code class="literal">
        OR
       </code>
      </a>
      or
      <a class="link" href="logical-operators.html#operator_and">
       <code class="literal">
        AND
       </code>
      </a>
      expression in the
      <code class="literal">
       WHERE
      </code>
      clause, MySQL assumes that you do
              not care. Another instance where the optimizer notices
              that
      <code class="literal">
       NULL
      </code>
      and
      <code class="literal">
       FALSE
      </code>
      subquery results need not be distinguished is this
              construct:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa85538461"><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><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">WHERE</span> <em class="replaceable">outer_expr</em> <span class="token keyword">IN</span> <span class="token punctuation">(</span><em class="replaceable">subquery</em><span class="token punctuation">)</span></code></pre>
     </div>
     <p>
      In this case, the
      <code class="literal">
       WHERE
      </code>
      clause rejects
              the row whether
      <code class="literal">
       IN
              (
       <em class="replaceable">
        <code>
         subquery
        </code>
       </em>
       )
      </code>
      returns
      <code class="literal">
       NULL
      </code>
      or
      <code class="literal">
       FALSE
      </code>
      .
     </p>
    </li>
   </ul>
  </div>
  <p>
   Suppose that
   <em class="replaceable">
    <code>
     outer_expr
    </code>
   </em>
   is known to
          be a non-
   <code class="literal">
    NULL
   </code>
   value but the subquery does
          not produce a row such that
   <em class="replaceable">
    <code>
     outer_expr
    </code>
   </em>
   =
   <em class="replaceable">
    <code>
     inner_expr
    </code>
   </em>
   . Then
   <code class="literal">
    <em class="replaceable">
     <code>
      outer_expr
     </code>
    </em>
    IN (SELECT
          ...)
   </code>
   evaluates as follows:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       NULL
      </code>
      , if the
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      produces any row
              where
      <em class="replaceable">
       <code>
        inner_expr
       </code>
      </em>
      is
      <code class="literal">
       NULL
      </code>
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       FALSE
      </code>
      , if the
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      produces only
              non-
      <code class="literal">
       NULL
      </code>
      values or produces nothing
     </p>
    </li>
   </ul>
  </div>
  <p>
   In this situation, the approach of looking for rows with
   <code class="literal">
    <em class="replaceable">
     <code>
      outer_expr
     </code>
    </em>
    =
    <em class="replaceable">
     <code>
      inner_expr
     </code>
    </em>
   </code>
   is no longer
          valid. It is necessary to look for such rows, but if none are
          found, also look for rows where
   <em class="replaceable">
    <code>
     inner_expr
    </code>
   </em>
   is
   <code class="literal">
    NULL
   </code>
   . Roughly speaking, the subquery can be
          converted to something like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa63572651"><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">EXISTS</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token number">1</span> <span class="token keyword">FROM</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">WHERE</span> <em class="replaceable">subquery_where</em> <span class="token operator">AND</span>
        <span class="token punctuation">(</span><em class="replaceable">outer_expr</em><span class="token operator">=</span><em class="replaceable">inner_expr</em> <span class="token operator">OR</span> <em class="replaceable">inner_expr</em> <span class="token operator">IS</span> <span class="token boolean">NULL</span><span class="token punctuation">)</span><span class="token punctuation">)</span></code></pre>
  </div>
  <p>
   The need to evaluate the extra
   <a class="link" href="comparison-operators.html#operator_is-null">
    <code class="literal">
     IS
          NULL
    </code>
   </a>
   condition is why MySQL has the
   <a class="link" href="explain-output.html#jointype_ref_or_null">
    <code class="literal">
     ref_or_null
    </code>
   </a>
   access method:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa7376903"><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">EXPLAIN</span>
       <span class="token keyword">SELECT</span> <em class="replaceable">outer_expr</em> <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> t2<span class="token punctuation">.</span>maybe_null_key
                             <span class="token keyword">FROM</span> t2<span class="token punctuation">,</span> t3 <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">FROM</span> t1<span class="token punctuation">;</span>
<span class="token output"><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><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><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><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> 1. row <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><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><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><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>
           id<span class="token punctuation">:</span> 1
  select_type<span class="token punctuation">:</span> PRIMARY
        table<span class="token punctuation">:</span> t1
...
<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><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><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><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> 2. row <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><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><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><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>
           id<span class="token punctuation">:</span> 2
  select_type<span class="token punctuation">:</span> DEPENDENT SUBQUERY
        table<span class="token punctuation">:</span> t2
         type<span class="token punctuation">:</span> ref_or_null
possible_keys<span class="token punctuation">:</span> maybe_null_key
          key<span class="token punctuation">:</span> maybe_null_key
      key_len<span class="token punctuation">:</span> 5
          ref<span class="token punctuation">:</span> func
         rows<span class="token punctuation">:</span> 2
        Extra<span class="token punctuation">:</span> Using where; Using index
...</span></code></pre>
  </div>
  <p>
   The
   <a class="link" href="explain-output.html#jointype_unique_subquery">
    <code class="literal">
     unique_subquery
    </code>
   </a>
   and
   <a class="link" href="explain-output.html#jointype_index_subquery">
    <code class="literal">
     index_subquery
    </code>
   </a>
   subquery-specific access methods also have
   <span class="quote">
    “
    <span class="quote">
     or
     <code class="literal">
      NULL
     </code>
    </span>
    ”
   </span>
   variants.
  </p>
  <p>
   The additional
   <code class="literal">
    OR ... IS NULL
   </code>
   condition
          makes query execution slightly more complicated (and some
          optimizations within the subquery become inapplicable), but
          generally this is tolerable.
  </p>
  <p>
   The situation is much worse when
   <em class="replaceable">
    <code>
     outer_expr
    </code>
   </em>
   can be
   <code class="literal">
    NULL
   </code>
   . According to the SQL interpretation
          of
   <code class="literal">
    NULL
   </code>
   as
   <span class="quote">
    “
    <span class="quote">
     unknown value,
    </span>
    ”
   </span>
   <code class="literal">
    NULL IN (SELECT
    <em class="replaceable">
     <code>
      inner_expr
     </code>
    </em>
    ...)
   </code>
   should evaluate to:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       NULL
      </code>
      , if the
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      produces any rows
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       FALSE
      </code>
      , if the
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      produces no rows
     </p>
    </li>
   </ul>
  </div>
  <p>
   For proper evaluation, it is necessary to be able to check
          whether the
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   has produced
          any rows at all, so
   <code class="literal">
    <em class="replaceable">
     <code>
      outer_expr
     </code>
    </em>
    =
    <em class="replaceable">
     <code>
      inner_expr
     </code>
    </em>
   </code>
   cannot be
          pushed down into the subquery. This is a problem because many
          real world subqueries become very slow unless the equality can
          be pushed down.
  </p>
  <p>
   Essentially, there must be different ways to execute the
          subquery depending on the value of
   <em class="replaceable">
    <code>
     outer_expr
    </code>
   </em>
   .
  </p>
  <p>
   The optimizer chooses SQL compliance over speed, so it
          accounts for the possibility that
   <em class="replaceable">
    <code>
     outer_expr
    </code>
   </em>
   might be
   <code class="literal">
    NULL
   </code>
   :
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      If
      <em class="replaceable">
       <code>
        outer_expr
       </code>
      </em>
      is
      <code class="literal">
       NULL
      </code>
      , to evaluate the following
              expression, it is necessary to execute the
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      to determine whether
              it produces any rows:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa55297223"><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 boolean">NULL</span> <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <em class="replaceable">inner_expr</em> <span class="token keyword">FROM</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">WHERE</span> <em class="replaceable">subquery_where</em><span class="token punctuation">)</span></code></pre>
     </div>
     <p>
      It is necessary to execute the original
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      here, without any
              pushed-down equalities of the kind mentioned previously.
     </p>
    </li>
    <li class="listitem">
     <p>
      On the other hand, when
      <em class="replaceable">
       <code>
        outer_expr
       </code>
      </em>
      is not
      <code class="literal">
       NULL
      </code>
      , it is absolutely essential that
              this comparison:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa3898127"><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"><em class="replaceable">outer_expr</em> <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <em class="replaceable">inner_expr</em> <span class="token keyword">FROM</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">WHERE</span> <em class="replaceable">subquery_where</em><span class="token punctuation">)</span></code></pre>
     </div>
     <p>
      Be converted to this expression that uses a pushed-down
              condition:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa2473766"><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">EXISTS</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token number">1</span> <span class="token keyword">FROM</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">WHERE</span> <em class="replaceable">subquery_where</em> <span class="token operator">AND</span> <em class="replaceable">outer_expr</em><span class="token operator">=</span><em class="replaceable">inner_expr</em><span class="token punctuation">)</span></code></pre>
     </div>
     <p>
      Without this conversion, subqueries are slow.
     </p>
    </li>
   </ul>
  </div>
  <p>
   To solve the dilemma of whether or not to push down conditions
          into the subquery, the conditions are wrapped within
   <span class="quote">
    “
    <span class="quote">
     trigger
    </span>
    ”
   </span>
   functions. Thus, an expression of the
          following form:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa47183186"><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"><em class="replaceable">outer_expr</em> <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <em class="replaceable">inner_expr</em> <span class="token keyword">FROM</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">WHERE</span> <em class="replaceable">subquery_where</em><span class="token punctuation">)</span></code></pre>
  </div>
  <p>
   Is converted into:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa28131917"><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">EXISTS</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token number">1</span> <span class="token keyword">FROM</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">WHERE</span> <em class="replaceable">subquery_where</em>
                          <span class="token operator">AND</span> trigcond<span class="token punctuation">(</span><em class="replaceable">outer_expr</em><span class="token operator">=</span><em class="replaceable">inner_expr</em><span class="token punctuation">)</span><span class="token punctuation">)</span></code></pre>
  </div>
  <p>
   More generally, if the subquery comparison is based on several
          pairs of outer and inner expressions, the conversion takes
          this comparison:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa77956927"><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">oe_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">oe_N</em><span class="token punctuation">)</span> <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <em class="replaceable">ie_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">ie_N</em> <span class="token keyword">FROM</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">WHERE</span> <em class="replaceable">subquery_where</em><span class="token punctuation">)</span></code></pre>
  </div>
  <p>
   And converts it to this expression:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa83919864"><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">EXISTS</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token number">1</span> <span class="token keyword">FROM</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">WHERE</span> <em class="replaceable">subquery_where</em>
                          <span class="token operator">AND</span> trigcond<span class="token punctuation">(</span><em class="replaceable">oe_1</em><span class="token operator">=</span><em class="replaceable">ie_1</em><span class="token punctuation">)</span>
                          <span class="token operator">AND</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
                          <span class="token operator">AND</span> trigcond<span class="token punctuation">(</span><em class="replaceable">oe_N</em><span class="token operator">=</span><em class="replaceable">ie_N</em><span class="token punctuation">)</span>
       <span class="token punctuation">)</span></code></pre>
  </div>
  <p>
   Each
   <code class="literal">
    trigcond(
    <em class="replaceable">
     <code>
      X
     </code>
    </em>
    )
   </code>
   is a special function that evaluates to the following values:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <em class="replaceable">
       <code>
        X
       </code>
      </em>
      when the
      <span class="quote">
       “
       <span class="quote">
        linked
       </span>
       ”
      </span>
      outer expression
      <em class="replaceable">
       <code>
        oe_i
       </code>
      </em>
      is not
      <code class="literal">
       NULL
      </code>
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       TRUE
      </code>
      when the
      <span class="quote">
       “
       <span class="quote">
        linked
       </span>
       ”
      </span>
      outer expression
      <em class="replaceable">
       <code>
        oe_i
       </code>
      </em>
      is
      <code class="literal">
       NULL
      </code>
     </p>
    </li>
   </ul>
  </div>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    Trigger functions are
    <span class="emphasis">
     <em>
      not
     </em>
    </span>
    triggers of
            the kind that you create with
    <a class="link" href="create-trigger.html" title="15.1.22 CREATE TRIGGER Statement">
     <code class="literal">
      CREATE
            TRIGGER
     </code>
    </a>
    .
   </p>
  </div>
  <p>
   Equalities that are wrapped within
   <code class="literal">
    trigcond()
   </code>
   functions are not first class
          predicates for the query optimizer. Most optimizations cannot
          deal with predicates that may be turned on and off at query
          execution time, so they assume any
   <code class="literal">
    trigcond(
    <em class="replaceable">
     <code>
      X
     </code>
    </em>
    )
   </code>
   to
          be an unknown function and ignore it. Triggered equalities can
          be used by those optimizations:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Reference optimizations:
      <code class="literal">
       trigcond(
       <em class="replaceable">
        <code>
         X
        </code>
       </em>
       =
       <em class="replaceable">
        <code>
         Y
        </code>
       </em>
       [OR
       <em class="replaceable">
        <code>
         Y
        </code>
       </em>
       IS NULL])
      </code>
      can
              be used to construct
      <a class="link" href="explain-output.html#jointype_ref">
       <code class="literal">
        ref
       </code>
      </a>
      ,
      <a class="link" href="explain-output.html#jointype_eq_ref">
       <code class="literal">
        eq_ref
       </code>
      </a>
      , or
      <a class="link" href="explain-output.html#jointype_ref_or_null">
       <code class="literal">
        ref_or_null
       </code>
      </a>
      table
              accesses.
     </p>
    </li>
    <li class="listitem">
     <p>
      Index lookup-based subquery execution engines:
      <code class="literal">
       trigcond(
       <em class="replaceable">
        <code>
         X
        </code>
       </em>
       =
       <em class="replaceable">
        <code>
         Y
        </code>
       </em>
       )
      </code>
      can be used to construct
      <a class="link" href="explain-output.html#jointype_unique_subquery">
       <code class="literal">
        unique_subquery
       </code>
      </a>
      or
      <a class="link" href="explain-output.html#jointype_index_subquery">
       <code class="literal">
        index_subquery
       </code>
      </a>
      accesses.
     </p>
    </li>
    <li class="listitem">
     <p>
      Table-condition generator: If the subquery is a join of
              several tables, the triggered condition is checked as soon
              as possible.
     </p>
    </li>
   </ul>
  </div>
  <p>
   When the optimizer uses a triggered condition to create some
          kind of index lookup-based access (as for the first two items
          of the preceding list), it must have a fallback strategy for
          the case when the condition is turned off. This fallback
          strategy is always the same: Do a full table scan. In
   <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
    <code class="literal">
     EXPLAIN
    </code>
   </a>
   output, the fallback
          shows up as
   <code class="literal">
    Full scan on NULL key
   </code>
   in the
   <code class="literal">
    Extra
   </code>
   column:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa88009572"><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">EXPLAIN</span> <span class="token keyword">SELECT</span> t1<span class="token punctuation">.</span>col1<span class="token punctuation">,</span>
       t1<span class="token punctuation">.</span>col1 <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> t2<span class="token punctuation">.</span>key1 <span class="token keyword">FROM</span> t2 <span class="token keyword">WHERE</span> t2<span class="token punctuation">.</span>col2<span class="token operator">=</span>t1<span class="token punctuation">.</span>col2<span class="token punctuation">)</span> <span class="token keyword">FROM</span> t1\G
<span class="token output"><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><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><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><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> 1. row <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><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><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><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>
           id<span class="token punctuation">:</span> 1
  select_type<span class="token punctuation">:</span> PRIMARY
        table<span class="token punctuation">:</span> t1
        ...
<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><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><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><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> 2. row <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><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><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><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>
           id<span class="token punctuation">:</span> 2
  select_type<span class="token punctuation">:</span> DEPENDENT SUBQUERY
        table<span class="token punctuation">:</span> t2
         type<span class="token punctuation">:</span> index_subquery
possible_keys<span class="token punctuation">:</span> key1
          key<span class="token punctuation">:</span> key1
      key_len<span class="token punctuation">:</span> 5
          ref<span class="token punctuation">:</span> func
         rows<span class="token punctuation">:</span> 2
        Extra<span class="token punctuation">:</span> Using where; Full scan on NULL key</span></code></pre>
  </div>
  <p>
   If you run
   <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
    <code class="literal">
     EXPLAIN
    </code>
   </a>
   followed by
   <a class="link" href="show-warnings.html" title="15.7.7.42 SHOW WARNINGS Statement">
    <code class="literal">
     SHOW WARNINGS
    </code>
   </a>
   , you can see the
          triggered condition:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-none"><div class="docs-select-all right" id="sa96538851"><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-none">*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select `test`.`t1`.`col1` AS `col1`,
         &lt;in_optimizer&gt;(`test`.`t1`.`col1`,
         &lt;exists&gt;(&lt;index_lookup&gt;(&lt;cache&gt;(`test`.`t1`.`col1`) in t2
         on key1 checking NULL
         where (`test`.`t2`.`col2` = `test`.`t1`.`col2`) having
         trigcond(&lt;is_not_null_test&gt;(`test`.`t2`.`key1`))))) AS
         `t1.col1 IN (select t2.key1 from t2 where t2.col2=t1.col2)`
         from `test`.`t1`</code></pre>
  </div>
  <p>
   The use of triggered conditions has some performance
          implications. A
   <code class="literal">
    NULL IN (SELECT ...)
   </code>
   expression now may cause a full table scan (which is slow)
          when it previously did not. This is the price paid for correct
          results (the goal of the trigger-condition strategy is to
          improve compliance, not speed).
  </p>
  <p>
   For multiple-table subqueries, execution of
   <code class="literal">
    NULL IN
          (SELECT ...)
   </code>
   is particularly slow because the join
          optimizer does not optimize for the case where the outer
          expression is
   <code class="literal">
    NULL
   </code>
   . It assumes that
          subquery evaluations with
   <code class="literal">
    NULL
   </code>
   on the left
          side are very rare, even if there are statistics that indicate
          otherwise. On the other hand, if the outer expression might be
   <code class="literal">
    NULL
   </code>
   but never actually is, there is no
          performance penalty.
  </p>
  <p>
   To help the query optimizer better execute your queries, use
          these suggestions:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Declare a column as
      <code class="literal">
       NOT NULL
      </code>
      if it
              really is. This also helps other aspects of the optimizer
              by simplifying condition testing for the column.
     </p>
    </li>
    <li class="listitem">
     <p>
      If you need not distinguish a
      <code class="literal">
       NULL
      </code>
      from
      <code class="literal">
       FALSE
      </code>
      subquery result, you can easily
              avoid the slow execution path. Replace a comparison that
              looks like this:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa42411226"><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"><em class="replaceable">outer_expr</em> <span class="token punctuation">[</span><span class="token operator">NOT</span><span class="token punctuation">]</span> <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <em class="replaceable">inner_expr</em> <span class="token keyword">FROM</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>
      with this expression:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa56553508"><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">outer_expr</em> <span class="token operator">IS</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">)</span> <span class="token operator">AND</span> <span class="token punctuation">(</span><em class="replaceable">outer_expr</em> <span class="token punctuation">[</span><span class="token operator">NOT</span><span class="token punctuation">]</span> <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <em class="replaceable">inner_expr</em> <span class="token keyword">FROM</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>
      Then
      <code class="literal">
       NULL IN (SELECT ...)
      </code>
      is never
              evaluated because MySQL stops evaluating
      <a class="link" href="logical-operators.html#operator_and">
       <code class="literal">
        AND
       </code>
      </a>
      parts as soon as the
              expression result is clear.
     </p>
     <p>
      Another possible rewrite:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa18761371"><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><span class="token operator">NOT</span><span class="token punctuation">]</span> <span class="token keyword">EXISTS</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <em class="replaceable">inner_expr</em> <span class="token keyword">FROM</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
        <span class="token keyword">WHERE</span> <em class="replaceable">inner_expr</em><span class="token operator">=</span><em class="replaceable">outer_expr</em><span class="token punctuation">)</span></code></pre>
     </div>
    </li>
   </ul>
  </div>
  <p>
   The
   <a class="link" href="switchable-optimizations.html#optflag_subquery-materialization-cost-based">
    <code class="literal">
     subquery_materialization_cost_based
    </code>
   </a>
   flag of the
   <a class="link" href="server-system-variables.html#sysvar_optimizer_switch">
    <code class="literal">
     optimizer_switch
    </code>
   </a>
   system variable enables control over the choice between
          subquery materialization and
   <code class="literal">
    IN
   </code>
   -to-
   <code class="literal">
    EXISTS
   </code>
   subquery
          transformation. See
   <a class="xref" href="switchable-optimizations.html" title="10.9.2 Switchable Optimizations">
    Section 10.9.2, “Switchable Optimizations”
   </a>
   .
  </p>
 </div>
 <br/>
</div>
