<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="outer-join-optimization">
      </a>
      10.2.1.9 Outer Join Optimization
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045229445232">
  </a>
  <a class="indexterm" name="idm46045229443744">
  </a>
  <a class="indexterm" name="idm46045229442256">
  </a>
  <a class="indexterm" name="idm46045229441184">
  </a>
  <a class="indexterm" name="idm46045229439696">
  </a>
  <a class="indexterm" name="idm46045229438624">
  </a>
  <p>
   Outer joins include
   <code class="literal">
    LEFT JOIN
   </code>
   and
   <code class="literal">
    RIGHT JOIN
   </code>
   .
  </p>
  <p>
   MySQL implements an
   <code class="literal">
    <em class="replaceable">
     <code>
      A
     </code>
    </em>
    LEFT
          JOIN
    <em class="replaceable">
     <code>
      B
     </code>
    </em>
    <em class="replaceable">
     <code>
      join_specification
     </code>
    </em>
   </code>
   as
          follows:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Table
      <em class="replaceable">
       <code>
        B
       </code>
      </em>
      is set to depend on
              table
      <em class="replaceable">
       <code>
        A
       </code>
      </em>
      and all tables on which
      <em class="replaceable">
       <code>
        A
       </code>
      </em>
      depends.
     </p>
    </li>
    <li class="listitem">
     <p>
      Table
      <em class="replaceable">
       <code>
        A
       </code>
      </em>
      is set to depend on all
              tables (except
      <em class="replaceable">
       <code>
        B
       </code>
      </em>
      ) that are used
              in the
      <code class="literal">
       LEFT JOIN
      </code>
      condition.
     </p>
    </li>
    <li class="listitem">
     <p>
      The
      <code class="literal">
       LEFT JOIN
      </code>
      condition is used to
              decide how to retrieve rows from table
      <em class="replaceable">
       <code>
        B
       </code>
      </em>
      . (In other words, any
              condition in the
      <code class="literal">
       WHERE
      </code>
      clause is not
              used.)
     </p>
    </li>
    <li class="listitem">
     <p>
      All standard join optimizations are performed, with the
              exception that a table is always read after all tables on
              which it depends. If there is a circular dependency, an
              error occurs.
     </p>
    </li>
    <li class="listitem">
     <p>
      All standard
      <code class="literal">
       WHERE
      </code>
      optimizations are
              performed.
     </p>
    </li>
    <li class="listitem">
     <p>
      If there is a row in
      <em class="replaceable">
       <code>
        A
       </code>
      </em>
      that
              matches the
      <code class="literal">
       WHERE
      </code>
      clause, but there is
              no row in
      <em class="replaceable">
       <code>
        B
       </code>
      </em>
      that matches the
      <code class="literal">
       ON
      </code>
      condition, an extra
      <em class="replaceable">
       <code>
        B
       </code>
      </em>
      row is generated with all
              columns set to
      <code class="literal">
       NULL
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      If you use
      <code class="literal">
       LEFT JOIN
      </code>
      to find rows that
              do not exist in some table and you have the following
              test:
      <code class="literal">
       <em class="replaceable">
        <code>
         col_name
        </code>
       </em>
       IS
              NULL
      </code>
      in the
      <code class="literal">
       WHERE
      </code>
      part, where
      <em class="replaceable">
       <code>
        col_name
       </code>
      </em>
      is a column that is
              declared as
      <code class="literal">
       NOT NULL
      </code>
      , MySQL stops
              searching for more rows (for a particular key combination)
              after it has found one row that matches the
      <code class="literal">
       LEFT
              JOIN
      </code>
      condition.
     </p>
    </li>
   </ul>
  </div>
  <p>
   The
   <code class="literal">
    RIGHT JOIN
   </code>
   implementation is analogous
          to that of
   <code class="literal">
    LEFT JOIN
   </code>
   with the table roles
          reversed. Right joins are converted to equivalent left joins,
          as described in
   <a class="xref" href="outer-join-simplification.html" title="10.2.1.10 Outer Join Simplification">
    Section 10.2.1.10, “Outer Join Simplification”
   </a>
   .
  </p>
  <p>
   For a
   <code class="literal">
    LEFT JOIN
   </code>
   , if the
   <code class="literal">
    WHERE
   </code>
   condition is always false for the
          generated
   <code class="literal">
    NULL
   </code>
   row, the
   <code class="literal">
    LEFT
          JOIN
   </code>
   is changed to an inner join. For example, the
   <code class="literal">
    WHERE
   </code>
   clause would be false in the
          following query if
   <code class="literal">
    t2.column1
   </code>
   were
   <code class="literal">
    NULL
   </code>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa15174580"><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">LEFT</span> <span class="token keyword">JOIN</span> t2 <span class="token keyword">ON</span> <span class="token punctuation">(</span>column1<span class="token punctuation">)</span> <span class="token keyword">WHERE</span> t2<span class="token punctuation">.</span>column2<span class="token operator">=</span><span class="token number">5</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Therefore, it is safe to convert the query to an inner join:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa38754113"><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 punctuation">,</span> t2 <span class="token keyword">WHERE</span> t2<span class="token punctuation">.</span>column2<span class="token operator">=</span><span class="token number">5</span> <span class="token operator">AND</span> t1<span class="token punctuation">.</span>column1<span class="token operator">=</span>t2<span class="token punctuation">.</span>column1<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Trivial
   <code class="literal">
    WHERE
   </code>
   conditions arising from
          constant literal expressions are removed during preparation,
          rather than at a later stage in optimization, by which time
          joins have already been simplified. Earlier removal of trivial
          conditions allows the optimizer to convert outer joins to
          inner joins; this can result in improved plans for queries
          with outer joins containing trivial conditions in the
   <code class="literal">
    WHERE
   </code>
   clause, such as this one:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa31063617"><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">LEFT</span> <span class="token keyword">JOIN</span> t2 <span class="token keyword">ON</span> <em class="replaceable">condition_1</em> <span class="token keyword">WHERE</span> <em class="replaceable">condition_2</em> <span class="token operator">OR</span> <span class="token number">0</span> <span class="token operator">=</span> <span class="token number">1</span></code></pre>
  </div>
  <p>
   The optimizer now sees during preparation that 0 = 1 is always
          false, making
   <code class="literal">
    OR 0 = 1
   </code>
   redundant, and
          removes it, leaving this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa88002847"><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">LEFT</span> <span class="token keyword">JOIN</span> t2 <span class="token keyword">ON</span> <em class="replaceable">condition_1</em> <span class="token keyword">where</span> <em class="replaceable">condition_2</em></code></pre>
  </div>
  <p>
   Now the optimizer can rewrite the query as an inner join, like
          this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa80784111"><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">JOIN</span> t2 <span class="token keyword">WHERE</span> <em class="replaceable">condition_1</em> <span class="token operator">AND</span> <em class="replaceable">condition_2</em></code></pre>
  </div>
  <a class="indexterm" name="idm46045229392944">
  </a>
  <p>
   Now the optimizer can use table
   <code class="literal">
    t2
   </code>
   before
          table
   <code class="literal">
    t1
   </code>
   if doing so would result in a
          better query plan. To provide a hint about the table join
          order, use optimizer hints; see
   <a class="xref" href="optimizer-hints.html" title="10.9.3 Optimizer Hints">
    Section 10.9.3, “Optimizer Hints”
   </a>
   . Alternatively, use
   <code class="literal">
    STRAIGHT_JOIN
   </code>
   ; see
   <a class="xref" href="select.html" title="15.2.13 SELECT Statement">
    Section 15.2.13, “SELECT Statement”
   </a>
   . However,
   <code class="literal">
    STRAIGHT_JOIN
   </code>
   may prevent indexes from
          being used because it disables semijoin transformations; see
   <a class="ulink" href="/doc/refman/8.0/en/semijoins.html" target="_top">
    Optimizing IN and EXISTS Subquery Predicates with Semijoin Transformations
   </a>
   .
  </p>
 </div>
 <br/>
</div>
