<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="nested-join-optimization">
      </a>
      10.2.1.8 Nested Join Optimization
     </h4>
    </div>
   </div>
  </div>
  <p>
   The syntax for expressing joins permits nested joins. The
          following discussion refers to the join syntax described in
   <a class="xref" href="join.html" title="15.2.13.2 JOIN Clause">
    Section 15.2.13.2, “JOIN Clause”
   </a>
   .
  </p>
  <p>
   The syntax of
   <em class="replaceable">
    <code>
     table_factor
    </code>
   </em>
   is
          extended in comparison with the SQL Standard. The latter
          accepts only
   <em class="replaceable">
    <code>
     table_reference
    </code>
   </em>
   , not a
          list of them inside a pair of parentheses. This is a
          conservative extension if we consider each comma in a list of
   <em class="replaceable">
    <code>
     table_reference
    </code>
   </em>
   items as equivalent
          to an inner join. For example:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa87664950"><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> <span class="token punctuation">(</span>t2<span class="token punctuation">,</span> t3<span class="token punctuation">,</span> t4<span class="token punctuation">)</span>
                 <span class="token keyword">ON</span> <span class="token punctuation">(</span>t2<span class="token punctuation">.</span>a<span class="token operator">=</span>t1<span class="token punctuation">.</span>a <span class="token operator">AND</span> t3<span class="token punctuation">.</span>b<span class="token operator">=</span>t1<span class="token punctuation">.</span>b <span class="token operator">AND</span> t4<span class="token punctuation">.</span>c<span class="token operator">=</span>t1<span class="token punctuation">.</span>c<span class="token punctuation">)</span></code></pre>
  </div>
  <p>
   Is equivalent to:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa19116309"><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> <span class="token punctuation">(</span>t2 <span class="token keyword">CROSS</span> <span class="token keyword">JOIN</span> t3 <span class="token keyword">CROSS</span> <span class="token keyword">JOIN</span> t4<span class="token punctuation">)</span>
                 <span class="token keyword">ON</span> <span class="token punctuation">(</span>t2<span class="token punctuation">.</span>a<span class="token operator">=</span>t1<span class="token punctuation">.</span>a <span class="token operator">AND</span> t3<span class="token punctuation">.</span>b<span class="token operator">=</span>t1<span class="token punctuation">.</span>b <span class="token operator">AND</span> t4<span class="token punctuation">.</span>c<span class="token operator">=</span>t1<span class="token punctuation">.</span>c<span class="token punctuation">)</span></code></pre>
  </div>
  <p>
   In MySQL,
   <code class="literal">
    CROSS JOIN
   </code>
   is syntactically
          equivalent to
   <code class="literal">
    INNER JOIN
   </code>
   ; they can replace
          each other. In standard SQL, they are not equivalent.
   <code class="literal">
    INNER JOIN
   </code>
   is used with an
   <code class="literal">
    ON
   </code>
   clause;
   <code class="literal">
    CROSS JOIN
   </code>
   is
          used otherwise.
  </p>
  <p>
   In general, parentheses can be ignored in join expressions
          containing only inner join operations. Consider this join
          expression:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa31032284"><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">t1 <span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> <span class="token punctuation">(</span>t2 <span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> t3 <span class="token keyword">ON</span> t2<span class="token punctuation">.</span>b<span class="token operator">=</span>t3<span class="token punctuation">.</span>b <span class="token operator">OR</span> t2<span class="token punctuation">.</span>b <span class="token operator">IS</span> <span class="token boolean">NULL</span><span class="token punctuation">)</span>
   <span class="token keyword">ON</span> t1<span class="token punctuation">.</span>a<span class="token operator">=</span>t2<span class="token punctuation">.</span>a</code></pre>
  </div>
  <p>
   After removing parentheses and grouping operations to the
          left, that join expression transforms into this expression:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa35093906"><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>t1 <span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> t2 <span class="token keyword">ON</span> t1<span class="token punctuation">.</span>a<span class="token operator">=</span>t2<span class="token punctuation">.</span>a<span class="token punctuation">)</span> <span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> t3
    <span class="token keyword">ON</span> t2<span class="token punctuation">.</span>b<span class="token operator">=</span>t3<span class="token punctuation">.</span>b <span class="token operator">OR</span> t2<span class="token punctuation">.</span>b <span class="token operator">IS</span> <span class="token boolean">NULL</span></code></pre>
  </div>
  <p>
   Yet, the two expressions are not equivalent. To see this,
          suppose that the tables
   <code class="literal">
    t1
   </code>
   ,
   <code class="literal">
    t2
   </code>
   , and
   <code class="literal">
    t3
   </code>
   have the
          following state:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Table
      <code class="literal">
       t1
      </code>
      contains rows
      <code class="literal">
       (1)
      </code>
      ,
      <code class="literal">
       (2)
      </code>
     </p>
    </li>
    <li class="listitem">
     <p>
      Table
      <code class="literal">
       t2
      </code>
      contains row
      <code class="literal">
       (1,101)
      </code>
     </p>
    </li>
    <li class="listitem">
     <p>
      Table
      <code class="literal">
       t3
      </code>
      contains row
      <code class="literal">
       (101)
      </code>
     </p>
    </li>
   </ul>
  </div>
  <p>
   In this case, the first expression returns a result set
          including the rows
   <code class="literal">
    (1,1,101,101)
   </code>
   ,
   <code class="literal">
    (2,NULL,NULL,NULL)
   </code>
   , whereas the second
          expression returns the rows
   <code class="literal">
    (1,1,101,101)
   </code>
   ,
   <code class="literal">
    (2,NULL,NULL,101)
   </code>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa58147795"><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">LEFT</span> <span class="token keyword">JOIN</span>
            <span class="token punctuation">(</span>t2 <span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> t3 <span class="token keyword">ON</span> t2<span class="token punctuation">.</span>b<span class="token operator">=</span>t3<span class="token punctuation">.</span>b <span class="token operator">OR</span> t2<span class="token punctuation">.</span>b <span class="token operator">IS</span> <span class="token boolean">NULL</span><span class="token punctuation">)</span>
            <span class="token keyword">ON</span> t1<span class="token punctuation">.</span>a<span class="token operator">=</span>t2<span class="token punctuation">.</span>a<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><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> a    <span class="token punctuation">|</span> a    <span class="token punctuation">|</span> b    <span class="token punctuation">|</span> b    <span class="token punctuation">|</span></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><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>    1 <span class="token punctuation">|</span>    1 <span class="token punctuation">|</span>  101 <span class="token punctuation">|</span>  101 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    2 <span class="token punctuation">|</span> NULL <span class="token punctuation">|</span> NULL <span class="token punctuation">|</span> NULL <span class="token punctuation">|</span></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><span class="token punctuation">-</span><span class="token punctuation">+</span></span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span>
       <span class="token keyword">FROM</span> <span class="token punctuation">(</span>t1 <span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> t2 <span class="token keyword">ON</span> t1<span class="token punctuation">.</span>a<span class="token operator">=</span>t2<span class="token punctuation">.</span>a<span class="token punctuation">)</span>
            <span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> t3
            <span class="token keyword">ON</span> t2<span class="token punctuation">.</span>b<span class="token operator">=</span>t3<span class="token punctuation">.</span>b <span class="token operator">OR</span> t2<span class="token punctuation">.</span>b <span class="token operator">IS</span> <span class="token boolean">NULL</span><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><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> a    <span class="token punctuation">|</span> a    <span class="token punctuation">|</span> b    <span class="token punctuation">|</span> b    <span class="token punctuation">|</span></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><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>    1 <span class="token punctuation">|</span>    1 <span class="token punctuation">|</span>  101 <span class="token punctuation">|</span>  101 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    2 <span class="token punctuation">|</span> NULL <span class="token punctuation">|</span> NULL <span class="token punctuation">|</span>  101 <span class="token punctuation">|</span></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><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <p>
   In the following example, an outer join operation is used
          together with an inner join operation:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa39842864"><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">t1 <span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> <span class="token punctuation">(</span>t2<span class="token punctuation">,</span> t3<span class="token punctuation">)</span> <span class="token keyword">ON</span> t1<span class="token punctuation">.</span>a<span class="token operator">=</span>t2<span class="token punctuation">.</span>a</code></pre>
  </div>
  <p>
   That expression cannot be transformed into the following
          expression:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa16303848"><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">t1 <span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> t2 <span class="token keyword">ON</span> t1<span class="token punctuation">.</span>a<span class="token operator">=</span>t2<span class="token punctuation">.</span>a<span class="token punctuation">,</span> t3</code></pre>
  </div>
  <p>
   For the given table states, the two expressions return
          different sets of rows:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa58551561"><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">LEFT</span> <span class="token keyword">JOIN</span> <span class="token punctuation">(</span>t2<span class="token punctuation">,</span> t3<span class="token punctuation">)</span> <span class="token keyword">ON</span> t1<span class="token punctuation">.</span>a<span class="token operator">=</span>t2<span class="token punctuation">.</span>a<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><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> a    <span class="token punctuation">|</span> a    <span class="token punctuation">|</span> b    <span class="token punctuation">|</span> b    <span class="token punctuation">|</span></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><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>    1 <span class="token punctuation">|</span>    1 <span class="token punctuation">|</span>  101 <span class="token punctuation">|</span>  101 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    2 <span class="token punctuation">|</span> NULL <span class="token punctuation">|</span> NULL <span class="token punctuation">|</span> NULL <span class="token punctuation">|</span></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><span class="token punctuation">-</span><span class="token punctuation">+</span></span>

<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">LEFT</span> <span class="token keyword">JOIN</span> t2 <span class="token keyword">ON</span> t1<span class="token punctuation">.</span>a<span class="token operator">=</span>t2<span class="token punctuation">.</span>a<span class="token punctuation">,</span> t3<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><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> a    <span class="token punctuation">|</span> a    <span class="token punctuation">|</span> b    <span class="token punctuation">|</span> b    <span class="token punctuation">|</span></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><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>    1 <span class="token punctuation">|</span>    1 <span class="token punctuation">|</span>  101 <span class="token punctuation">|</span>  101 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    2 <span class="token punctuation">|</span> NULL <span class="token punctuation">|</span> NULL <span class="token punctuation">|</span>  101 <span class="token punctuation">|</span></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><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <p>
   Therefore, if we omit parentheses in a join expression with
          outer join operators, we might change the result set for the
          original expression.
  </p>
  <p>
   More exactly, we cannot ignore parentheses in the right
          operand of the left outer join operation and in the left
          operand of a right join operation. In other words, we cannot
          ignore parentheses for the inner table expressions of outer
          join operations. Parentheses for the other operand (operand
          for the outer table) can be ignored.
  </p>
  <p>
   The following expression:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa28040457"><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>t1<span class="token punctuation">,</span>t2<span class="token punctuation">)</span> <span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> t3 <span class="token keyword">ON</span> P<span class="token punctuation">(</span>t2<span class="token punctuation">.</span>b<span class="token punctuation">,</span>t3<span class="token punctuation">.</span>b<span class="token punctuation">)</span></code></pre>
  </div>
  <p>
   Is equivalent to this expression for any tables
   <code class="literal">
    t1,t2,t3
   </code>
   and any condition
   <code class="literal">
    P
   </code>
   over attributes
   <code class="literal">
    t2.b
   </code>
   and
   <code class="literal">
    t3.b
   </code>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa21609699"><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">t1<span class="token punctuation">,</span> t2 <span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> t3 <span class="token keyword">ON</span> P<span class="token punctuation">(</span>t2<span class="token punctuation">.</span>b<span class="token punctuation">,</span>t3<span class="token punctuation">.</span>b<span class="token punctuation">)</span></code></pre>
  </div>
  <p>
   Whenever the order of execution of join operations in a join
          expression (
   <em class="replaceable">
    <code>
     joined_table
    </code>
   </em>
   ) is not
          from left to right, we talk about nested joins. Consider the
          following queries:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa25748256"><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> <span class="token punctuation">(</span>t2 <span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> t3 <span class="token keyword">ON</span> t2<span class="token punctuation">.</span>b<span class="token operator">=</span>t3<span class="token punctuation">.</span>b<span class="token punctuation">)</span> <span class="token keyword">ON</span> t1<span class="token punctuation">.</span>a<span class="token operator">=</span>t2<span class="token punctuation">.</span>a
  <span class="token keyword">WHERE</span> t1<span class="token punctuation">.</span>a <span class="token operator">&gt;</span> <span class="token number">1</span>

<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> <span class="token punctuation">(</span>t2<span class="token punctuation">,</span> t3<span class="token punctuation">)</span> <span class="token keyword">ON</span> t1<span class="token punctuation">.</span>a<span class="token operator">=</span>t2<span class="token punctuation">.</span>a
  <span class="token keyword">WHERE</span> <span class="token punctuation">(</span>t2<span class="token punctuation">.</span>b<span class="token operator">=</span>t3<span class="token punctuation">.</span>b <span class="token operator">OR</span> t2<span class="token punctuation">.</span>b <span class="token operator">IS</span> <span class="token boolean">NULL</span><span class="token punctuation">)</span> <span class="token operator">AND</span> t1<span class="token punctuation">.</span>a <span class="token operator">&gt;</span> <span class="token number">1</span></code></pre>
  </div>
  <p>
   Those queries are considered to contain these nested joins:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa76005027"><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">t2 <span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> t3 <span class="token keyword">ON</span> t2<span class="token punctuation">.</span>b<span class="token operator">=</span>t3<span class="token punctuation">.</span>b
t2<span class="token punctuation">,</span> t3</code></pre>
  </div>
  <p>
   In the first query, the nested join is formed with a left join
          operation. In the second query, it is formed with an inner
          join operation.
  </p>
  <p>
   In the first query, the parentheses can be omitted: The
          grammatical structure of the join expression dictates the same
          order of execution for join operations. For the second query,
          the parentheses cannot be omitted, although the join
          expression here can be interpreted unambiguously without them.
          In our extended syntax, the parentheses in
   <code class="literal">
    (t2,
          t3)
   </code>
   of the second query are required, although
          theoretically the query could be parsed without them: We still
          would have unambiguous syntactical structure for the query
          because
   <code class="literal">
    LEFT JOIN
   </code>
   and
   <code class="literal">
    ON
   </code>
   play the role of the left and right delimiters for the
          expression
   <code class="literal">
    (t2,t3)
   </code>
   .
  </p>
  <p>
   The preceding examples demonstrate these points:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      For join expressions involving only inner joins (and not
              outer joins), parentheses can be removed and joins
              evaluated left to right. In fact, tables can be evaluated
              in any order.
     </p>
    </li>
    <li class="listitem">
     <p>
      The same is not true, in general, for outer joins or for
              outer joins mixed with inner joins. Removal of parentheses
              may change the result.
     </p>
    </li>
   </ul>
  </div>
  <a class="indexterm" name="idm46045229506896">
  </a>
  <a class="indexterm" name="idm46045229505408">
  </a>
  <p>
   Queries with nested outer joins are executed in the same
          pipeline manner as queries with inner joins. More exactly, a
          variation of the nested-loop join algorithm is exploited.
          Recall the algorithm by which the nested-loop join executes a
          query (see
   <a class="xref" href="nested-loop-joins.html" title="10.2.1.7 Nested-Loop Join Algorithms">
    Section 10.2.1.7, “Nested-Loop Join Algorithms”
   </a>
   ). Suppose that
          a join query over 3 tables
   <code class="literal">
    T1,T2,T3
   </code>
   has
          this form:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa70881656"><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">INNER</span> <span class="token keyword">JOIN</span> T2 <span class="token keyword">ON</span> P1<span class="token punctuation">(</span>T1<span class="token punctuation">,</span>T2<span class="token punctuation">)</span>
                 <span class="token keyword">INNER</span> <span class="token keyword">JOIN</span> T3 <span class="token keyword">ON</span> P2<span class="token punctuation">(</span>T2<span class="token punctuation">,</span>T3<span class="token punctuation">)</span>
  <span class="token keyword">WHERE</span> P<span class="token punctuation">(</span>T1<span class="token punctuation">,</span>T2<span class="token punctuation">,</span>T3<span class="token punctuation">)</span></code></pre>
  </div>
  <p>
   Here,
   <code class="literal">
    P1(T1,T2)
   </code>
   and
   <code class="literal">
    P2(T3,T3)
   </code>
   are some join conditions (on
          expressions), whereas
   <code class="literal">
    P(T1,T2,T3)
   </code>
   is a
          condition over columns of tables
   <code class="literal">
    T1,T2,T3
   </code>
   .
  </p>
  <p>
   The nested-loop join algorithm would execute this query in the
          following manner:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-clike"><div class="docs-select-all right" id="sa95642955"><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-clike">FOR each row t1 <span class="token keyword">in</span> T1 <span class="token punctuation">{</span>
  FOR each row t2 <span class="token keyword">in</span> T2 such that <span class="token function">P1</span><span class="token punctuation">(</span>t1<span class="token punctuation">,</span>t2<span class="token punctuation">)</span> <span class="token punctuation">{</span>
    FOR each row t3 <span class="token keyword">in</span> T3 such that <span class="token function">P2</span><span class="token punctuation">(</span>t2<span class="token punctuation">,</span>t3<span class="token punctuation">)</span> <span class="token punctuation">{</span>
      IF <span class="token function">P</span><span class="token punctuation">(</span>t1<span class="token punctuation">,</span>t2<span class="token punctuation">,</span>t3<span class="token punctuation">)</span> <span class="token punctuation">{</span>
         t<span class="token punctuation">:</span><span class="token operator">=</span>t1<span class="token operator">||</span>t2<span class="token operator">||</span>t3<span class="token punctuation">;</span> OUTPUT 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>
   The notation
   <code class="literal">
    t1||t2||t3
   </code>
   indicates a row
          constructed by concatenating the columns of rows
   <code class="literal">
    t1
   </code>
   ,
   <code class="literal">
    t2
   </code>
   , and
   <code class="literal">
    t3
   </code>
   . In some of the following examples,
   <code class="literal">
    NULL
   </code>
   where a table name appears means a row
          in which
   <code class="literal">
    NULL
   </code>
   is used for each column of
          that table. For example,
   <code class="literal">
    t1||t2||NULL
   </code>
   indicates a row constructed by concatenating the columns of
          rows
   <code class="literal">
    t1
   </code>
   and
   <code class="literal">
    t2
   </code>
   , and
   <code class="literal">
    NULL
   </code>
   for each column of
   <code class="literal">
    t3
   </code>
   . Such a row is said to be
   <code class="literal">
    NULL
   </code>
   -complemented.
  </p>
  <a class="indexterm" name="idm46045229486992">
  </a>
  <p>
   Now consider a query with nested outer joins:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa23053414"><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>
              <span class="token punctuation">(</span>T2 <span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> T3 <span class="token keyword">ON</span> P2<span class="token punctuation">(</span>T2<span class="token punctuation">,</span>T3<span class="token punctuation">)</span><span class="token punctuation">)</span>
              <span class="token keyword">ON</span> P1<span class="token punctuation">(</span>T1<span class="token punctuation">,</span>T2<span class="token punctuation">)</span>
  <span class="token keyword">WHERE</span> P<span class="token punctuation">(</span>T1<span class="token punctuation">,</span>T2<span class="token punctuation">,</span>T3<span class="token punctuation">)</span></code></pre>
  </div>
  <p>
   For this query, modify the nested-loop pattern to obtain:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-clike"><div class="docs-select-all right" id="sa41827055"><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-clike">FOR each row t1 <span class="token keyword">in</span> T1 <span class="token punctuation">{</span>
  BOOL f1<span class="token punctuation">:</span><span class="token operator">=</span>FALSE<span class="token punctuation">;</span>
  FOR each row t2 <span class="token keyword">in</span> T2 such that <span class="token function">P1</span><span class="token punctuation">(</span>t1<span class="token punctuation">,</span>t2<span class="token punctuation">)</span> <span class="token punctuation">{</span>
    BOOL f2<span class="token punctuation">:</span><span class="token operator">=</span>FALSE<span class="token punctuation">;</span>
    FOR each row t3 <span class="token keyword">in</span> T3 such that <span class="token function">P2</span><span class="token punctuation">(</span>t2<span class="token punctuation">,</span>t3<span class="token punctuation">)</span> <span class="token punctuation">{</span>
      IF <span class="token function">P</span><span class="token punctuation">(</span>t1<span class="token punctuation">,</span>t2<span class="token punctuation">,</span>t3<span class="token punctuation">)</span> <span class="token punctuation">{</span>
        t<span class="token punctuation">:</span><span class="token operator">=</span>t1<span class="token operator">||</span>t2<span class="token operator">||</span>t3<span class="token punctuation">;</span> OUTPUT t<span class="token punctuation">;</span>
      <span class="token punctuation">}</span>
      f2<span class="token operator">=</span>TRUE<span class="token punctuation">;</span>
      f1<span class="token operator">=</span>TRUE<span class="token punctuation">;</span>
    <span class="token punctuation">}</span>
    IF <span class="token punctuation">(</span><span class="token operator">!</span>f2<span class="token punctuation">)</span> <span class="token punctuation">{</span>
      IF <span class="token function">P</span><span class="token punctuation">(</span>t1<span class="token punctuation">,</span>t2<span class="token punctuation">,</span>NULL<span class="token punctuation">)</span> <span class="token punctuation">{</span>
        t<span class="token punctuation">:</span><span class="token operator">=</span>t1<span class="token operator">||</span>t2<span class="token operator">||</span>NULL<span class="token punctuation">;</span> OUTPUT t<span class="token punctuation">;</span>
      <span class="token punctuation">}</span>
      f1<span class="token operator">=</span>TRUE<span class="token punctuation">;</span>
    <span class="token punctuation">}</span>
  <span class="token punctuation">}</span>
  IF <span class="token punctuation">(</span><span class="token operator">!</span>f1<span class="token punctuation">)</span> <span class="token punctuation">{</span>
    IF <span class="token function">P</span><span class="token punctuation">(</span>t1<span class="token punctuation">,</span>NULL<span class="token punctuation">,</span>NULL<span class="token punctuation">)</span> <span class="token punctuation">{</span>
      t<span class="token punctuation">:</span><span class="token operator">=</span>t1<span class="token operator">||</span>NULL<span class="token operator">||</span>NULL<span class="token punctuation">;</span> OUTPUT t<span class="token punctuation">;</span>
    <span class="token punctuation">}</span>
  <span class="token punctuation">}</span>
<span class="token punctuation">}</span></code></pre>
  </div>
  <p>
   In general, for any nested loop for the first inner table in
          an outer join operation, a flag is introduced that is turned
          off before the loop and is checked after the loop. The flag is
          turned on when for the current row from the outer table a
          match from the table representing the inner operand is found.
          If at the end of the loop cycle the flag is still off, no
          match has been found for the current row of the outer table.
          In this case, the row is complemented by
   <code class="literal">
    NULL
   </code>
   values for the columns of the inner
          tables. The result row is passed to the final check for the
          output or into the next nested loop, but only if the row
          satisfies the join condition of all embedded outer joins.
  </p>
  <p>
   In the example, the outer join table expressed by the
          following expression is embedded:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa36860968"><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>T2 <span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> T3 <span class="token keyword">ON</span> P2<span class="token punctuation">(</span>T2<span class="token punctuation">,</span>T3<span class="token punctuation">)</span><span class="token punctuation">)</span></code></pre>
  </div>
  <p>
   For the query with inner joins, the optimizer could choose a
          different order of nested loops, such as this one:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-clike"><div class="docs-select-all right" id="sa30785924"><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-clike">FOR each row t3 <span class="token keyword">in</span> T3 <span class="token punctuation">{</span>
  FOR each row t2 <span class="token keyword">in</span> T2 such that <span class="token function">P2</span><span class="token punctuation">(</span>t2<span class="token punctuation">,</span>t3<span class="token punctuation">)</span> <span class="token punctuation">{</span>
    FOR each row t1 <span class="token keyword">in</span> T1 such that <span class="token function">P1</span><span class="token punctuation">(</span>t1<span class="token punctuation">,</span>t2<span class="token punctuation">)</span> <span class="token punctuation">{</span>
      IF <span class="token function">P</span><span class="token punctuation">(</span>t1<span class="token punctuation">,</span>t2<span class="token punctuation">,</span>t3<span class="token punctuation">)</span> <span class="token punctuation">{</span>
         t<span class="token punctuation">:</span><span class="token operator">=</span>t1<span class="token operator">||</span>t2<span class="token operator">||</span>t3<span class="token punctuation">;</span> OUTPUT 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>
   For queries with outer joins, the optimizer can choose only
          such an order where loops for outer tables precede loops for
          inner tables. Thus, for our query with outer joins, only one
          nesting order is possible. For the following query, the
          optimizer evaluates two different nestings. In both nestings,
   <code class="literal">
    T1
   </code>
   must be processed in the outer loop
          because it is used in an outer join.
   <code class="literal">
    T2
   </code>
   and
   <code class="literal">
    T3
   </code>
   are used in an inner join, so that join
          must be processed in the inner loop. However, because the join
          is an inner join,
   <code class="literal">
    T2
   </code>
   and
   <code class="literal">
    T3
   </code>
   can be processed in either order.
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa98447144"><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> T1 <span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> <span class="token punctuation">(</span>T2<span class="token punctuation">,</span>T3<span class="token punctuation">)</span> <span class="token keyword">ON</span> P1<span class="token punctuation">(</span>T1<span class="token punctuation">,</span>T2<span class="token punctuation">)</span> <span class="token operator">AND</span> P2<span class="token punctuation">(</span>T1<span class="token punctuation">,</span>T3<span class="token punctuation">)</span>
  <span class="token keyword">WHERE</span> P<span class="token punctuation">(</span>T1<span class="token punctuation">,</span>T2<span class="token punctuation">,</span>T3<span class="token punctuation">)</span></code></pre>
  </div>
  <p>
   One nesting evaluates
   <code class="literal">
    T2
   </code>
   , then
   <code class="literal">
    T3
   </code>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-clike"><div class="docs-select-all right" id="sa6597944"><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-clike">FOR each row t1 <span class="token keyword">in</span> T1 <span class="token punctuation">{</span>
  BOOL f1<span class="token punctuation">:</span><span class="token operator">=</span>FALSE<span class="token punctuation">;</span>
  FOR each row t2 <span class="token keyword">in</span> T2 such that <span class="token function">P1</span><span class="token punctuation">(</span>t1<span class="token punctuation">,</span>t2<span class="token punctuation">)</span> <span class="token punctuation">{</span>
    FOR each row t3 <span class="token keyword">in</span> T3 such that <span class="token function">P2</span><span class="token punctuation">(</span>t1<span class="token punctuation">,</span>t3<span class="token punctuation">)</span> <span class="token punctuation">{</span>
      IF <span class="token function">P</span><span class="token punctuation">(</span>t1<span class="token punctuation">,</span>t2<span class="token punctuation">,</span>t3<span class="token punctuation">)</span> <span class="token punctuation">{</span>
        t<span class="token punctuation">:</span><span class="token operator">=</span>t1<span class="token operator">||</span>t2<span class="token operator">||</span>t3<span class="token punctuation">;</span> OUTPUT t<span class="token punctuation">;</span>
      <span class="token punctuation">}</span>
      f1<span class="token punctuation">:</span><span class="token operator">=</span>TRUE
    <span class="token punctuation">}</span>
  <span class="token punctuation">}</span>
  IF <span class="token punctuation">(</span><span class="token operator">!</span>f1<span class="token punctuation">)</span> <span class="token punctuation">{</span>
    IF <span class="token function">P</span><span class="token punctuation">(</span>t1<span class="token punctuation">,</span>NULL<span class="token punctuation">,</span>NULL<span class="token punctuation">)</span> <span class="token punctuation">{</span>
      t<span class="token punctuation">:</span><span class="token operator">=</span>t1<span class="token operator">||</span>NULL<span class="token operator">||</span>NULL<span class="token punctuation">;</span> OUTPUT t<span class="token punctuation">;</span>
    <span class="token punctuation">}</span>
  <span class="token punctuation">}</span>
<span class="token punctuation">}</span></code></pre>
  </div>
  <p>
   The other nesting evaluates
   <code class="literal">
    T3
   </code>
   , then
   <code class="literal">
    T2
   </code>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-clike"><div class="docs-select-all right" id="sa79320099"><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-clike">FOR each row t1 <span class="token keyword">in</span> T1 <span class="token punctuation">{</span>
  BOOL f1<span class="token punctuation">:</span><span class="token operator">=</span>FALSE<span class="token punctuation">;</span>
  FOR each row t3 <span class="token keyword">in</span> T3 such that <span class="token function">P2</span><span class="token punctuation">(</span>t1<span class="token punctuation">,</span>t3<span class="token punctuation">)</span> <span class="token punctuation">{</span>
    FOR each row t2 <span class="token keyword">in</span> T2 such that <span class="token function">P1</span><span class="token punctuation">(</span>t1<span class="token punctuation">,</span>t2<span class="token punctuation">)</span> <span class="token punctuation">{</span>
      IF <span class="token function">P</span><span class="token punctuation">(</span>t1<span class="token punctuation">,</span>t2<span class="token punctuation">,</span>t3<span class="token punctuation">)</span> <span class="token punctuation">{</span>
        t<span class="token punctuation">:</span><span class="token operator">=</span>t1<span class="token operator">||</span>t2<span class="token operator">||</span>t3<span class="token punctuation">;</span> OUTPUT t<span class="token punctuation">;</span>
      <span class="token punctuation">}</span>
      f1<span class="token punctuation">:</span><span class="token operator">=</span>TRUE
    <span class="token punctuation">}</span>
  <span class="token punctuation">}</span>
  IF <span class="token punctuation">(</span><span class="token operator">!</span>f1<span class="token punctuation">)</span> <span class="token punctuation">{</span>
    IF <span class="token function">P</span><span class="token punctuation">(</span>t1<span class="token punctuation">,</span>NULL<span class="token punctuation">,</span>NULL<span class="token punctuation">)</span> <span class="token punctuation">{</span>
      t<span class="token punctuation">:</span><span class="token operator">=</span>t1<span class="token operator">||</span>NULL<span class="token operator">||</span>NULL<span class="token punctuation">;</span> OUTPUT t<span class="token punctuation">;</span>
    <span class="token punctuation">}</span>
  <span class="token punctuation">}</span>
<span class="token punctuation">}</span></code></pre>
  </div>
  <p>
   When discussing the nested-loop algorithm for inner joins, we
          omitted some details whose impact on the performance of query
          execution may be huge. We did not mention so-called
   <span class="quote">
    “
    <span class="quote">
     pushed-down
    </span>
    ”
   </span>
   conditions. Suppose that our
   <code class="literal">
    WHERE
   </code>
   condition
   <code class="literal">
    P(T1,T2,T3)
   </code>
   can be represented by a
          conjunctive formula:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-clike"><div class="docs-select-all right" id="sa46103278"><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-clike"><span class="token function">P</span><span class="token punctuation">(</span>T1<span class="token punctuation">,</span>T2<span class="token punctuation">,</span>T2<span class="token punctuation">)</span> <span class="token operator">=</span> <span class="token function">C1</span><span class="token punctuation">(</span>T1<span class="token punctuation">)</span> AND <span class="token function">C2</span><span class="token punctuation">(</span>T2<span class="token punctuation">)</span> AND <span class="token function">C3</span><span class="token punctuation">(</span>T3<span class="token punctuation">)</span><span class="token punctuation">.</span></code></pre>
  </div>
  <p>
   In this case, MySQL actually uses the following nested-loop
          algorithm for the execution of the query with inner joins:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-clike"><div class="docs-select-all right" id="sa61551975"><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-clike">FOR each row t1 <span class="token keyword">in</span> T1 such that <span class="token function">C1</span><span class="token punctuation">(</span>t1<span class="token punctuation">)</span> <span class="token punctuation">{</span>
  FOR each row t2 <span class="token keyword">in</span> T2 such that <span class="token function">P1</span><span class="token punctuation">(</span>t1<span class="token punctuation">,</span>t2<span class="token punctuation">)</span> AND <span class="token function">C2</span><span class="token punctuation">(</span>t2<span class="token punctuation">)</span>  <span class="token punctuation">{</span>
    FOR each row t3 <span class="token keyword">in</span> T3 such that <span class="token function">P2</span><span class="token punctuation">(</span>t2<span class="token punctuation">,</span>t3<span class="token punctuation">)</span> AND <span class="token function">C3</span><span class="token punctuation">(</span>t3<span class="token punctuation">)</span> <span class="token punctuation">{</span>
      IF <span class="token function">P</span><span class="token punctuation">(</span>t1<span class="token punctuation">,</span>t2<span class="token punctuation">,</span>t3<span class="token punctuation">)</span> <span class="token punctuation">{</span>
         t<span class="token punctuation">:</span><span class="token operator">=</span>t1<span class="token operator">||</span>t2<span class="token operator">||</span>t3<span class="token punctuation">;</span> OUTPUT 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>
   You see that each of the conjuncts
   <code class="literal">
    C1(T1)
   </code>
   ,
   <code class="literal">
    C2(T2)
   </code>
   ,
   <code class="literal">
    C3(T3)
   </code>
   are
          pushed out of the most inner loop to the most outer loop where
          it can be evaluated. If
   <code class="literal">
    C1(T1)
   </code>
   is a very
          restrictive condition, this condition pushdown may greatly
          reduce the number of rows from table
   <code class="literal">
    T1
   </code>
   passed to the inner loops. As a result, the execution time for
          the query may improve immensely.
  </p>
  <p>
   For a query with outer joins, the
   <code class="literal">
    WHERE
   </code>
   condition is to be checked only after it has been found that
          the current row from the outer table has a match in the inner
          tables. Thus, the optimization of pushing conditions out of
          the inner nested loops cannot be applied directly to queries
          with outer joins. Here we must introduce conditional
          pushed-down predicates guarded by the flags that are turned on
          when a match has been encountered.
  </p>
  <p>
   Recall this example with outer joins:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-clike"><div class="docs-select-all right" id="sa75761356"><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-clike"><span class="token function">P</span><span class="token punctuation">(</span>T1<span class="token punctuation">,</span>T2<span class="token punctuation">,</span>T3<span class="token punctuation">)</span><span class="token operator">=</span><span class="token function">C1</span><span class="token punctuation">(</span>T1<span class="token punctuation">)</span> AND <span class="token function">C</span><span class="token punctuation">(</span>T2<span class="token punctuation">)</span> AND <span class="token function">C3</span><span class="token punctuation">(</span>T3<span class="token punctuation">)</span></code></pre>
  </div>
  <p>
   For that example, the nested-loop algorithm using guarded
          pushed-down conditions looks like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-clike"><div class="docs-select-all right" id="sa94400307"><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-clike">FOR each row t1 <span class="token keyword">in</span> T1 such that <span class="token function">C1</span><span class="token punctuation">(</span>t1<span class="token punctuation">)</span> <span class="token punctuation">{</span>
  BOOL f1<span class="token punctuation">:</span><span class="token operator">=</span>FALSE<span class="token punctuation">;</span>
  FOR each row t2 <span class="token keyword">in</span> T2
      such that <span class="token function">P1</span><span class="token punctuation">(</span>t1<span class="token punctuation">,</span>t2<span class="token punctuation">)</span> AND <span class="token punctuation">(</span>f1<span class="token operator">?</span><span class="token function">C2</span><span class="token punctuation">(</span>t2<span class="token punctuation">)</span><span class="token punctuation">:</span>TRUE<span class="token punctuation">)</span> <span class="token punctuation">{</span>
    BOOL f2<span class="token punctuation">:</span><span class="token operator">=</span>FALSE<span class="token punctuation">;</span>
    FOR each row t3 <span class="token keyword">in</span> T3
        such that <span class="token function">P2</span><span class="token punctuation">(</span>t2<span class="token punctuation">,</span>t3<span class="token punctuation">)</span> AND <span class="token punctuation">(</span>f1<span class="token operator">&amp;&amp;</span>f2<span class="token operator">?</span><span class="token function">C3</span><span class="token punctuation">(</span>t3<span class="token punctuation">)</span><span class="token punctuation">:</span>TRUE<span class="token punctuation">)</span> <span class="token punctuation">{</span>
      IF <span class="token punctuation">(</span>f1<span class="token operator">&amp;&amp;</span>f2<span class="token operator">?</span>TRUE<span class="token punctuation">:</span><span class="token punctuation">(</span><span class="token function">C2</span><span class="token punctuation">(</span>t2<span class="token punctuation">)</span> AND <span class="token function">C3</span><span class="token punctuation">(</span>t3<span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
        t<span class="token punctuation">:</span><span class="token operator">=</span>t1<span class="token operator">||</span>t2<span class="token operator">||</span>t3<span class="token punctuation">;</span> OUTPUT t<span class="token punctuation">;</span>
      <span class="token punctuation">}</span>
      f2<span class="token operator">=</span>TRUE<span class="token punctuation">;</span>
      f1<span class="token operator">=</span>TRUE<span class="token punctuation">;</span>
    <span class="token punctuation">}</span>
    IF <span class="token punctuation">(</span><span class="token operator">!</span>f2<span class="token punctuation">)</span> <span class="token punctuation">{</span>
      IF <span class="token punctuation">(</span>f1<span class="token operator">?</span>TRUE<span class="token punctuation">:</span><span class="token function">C2</span><span class="token punctuation">(</span>t2<span class="token punctuation">)</span> <span class="token operator">&amp;&amp;</span> <span class="token function">P</span><span class="token punctuation">(</span>t1<span class="token punctuation">,</span>t2<span class="token punctuation">,</span>NULL<span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
        t<span class="token punctuation">:</span><span class="token operator">=</span>t1<span class="token operator">||</span>t2<span class="token operator">||</span>NULL<span class="token punctuation">;</span> OUTPUT t<span class="token punctuation">;</span>
      <span class="token punctuation">}</span>
      f1<span class="token operator">=</span>TRUE<span class="token punctuation">;</span>
    <span class="token punctuation">}</span>
  <span class="token punctuation">}</span>
  IF <span class="token punctuation">(</span><span class="token operator">!</span>f1 <span class="token operator">&amp;&amp;</span> <span class="token function">P</span><span class="token punctuation">(</span>t1<span class="token punctuation">,</span>NULL<span class="token punctuation">,</span>NULL<span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
      t<span class="token punctuation">:</span><span class="token operator">=</span>t1<span class="token operator">||</span>NULL<span class="token operator">||</span>NULL<span class="token punctuation">;</span> OUTPUT t<span class="token punctuation">;</span>
  <span class="token punctuation">}</span>
<span class="token punctuation">}</span></code></pre>
  </div>
  <p>
   In general, pushed-down predicates can be extracted from join
          conditions such as
   <code class="literal">
    P1(T1,T2)
   </code>
   and
   <code class="literal">
    P(T2,T3)
   </code>
   . In this case, a pushed-down
          predicate is guarded also by a flag that prevents checking the
          predicate for the
   <code class="literal">
    NULL
   </code>
   -complemented row
          generated by the corresponding outer join operation.
  </p>
  <p>
   Access by key from one inner table to another in the same
          nested join is prohibited if it is induced by a predicate from
          the
   <code class="literal">
    WHERE
   </code>
   condition.
  </p>
 </div>
 <br/>
</div>
