<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="with">
      </a>
      15.2.20 WITH (Common Table Expressions)
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045179219200">
  </a>
  <p>
   A common table expression (CTE) is a named temporary result set
      that exists within the scope of a single statement and that can be
      referred to later within that statement, possibly multiple times.
      The following discussion describes how to write statements that
      use CTEs.
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="with.html#common-table-expressions" title="Common Table Expressions">
       Common Table Expressions
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="with.html#common-table-expressions-recursive" title="Recursive Common Table Expressions">
       Recursive Common Table Expressions
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="with.html#common-table-expressions-recursion-limits" title="Limiting Common Table Expression Recursion">
       Limiting Common Table Expression Recursion
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="with.html#common-table-expressions-recursive-examples" title="Recursive Common Table Expression Examples">
       Recursive Common Table Expression Examples
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="with.html#common-table-expressions-similar-constructs" title="Common Table Expressions Compared to Similar Constructs">
       Common Table Expressions Compared to Similar Constructs
      </a>
     </p>
    </li>
   </ul>
  </div>
  <p>
   For information about CTE optimization, see
   <a class="xref" href="derived-table-optimization.html" title="10.2.2.4 Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization">
    Section 10.2.2.4, “Optimizing Derived Tables, View References, and Common Table Expressions
          with Merging or Materialization”
   </a>
   .
  </p>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="common-table-expressions">
       </a>
       Common Table Expressions
      </h4>
     </div>
    </div>
   </div>
   <p>
    To specify common table expressions, use a
    <a class="link" href="with.html" title="15.2.20 WITH (Common Table Expressions)">
     <code class="literal">
      WITH
     </code>
    </a>
    clause that has one or more
        comma-separated subclauses. Each subclause provides a subquery
        that produces a result set, and associates a name with the
        subquery. The following example defines CTEs named
    <code class="literal">
     cte1
    </code>
    and
    <code class="literal">
     cte2
    </code>
    in the
    <a class="link" href="with.html" title="15.2.20 WITH (Common Table Expressions)">
     <code class="literal">
      WITH
     </code>
    </a>
    clause, and refers to them
        in the top-level
    <a class="link" href="select.html" title="15.2.13 SELECT Statement">
     <code class="literal">
      SELECT
     </code>
    </a>
    that
        follows the
    <a class="link" href="with.html" title="15.2.20 WITH (Common Table Expressions)">
     <code class="literal">
      WITH
     </code>
    </a>
    clause:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa70033151"><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">WITH</span>
  cte1 <span class="token keyword">AS</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> a<span class="token punctuation">,</span> b <span class="token keyword">FROM</span> table1<span class="token punctuation">)</span><span class="token punctuation">,</span>
  cte2 <span class="token keyword">AS</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> c<span class="token punctuation">,</span> d <span class="token keyword">FROM</span> table2<span class="token punctuation">)</span>
<span class="token keyword">SELECT</span> b<span class="token punctuation">,</span> d <span class="token keyword">FROM</span> cte1 <span class="token keyword">JOIN</span> cte2
<span class="token keyword">WHERE</span> cte1<span class="token punctuation">.</span>a <span class="token operator">=</span> cte2<span class="token punctuation">.</span>c<span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    In the statement containing the
    <a class="link" href="with.html" title="15.2.20 WITH (Common Table Expressions)">
     <code class="literal">
      WITH
     </code>
    </a>
    clause, each CTE name can be
        referenced to access the corresponding CTE result set.
   </p>
   <p>
    A CTE name can be referenced in other CTEs, enabling CTEs to be
        defined based on other CTEs.
   </p>
   <p>
    A CTE can refer to itself to define a recursive CTE. Common
        applications of recursive CTEs include series generation and
        traversal of hierarchical or tree-structured data.
   </p>
   <p>
    Common table expressions are an optional part of the syntax for
        DML statements. They are defined using a
    <a class="link" href="with.html" title="15.2.20 WITH (Common Table Expressions)">
     <code class="literal">
      WITH
     </code>
    </a>
    clause:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa79876892"><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">with_clause</em>:
    <span class="token keyword">WITH</span> <span class="token punctuation">[</span><span class="token keyword">RECURSIVE</span><span class="token punctuation">]</span>
        <em class="replaceable">cte_name</em> <span class="token punctuation">[</span><span class="token punctuation">(</span><em class="replaceable">col_name</em> <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">col_name</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><span class="token punctuation">]</span> <span class="token keyword">AS</span> <span class="token punctuation">(</span><em class="replaceable">subquery</em><span class="token punctuation">)</span>
        <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">cte_name</em> <span class="token punctuation">[</span><span class="token punctuation">(</span><em class="replaceable">col_name</em> <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">col_name</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><span class="token punctuation">]</span> <span class="token keyword">AS</span> <span class="token punctuation">(</span><em class="replaceable">subquery</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></code></pre>
   </div>
   <p>
    <em class="replaceable">
     <code>
      cte_name
     </code>
    </em>
    names a single common table
        expression and can be used as a table reference in the statement
        containing the
    <a class="link" href="with.html" title="15.2.20 WITH (Common Table Expressions)">
     <code class="literal">
      WITH
     </code>
    </a>
    clause.
   </p>
   <p>
    The
    <em class="replaceable">
     <code>
      subquery
     </code>
    </em>
    part of
    <code class="literal">
     AS
        (
     <em class="replaceable">
      <code>
       subquery
      </code>
     </em>
     )
    </code>
    is called the
    <span class="quote">
     “
     <span class="quote">
      subquery of the CTE
     </span>
     ”
    </span>
    and is what produces the CTE
        result set. The parentheses following
    <code class="literal">
     AS
    </code>
    are
        required.
   </p>
   <p>
    A common table expression is recursive if its subquery refers to
        its own name. The
    <code class="literal">
     RECURSIVE
    </code>
    keyword must be
        included if any CTE in the
    <a class="link" href="with.html" title="15.2.20 WITH (Common Table Expressions)">
     <code class="literal">
      WITH
     </code>
    </a>
    clause is recursive. For more information, see
    <a class="xref" href="with.html#common-table-expressions-recursive" title="Recursive Common Table Expressions">
     Recursive Common Table Expressions
    </a>
    .
   </p>
   <p>
    Determination of column names for a given CTE occurs as follows:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       If a parenthesized list of names follows the CTE name, those
            names are the column names:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa82263299"><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">WITH</span> cte <span class="token punctuation">(</span>col1<span class="token punctuation">,</span> col2<span class="token punctuation">)</span> <span class="token keyword">AS</span>
<span class="token punctuation">(</span>
  <span class="token keyword">SELECT</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">2</span>
  <span class="token keyword">UNION</span> <span class="token keyword">ALL</span>
  <span class="token keyword">SELECT</span> <span class="token number">3</span><span class="token punctuation">,</span> <span class="token number">4</span>
<span class="token punctuation">)</span>
<span class="token keyword">SELECT</span> col1<span class="token punctuation">,</span> col2 <span class="token keyword">FROM</span> cte<span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       The number of names in the list must be the same as the
            number of columns in the result set.
      </p>
     </li>
     <li class="listitem">
      <p>
       Otherwise, the column names come from the select list of the
            first
       <a class="link" href="select.html" title="15.2.13 SELECT Statement">
        <code class="literal">
         SELECT
        </code>
       </a>
       within the
       <code class="literal">
        AS (
        <em class="replaceable">
         <code>
          subquery
         </code>
        </em>
        )
       </code>
       part:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa13662318"><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">WITH</span> cte <span class="token keyword">AS</span>
<span class="token punctuation">(</span>
  <span class="token keyword">SELECT</span> <span class="token number">1</span> <span class="token keyword">AS</span> col1<span class="token punctuation">,</span> <span class="token number">2</span> <span class="token keyword">AS</span> col2
  <span class="token keyword">UNION</span> <span class="token keyword">ALL</span>
  <span class="token keyword">SELECT</span> <span class="token number">3</span><span class="token punctuation">,</span> <span class="token number">4</span>
<span class="token punctuation">)</span>
<span class="token keyword">SELECT</span> col1<span class="token punctuation">,</span> col2 <span class="token keyword">FROM</span> cte<span class="token punctuation">;</span></code></pre>
      </div>
     </li>
    </ul>
   </div>
   <p>
    A
    <a class="link" href="with.html" title="15.2.20 WITH (Common Table Expressions)">
     <code class="literal">
      WITH
     </code>
    </a>
    clause is permitted in
        these contexts:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       At the beginning of
       <a class="link" href="select.html" title="15.2.13 SELECT Statement">
        <code class="literal">
         SELECT
        </code>
       </a>
       ,
       <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
        <code class="literal">
         UPDATE
        </code>
       </a>
       , and
       <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
        <code class="literal">
         DELETE
        </code>
       </a>
       statements.
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa8925294"><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">WITH</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">SELECT</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
<span class="token keyword">WITH</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">UPDATE</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
<span class="token keyword">WITH</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">DELETE</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       At the beginning of subqueries (including derived table
            subqueries):
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa61887833"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">WHERE</span> id <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token keyword">WITH</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">SELECT</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <span class="token punctuation">(</span><span class="token keyword">WITH</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">SELECT</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> dt <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       Immediately preceding
       <a class="link" href="select.html" title="15.2.13 SELECT Statement">
        <code class="literal">
         SELECT
        </code>
       </a>
       for statements that include a
       <a class="link" href="select.html" title="15.2.13 SELECT Statement">
        <code class="literal">
         SELECT
        </code>
       </a>
       statement:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa67203631"><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">INSERT</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">WITH</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">SELECT</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
<span class="token keyword">REPLACE</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">WITH</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">SELECT</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">WITH</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">SELECT</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
<span class="token keyword">CREATE</span> <span class="token keyword">VIEW</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">WITH</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">SELECT</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
<span class="token keyword">DECLARE</span> <span class="token keyword">CURSOR</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">WITH</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">SELECT</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
<span class="token keyword">EXPLAIN</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">WITH</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">SELECT</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span></code></pre>
      </div>
     </li>
    </ul>
   </div>
   <p>
    Only one
    <a class="link" href="with.html" title="15.2.20 WITH (Common Table Expressions)">
     <code class="literal">
      WITH
     </code>
    </a>
    clause is permitted
        at the same level.
    <a class="link" href="with.html" title="15.2.20 WITH (Common Table Expressions)">
     <code class="literal">
      WITH
     </code>
    </a>
    followed
        by
    <a class="link" href="with.html" title="15.2.20 WITH (Common Table Expressions)">
     <code class="literal">
      WITH
     </code>
    </a>
    at the same level is not
        permitted, so this is illegal:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa62557073"><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">WITH</span> cte1 <span class="token keyword">AS</span> <span class="token punctuation">(</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">WITH</span> cte2 <span class="token keyword">AS</span> <span class="token punctuation">(</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span> <span class="token keyword">SELECT</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span></code></pre>
   </div>
   <p>
    To make the statement legal, use a single
    <a class="link" href="with.html" title="15.2.20 WITH (Common Table Expressions)">
     <code class="literal">
      WITH
     </code>
    </a>
    clause that separates the
        subclauses by a comma:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa31331961"><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">WITH</span> cte1 <span class="token keyword">AS</span> <span class="token punctuation">(</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span><span class="token punctuation">,</span> cte2 <span class="token keyword">AS</span> <span class="token punctuation">(</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span> <span class="token keyword">SELECT</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span></code></pre>
   </div>
   <p>
    However, a statement can contain multiple
    <a class="link" href="with.html" title="15.2.20 WITH (Common Table Expressions)">
     <code class="literal">
      WITH
     </code>
    </a>
    clauses if they occur at
        different levels:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa13751374"><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">WITH</span> cte1 <span class="token keyword">AS</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token number">1</span><span class="token punctuation">)</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <span class="token punctuation">(</span><span class="token keyword">WITH</span> cte2 <span class="token keyword">AS</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token number">2</span><span class="token punctuation">)</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> cte2 <span class="token keyword">JOIN</span> cte1<span class="token punctuation">)</span> <span class="token keyword">AS</span> dt<span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    A
    <a class="link" href="with.html" title="15.2.20 WITH (Common Table Expressions)">
     <code class="literal">
      WITH
     </code>
    </a>
    clause can define one or
        more common table expressions, but each CTE name must be unique
        to the clause. This is illegal:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa54445303"><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">WITH</span> cte1 <span class="token keyword">AS</span> <span class="token punctuation">(</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span><span class="token punctuation">,</span> cte1 <span class="token keyword">AS</span> <span class="token punctuation">(</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span> <span class="token keyword">SELECT</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span></code></pre>
   </div>
   <p>
    To make the statement legal, define the CTEs with unique names:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa63541151"><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">WITH</span> cte1 <span class="token keyword">AS</span> <span class="token punctuation">(</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span><span class="token punctuation">,</span> cte2 <span class="token keyword">AS</span> <span class="token punctuation">(</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span> <span class="token keyword">SELECT</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span></code></pre>
   </div>
   <p>
    A CTE can refer to itself or to other CTEs:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       A self-referencing CTE is recursive.
      </p>
     </li>
     <li class="listitem">
      <p>
       A CTE can refer to CTEs defined earlier in the same
       <a class="link" href="with.html" title="15.2.20 WITH (Common Table Expressions)">
        <code class="literal">
         WITH
        </code>
       </a>
       clause, but not those
            defined later.
      </p>
      <p>
       This constraint rules out mutually-recursive CTEs, where
       <code class="literal">
        cte1
       </code>
       references
       <code class="literal">
        cte2
       </code>
       and
       <code class="literal">
        cte2
       </code>
       references
       <code class="literal">
        cte1
       </code>
       . One of those references must be to
            a CTE defined later, which is not permitted.
      </p>
     </li>
     <li class="listitem">
      <p>
       A CTE in a given query block can refer to CTEs defined in
            query blocks at a more outer level, but not CTEs defined in
            query blocks at a more inner level.
      </p>
     </li>
    </ul>
   </div>
   <p>
    For resolving references to objects with the same names, derived
        tables hide CTEs; and CTEs hide base tables,
    <code class="literal">
     TEMPORARY
    </code>
    tables, and views. Name resolution
        occurs by searching for objects in the same query block, then
        proceeding to outer blocks in turn while no object with the name
        is found.
   </p>
   <p>
    For additional syntax considerations specific to recursive CTEs,
        see
    <a class="xref" href="with.html#common-table-expressions-recursive" title="Recursive Common Table Expressions">
     Recursive Common Table Expressions
    </a>
    .
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="common-table-expressions-recursive">
       </a>
       Recursive Common Table Expressions
      </h4>
     </div>
    </div>
   </div>
   <p>
    A recursive common table expression is one having a subquery
        that refers to its own name. For example:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa40900484"><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">WITH</span> <span class="token keyword">RECURSIVE</span> cte <span class="token punctuation">(</span>n<span class="token punctuation">)</span> <span class="token keyword">AS</span>
<span class="token punctuation">(</span>
  <span class="token keyword">SELECT</span> <span class="token number">1</span>
  <span class="token keyword">UNION</span> <span class="token keyword">ALL</span>
  <span class="token keyword">SELECT</span> n <span class="token operator">+</span> <span class="token number">1</span> <span class="token keyword">FROM</span> cte <span class="token keyword">WHERE</span> n <span class="token operator">&lt;</span> <span class="token number">5</span>
<span class="token punctuation">)</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> cte<span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    When executed, the statement produces this result, a single
        column containing a simple linear sequence:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-none"><div class="docs-select-all right" id="sa11621629"><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">+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+</code></pre>
   </div>
   <p>
    A recursive CTE has this structure:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       The
       <code class="literal">
        WITH
       </code>
       clause must begin with
       <code class="literal">
        WITH RECURSIVE
       </code>
       if any CTE in the
       <code class="literal">
        WITH
       </code>
       clause refers to itself. (If no CTE
            refers to itself,
       <code class="literal">
        RECURSIVE
       </code>
       is permitted
            but not required.)
      </p>
      <p>
       If you forget
       <code class="literal">
        RECURSIVE
       </code>
       for a recursive
            CTE, this error is a likely result:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-none"><div class="docs-select-all right" id="sa92576476"><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">ERROR 1146 (42S02): Table '<em class="replaceable">cte_name</em>' doesn't exist</code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       The recursive CTE subquery has two parts, separated by
       <a class="link" href="union.html" title="15.2.18 UNION Clause">
        <code class="literal">
         UNION ALL
        </code>
       </a>
       or
       <a class="link" href="union.html" title="15.2.18 UNION Clause">
        <code class="literal">
         UNION
            [DISTINCT]
        </code>
       </a>
       :
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa2865183"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>      <span class="token comment" spellcheck="true">-- return initial row set</span>
<span class="token keyword">UNION</span> <span class="token keyword">ALL</span>
<span class="token keyword">SELECT</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>      <span class="token comment" spellcheck="true">-- return additional row sets</span></code></pre>
      </div>
      <p>
       The first
       <a class="link" href="select.html" title="15.2.13 SELECT Statement">
        <code class="literal">
         SELECT
        </code>
       </a>
       produces the
            initial row or rows for the CTE and does not refer to the
            CTE name. The second
       <a class="link" href="select.html" title="15.2.13 SELECT Statement">
        <code class="literal">
         SELECT
        </code>
       </a>
       produces additional rows and recurses by referring to the
            CTE name in its
       <code class="literal">
        FROM
       </code>
       clause. Recursion
            ends when this part produces no new rows. Thus, a recursive
            CTE consists of a nonrecursive
       <a class="link" href="select.html" title="15.2.13 SELECT Statement">
        <code class="literal">
         SELECT
        </code>
       </a>
       part followed by a
            recursive
       <a class="link" href="select.html" title="15.2.13 SELECT Statement">
        <code class="literal">
         SELECT
        </code>
       </a>
       part.
      </p>
      <p>
       Each
       <a class="link" href="select.html" title="15.2.13 SELECT Statement">
        <code class="literal">
         SELECT
        </code>
       </a>
       part can itself
            be a union of multiple
       <a class="link" href="select.html" title="15.2.13 SELECT Statement">
        <code class="literal">
         SELECT
        </code>
       </a>
       statements.
      </p>
     </li>
     <li class="listitem">
      <p>
       The types of the CTE result columns are inferred from the
            column types of the nonrecursive
       <a class="link" href="select.html" title="15.2.13 SELECT Statement">
        <code class="literal">
         SELECT
        </code>
       </a>
       part only, and the
            columns are all nullable. For type determination, the
            recursive
       <a class="link" href="select.html" title="15.2.13 SELECT Statement">
        <code class="literal">
         SELECT
        </code>
       </a>
       part is
            ignored.
      </p>
     </li>
     <li class="listitem">
      <p>
       If the nonrecursive and recursive parts are separated by
       <a class="link" href="union.html" title="15.2.18 UNION Clause">
        <code class="literal">
         UNION
            DISTINCT
        </code>
       </a>
       , duplicate rows are eliminated. This is
            useful for queries that perform transitive closures, to
            avoid infinite loops.
      </p>
     </li>
     <li class="listitem">
      <p>
       Each iteration of the recursive part operates only on the
            rows produced by the previous iteration. If the recursive
            part has multiple query blocks, iterations of each query
            block are scheduled in unspecified order, and each query
            block operates on rows that have been produced either by its
            previous iteration or by other query blocks since that
            previous iteration's end.
      </p>
     </li>
    </ul>
   </div>
   <p>
    The recursive CTE subquery shown earlier has this nonrecursive
        part that retrieves a single row to produce the initial row set:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa23288940"><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 number">1</span></code></pre>
   </div>
   <p>
    The CTE subquery also has this recursive part:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa31955617"><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> n <span class="token operator">+</span> <span class="token number">1</span> <span class="token keyword">FROM</span> cte <span class="token keyword">WHERE</span> n <span class="token operator">&lt;</span> <span class="token number">5</span></code></pre>
   </div>
   <p>
    At each iteration, that
    <a class="link" href="select.html" title="15.2.13 SELECT Statement">
     <code class="literal">
      SELECT
     </code>
    </a>
    produces a row with a new value one greater than the value of
    <code class="literal">
     n
    </code>
    from the previous row set. The first
        iteration operates on the initial row set (
    <code class="literal">
     1
    </code>
    )
        and produces
    <code class="literal">
     1+1=2
    </code>
    ; the second iteration
        operates on the first iteration's row set (
    <code class="literal">
     2
    </code>
    )
        and produces
    <code class="literal">
     2+1=3
    </code>
    ; and so forth. This
        continues until recursion ends, which occurs when
    <code class="literal">
     n
    </code>
    is no longer less than 5.
   </p>
   <p>
    If the recursive part of a CTE produces wider values for a
        column than the nonrecursive part, it may be necessary to widen
        the column in the nonrecursive part to avoid data truncation.
        Consider this statement:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa25813355"><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">WITH</span> <span class="token keyword">RECURSIVE</span> cte <span class="token keyword">AS</span>
<span class="token punctuation">(</span>
  <span class="token keyword">SELECT</span> <span class="token number">1</span> <span class="token keyword">AS</span> n<span class="token punctuation">,</span> <span class="token string">'abc'</span> <span class="token keyword">AS</span> str
  <span class="token keyword">UNION</span> <span class="token keyword">ALL</span>
  <span class="token keyword">SELECT</span> n <span class="token operator">+</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token function">CONCAT</span><span class="token punctuation">(</span>str<span class="token punctuation">,</span> str<span class="token punctuation">)</span> <span class="token keyword">FROM</span> cte <span class="token keyword">WHERE</span> n <span class="token operator">&lt;</span> <span class="token number">3</span>
<span class="token punctuation">)</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> cte<span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    In nonstrict SQL mode, the statement produces this output:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-none"><div class="docs-select-all right" id="sa55773981"><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">+------+------+
| n    | str  |
+------+------+
|    1 | abc  |
|    2 | abc  |
|    3 | abc  |
+------+------+</code></pre>
   </div>
   <p>
    The
    <code class="literal">
     str
    </code>
    column values are all
    <code class="literal">
     'abc'
    </code>
    because the nonrecursive
    <a class="link" href="select.html" title="15.2.13 SELECT Statement">
     <code class="literal">
      SELECT
     </code>
    </a>
    determines the column
        widths. Consequently, the wider
    <code class="literal">
     str
    </code>
    values
        produced by the recursive
    <a class="link" href="select.html" title="15.2.13 SELECT Statement">
     <code class="literal">
      SELECT
     </code>
    </a>
    are truncated.
   </p>
   <p>
    In strict SQL mode, the statement produces an error:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-none"><div class="docs-select-all right" id="sa8066915"><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">ERROR 1406 (22001): Data too long for column 'str' at row 1</code></pre>
   </div>
   <p>
    To address this issue, so that the statement does not produce
        truncation or errors, use
    <a class="link" href="cast-functions.html#function_cast">
     <code class="literal">
      CAST()
     </code>
    </a>
    in the nonrecursive
    <a class="link" href="select.html" title="15.2.13 SELECT Statement">
     <code class="literal">
      SELECT
     </code>
    </a>
    to
        make the
    <code class="literal">
     str
    </code>
    column wider:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa46098292"><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">WITH</span> <span class="token keyword">RECURSIVE</span> cte <span class="token keyword">AS</span>
<span class="token punctuation">(</span>
  <span class="token keyword">SELECT</span> <span class="token number">1</span> <span class="token keyword">AS</span> n<span class="token punctuation">,</span> <span class="token function">CAST</span><span class="token punctuation">(</span><span class="token string">'abc'</span> <span class="token keyword">AS</span> <span class="token datatype">CHAR</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> str
  <span class="token keyword">UNION</span> <span class="token keyword">ALL</span>
  <span class="token keyword">SELECT</span> n <span class="token operator">+</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token function">CONCAT</span><span class="token punctuation">(</span>str<span class="token punctuation">,</span> str<span class="token punctuation">)</span> <span class="token keyword">FROM</span> cte <span class="token keyword">WHERE</span> n <span class="token operator">&lt;</span> <span class="token number">3</span>
<span class="token punctuation">)</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> cte<span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    Now the statement produces this result, without truncation:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-none"><div class="docs-select-all right" id="sa50040594"><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">+------+--------------+
| n    | str          |
+------+--------------+
|    1 | abc          |
|    2 | abcabc       |
|    3 | abcabcabcabc |
+------+--------------+</code></pre>
   </div>
   <p>
    Columns are accessed by name, not position, which means that
        columns in the recursive part can access columns in the
        nonrecursive part that have a different position, as this CTE
        illustrates:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa72888319"><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">WITH</span> <span class="token keyword">RECURSIVE</span> cte <span class="token keyword">AS</span>
<span class="token punctuation">(</span>
  <span class="token keyword">SELECT</span> <span class="token number">1</span> <span class="token keyword">AS</span> n<span class="token punctuation">,</span> <span class="token number">1</span> <span class="token keyword">AS</span> p<span class="token punctuation">,</span> <span class="token operator">-</span><span class="token number">1</span> <span class="token keyword">AS</span> q
  <span class="token keyword">UNION</span> <span class="token keyword">ALL</span>
  <span class="token keyword">SELECT</span> n <span class="token operator">+</span> <span class="token number">1</span><span class="token punctuation">,</span> q <span class="token operator">*</span> <span class="token number">2</span><span class="token punctuation">,</span> p <span class="token operator">*</span> <span class="token number">2</span> <span class="token keyword">FROM</span> cte <span class="token keyword">WHERE</span> n <span class="token operator">&lt;</span> <span class="token number">5</span>
<span class="token punctuation">)</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> cte<span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    Because
    <code class="literal">
     p
    </code>
    in one row is derived from
    <code class="literal">
     q
    </code>
    in the previous row, and vice versa, the
        positive and negative values swap positions in each successive
        row of the output:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-none"><div class="docs-select-all right" id="sa73772831"><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">+------+------+------+
| n    | p    | q    |
+------+------+------+
|    1 |    1 |   -1 |
|    2 |   -2 |    2 |
|    3 |    4 |   -4 |
|    4 |   -8 |    8 |
|    5 |   16 |  -16 |
+------+------+------+</code></pre>
   </div>
   <p>
    Some syntax constraints apply within recursive CTE subqueries:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       The recursive
       <a class="link" href="select.html" title="15.2.13 SELECT Statement">
        <code class="literal">
         SELECT
        </code>
       </a>
       part
            must not contain these constructs:
      </p>
      <div class="itemizedlist">
       <ul class="itemizedlist" style="list-style-type: circle; ">
        <li class="listitem">
         <p>
          Aggregate functions such as
          <code class="literal">
           SUM()
          </code>
         </p>
        </li>
        <li class="listitem">
         <p>
          Window functions
         </p>
        </li>
        <li class="listitem">
         <p>
          <code class="literal">
           GROUP BY
          </code>
         </p>
        </li>
        <li class="listitem">
         <p>
          <code class="literal">
           ORDER BY
          </code>
         </p>
        </li>
        <li class="listitem">
         <p>
          <code class="literal">
           DISTINCT
          </code>
         </p>
        </li>
       </ul>
      </div>
      <p>
       The recursive
       <a class="link" href="select.html" title="15.2.13 SELECT Statement">
        <code class="literal">
         SELECT
        </code>
       </a>
       part of
            a recursive CTE can also use a
       <code class="literal">
        LIMIT
       </code>
       clause, along with an optional
       <code class="literal">
        OFFSET
       </code>
       clause. The effect on the result set is the same as when
            using
       <code class="literal">
        LIMIT
       </code>
       in the outermost
       <code class="literal">
        SELECT
       </code>
       , but is also more efficient, since
            using it with the recursive
       <code class="literal">
        SELECT
       </code>
       stops
            the generation of rows as soon as the requested number of
            them has been produced.
      </p>
      <p>
       The prohibition on
       <code class="literal">
        DISTINCT
       </code>
       applies only
            to
       <a class="link" href="union.html" title="15.2.18 UNION Clause">
        <code class="literal">
         UNION
        </code>
       </a>
       members;
       <code class="literal">
        UNION DISTINCT
       </code>
       is permitted.
      </p>
     </li>
     <li class="listitem">
      <p>
       The recursive
       <a class="link" href="select.html" title="15.2.13 SELECT Statement">
        <code class="literal">
         SELECT
        </code>
       </a>
       part
            must reference the CTE only once and only in its
       <code class="literal">
        FROM
       </code>
       clause, not in any subquery. It can
            reference tables other than the CTE and join them with the
            CTE. If used in a join like this, the CTE must not be on the
            right side of a
       <code class="literal">
        LEFT JOIN
       </code>
       .
      </p>
     </li>
    </ul>
   </div>
   <p>
    These constraints come from the SQL standard, other than the
        MySQL-specific exclusions mentioned previously.
   </p>
   <p>
    For recursive CTEs,
    <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
     <code class="literal">
      EXPLAIN
     </code>
    </a>
    output rows for recursive
    <a class="link" href="select.html" title="15.2.13 SELECT Statement">
     <code class="literal">
      SELECT
     </code>
    </a>
    parts display
    <code class="literal">
     Recursive
    </code>
    in the
    <code class="literal">
     Extra
    </code>
    column.
   </p>
   <p>
    Cost estimates displayed by
    <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
     <code class="literal">
      EXPLAIN
     </code>
    </a>
    represent cost per
        iteration, which might differ considerably from total cost. The
        optimizer cannot predict the number of iterations because it
        cannot predict at what point the
    <code class="literal">
     WHERE
    </code>
    clause
        becomes false.
   </p>
   <p>
    CTE actual cost may also be affected by result set size. A CTE
        that produces many rows may require an internal temporary table
        large enough to be converted from in-memory to on-disk format
        and may suffer a performance penalty. If so, increasing the
        permitted in-memory temporary table size may improve
        performance; see
    <a class="xref" href="internal-temporary-tables.html" title="10.4.4 Internal Temporary Table Use in MySQL">
     Section 10.4.4, “Internal Temporary Table Use in MySQL”
    </a>
    .
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="common-table-expressions-recursion-limits">
       </a>
       Limiting Common Table Expression Recursion
      </h4>
     </div>
    </div>
   </div>
   <p>
    It is important for recursive CTEs that the recursive
    <a class="link" href="select.html" title="15.2.13 SELECT Statement">
     <code class="literal">
      SELECT
     </code>
    </a>
    part include a condition
        to terminate recursion. As a development technique to guard
        against a runaway recursive CTE, you can force termination by
        placing a limit on execution time:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       The
       <a class="link" href="server-system-variables.html#sysvar_cte_max_recursion_depth">
        <code class="literal">
         cte_max_recursion_depth
        </code>
       </a>
       system variable enforces a limit on the number of recursion
            levels for CTEs. The server terminates execution of any CTE
            that recurses more levels than the value of this variable.
      </p>
     </li>
     <li class="listitem">
      <p>
       The
       <a class="link" href="server-system-variables.html#sysvar_max_execution_time">
        <code class="literal">
         max_execution_time
        </code>
       </a>
       system variable enforces an execution timeout for
       <a class="link" href="select.html" title="15.2.13 SELECT Statement">
        <code class="literal">
         SELECT
        </code>
       </a>
       statements executed
            within the current session.
      </p>
     </li>
     <li class="listitem">
      <p>
       The
       <a class="link" href="optimizer-hints.html#optimizer-hints-execution-time" title="Statement Execution Time Optimizer Hints">
        <code class="literal">
         MAX_EXECUTION_TIME
        </code>
       </a>
       optimizer hint enforces a per-query execution timeout for
            the
       <a class="link" href="select.html" title="15.2.13 SELECT Statement">
        <code class="literal">
         SELECT
        </code>
       </a>
       statement in which
            it appears.
      </p>
     </li>
    </ul>
   </div>
   <p>
    Suppose that a recursive CTE is mistakenly written with no
        recursion execution termination condition:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa69040439"><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">WITH</span> <span class="token keyword">RECURSIVE</span> cte <span class="token punctuation">(</span>n<span class="token punctuation">)</span> <span class="token keyword">AS</span>
<span class="token punctuation">(</span>
  <span class="token keyword">SELECT</span> <span class="token number">1</span>
  <span class="token keyword">UNION</span> <span class="token keyword">ALL</span>
  <span class="token keyword">SELECT</span> n <span class="token operator">+</span> <span class="token number">1</span> <span class="token keyword">FROM</span> cte
<span class="token punctuation">)</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> cte<span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    By default,
    <a class="link" href="server-system-variables.html#sysvar_cte_max_recursion_depth">
     <code class="literal">
      cte_max_recursion_depth
     </code>
    </a>
    has a
        value of 1000, causing the CTE to terminate when it recurses
        past 1000 levels. Applications can change the session value to
        adjust for their requirements:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa3736616"><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">SET</span> <span class="token keyword">SESSION</span> cte_max_recursion_depth <span class="token operator">=</span> <span class="token number">10</span><span class="token punctuation">;</span>      <span class="token comment" spellcheck="true">-- permit only shallow recursion</span>
<span class="token keyword">SET</span> <span class="token keyword">SESSION</span> cte_max_recursion_depth <span class="token operator">=</span> <span class="token number">1000000</span><span class="token punctuation">;</span> <span class="token comment" spellcheck="true">-- permit deeper recursion</span></code></pre>
   </div>
   <p>
    You can also set the global
    <a class="link" href="server-system-variables.html#sysvar_cte_max_recursion_depth">
     <code class="literal">
      cte_max_recursion_depth
     </code>
    </a>
    value
        to affect all sessions that begin subsequently.
   </p>
   <p>
    For queries that execute and thus recurse slowly or in contexts
        for which there is reason to set the
    <a class="link" href="server-system-variables.html#sysvar_cte_max_recursion_depth">
     <code class="literal">
      cte_max_recursion_depth
     </code>
    </a>
    value
        very high, another way to guard against deep recursion is to set
        a per-session timeout. To do so, execute a statement like this
        prior to executing the CTE statement:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa63375844"><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">SET</span> max_execution_time <span class="token operator">=</span> <span class="token number">1000</span><span class="token punctuation">;</span> <span class="token comment" spellcheck="true">-- impose one second timeout</span></code></pre>
   </div>
   <p>
    Alternatively, include an optimizer hint within the CTE
        statement itself:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa5480715"><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">WITH</span> <span class="token keyword">RECURSIVE</span> cte <span class="token punctuation">(</span>n<span class="token punctuation">)</span> <span class="token keyword">AS</span>
<span class="token punctuation">(</span>
  <span class="token keyword">SELECT</span> <span class="token number">1</span>
  <span class="token keyword">UNION</span> <span class="token keyword">ALL</span>
  <span class="token keyword">SELECT</span> n <span class="token operator">+</span> <span class="token number">1</span> <span class="token keyword">FROM</span> cte
<span class="token punctuation">)</span>
<span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ SET_VAR(cte_max_recursion_depth = 1M) */</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> cte<span class="token punctuation">;</span>

<span class="token keyword">WITH</span> <span class="token keyword">RECURSIVE</span> cte <span class="token punctuation">(</span>n<span class="token punctuation">)</span> <span class="token keyword">AS</span>
<span class="token punctuation">(</span>
  <span class="token keyword">SELECT</span> <span class="token number">1</span>
  <span class="token keyword">UNION</span> <span class="token keyword">ALL</span>
  <span class="token keyword">SELECT</span> n <span class="token operator">+</span> <span class="token number">1</span> <span class="token keyword">FROM</span> cte
<span class="token punctuation">)</span>
<span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ MAX_EXECUTION_TIME(1000) */</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> cte<span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    You can also use
    <code class="literal">
     LIMIT
    </code>
    within the recursive
        query to impose a maximum number of rows to be returned to the
        outermost
    <a class="link" href="select.html" title="15.2.13 SELECT Statement">
     <code class="literal">
      SELECT
     </code>
    </a>
    , for example:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa71108748"><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">WITH</span> <span class="token keyword">RECURSIVE</span> cte <span class="token punctuation">(</span>n<span class="token punctuation">)</span> <span class="token keyword">AS</span>
<span class="token punctuation">(</span>
  <span class="token keyword">SELECT</span> <span class="token number">1</span>
  <span class="token keyword">UNION</span> <span class="token keyword">ALL</span>
  <span class="token keyword">SELECT</span> n <span class="token operator">+</span> <span class="token number">1</span> <span class="token keyword">FROM</span> cte <span class="token keyword">LIMIT</span> <span class="token number">10000</span>
<span class="token punctuation">)</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> cte<span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    You can do this in addition to or instead of setting a time
        limit. Thus, the following CTE terminates after returning ten
        thousand rows or running for one second (1000 milliseconds),
        whichever occurs first:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa5464861"><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">WITH</span> <span class="token keyword">RECURSIVE</span> cte <span class="token punctuation">(</span>n<span class="token punctuation">)</span> <span class="token keyword">AS</span>
<span class="token punctuation">(</span>
  <span class="token keyword">SELECT</span> <span class="token number">1</span>
  <span class="token keyword">UNION</span> <span class="token keyword">ALL</span>
  <span class="token keyword">SELECT</span> n <span class="token operator">+</span> <span class="token number">1</span> <span class="token keyword">FROM</span> cte <span class="token keyword">LIMIT</span> <span class="token number">10000</span>
<span class="token punctuation">)</span>
<span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ MAX_EXECUTION_TIME(1000) */</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> cte<span class="token punctuation">;</span></code></pre>
   </div>
   <a class="indexterm" name="idm46045179012944">
   </a>
   <a class="indexterm" name="idm46045179011456">
   </a>
   <p>
    If a recursive query without an execution time limit enters an
        infinite loop, you can terminate it from another session using
    <a class="link" href="kill.html" title="15.7.8.4 KILL Statement">
     <code class="literal">
      KILL QUERY
     </code>
    </a>
    .
        Within the session itself, the client program used to run the
        query might provide a way to kill the query. For example, in
    <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
     <span class="command">
      <strong>
       mysql
      </strong>
     </span>
    </a>
    , typing
    <span class="keycap">
     <strong>
      Control+C
     </strong>
    </span>
    interrupts the current statement.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="common-table-expressions-recursive-examples">
       </a>
       Recursive Common Table Expression Examples
      </h4>
     </div>
    </div>
   </div>
   <p>
    As mentioned previously, recursive common table expressions
        (CTEs) are frequently used for series generation and traversing
        hierarchical or tree-structured data. This section shows some
        simple examples of these techniques.
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <a class="xref" href="with.html#common-table-expressions-recursive-fibonacci-series" title="Fibonacci Series Generation">
        Fibonacci Series Generation
       </a>
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="xref" href="with.html#common-table-expressions-recursive-date-series" title="Date Series Generation">
        Date Series Generation
       </a>
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="xref" href="with.html#common-table-expressions-recursive-hierarchy-traversal" title="Hierarchical Data Traversal">
        Hierarchical Data Traversal
       </a>
      </p>
     </li>
    </ul>
   </div>
   <h5>
    <a name="common-table-expressions-recursive-fibonacci-series">
    </a>
    Fibonacci Series Generation
   </h5>
   <p>
    A Fibonacci series begins with the two numbers 0 and 1 (or 1 and
        1) and each number after that is the sum of the previous two
        numbers. A recursive common table expression can generate a
        Fibonacci series if each row produced by the recursive
    <a class="link" href="select.html" title="15.2.13 SELECT Statement">
     <code class="literal">
      SELECT
     </code>
    </a>
    has access to the two
        previous numbers from the series. The following CTE generates a
        10-number series using 0 and 1 as the first two numbers:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa72856105"><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">WITH</span> <span class="token keyword">RECURSIVE</span> fibonacci <span class="token punctuation">(</span>n<span class="token punctuation">,</span> fib_n<span class="token punctuation">,</span> next_fib_n<span class="token punctuation">)</span> <span class="token keyword">AS</span>
<span class="token punctuation">(</span>
  <span class="token keyword">SELECT</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">0</span><span class="token punctuation">,</span> <span class="token number">1</span>
  <span class="token keyword">UNION</span> <span class="token keyword">ALL</span>
  <span class="token keyword">SELECT</span> n <span class="token operator">+</span> <span class="token number">1</span><span class="token punctuation">,</span> next_fib_n<span class="token punctuation">,</span> fib_n <span class="token operator">+</span> next_fib_n
    <span class="token keyword">FROM</span> fibonacci <span class="token keyword">WHERE</span> n <span class="token operator">&lt;</span> <span class="token number">10</span>
<span class="token punctuation">)</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> fibonacci<span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    The CTE produces this result:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-none"><div class="docs-select-all right" id="sa17982288"><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">+------+-------+------------+
| n    | fib_n | next_fib_n |
+------+-------+------------+
|    1 |     0 |          1 |
|    2 |     1 |          1 |
|    3 |     1 |          2 |
|    4 |     2 |          3 |
|    5 |     3 |          5 |
|    6 |     5 |          8 |
|    7 |     8 |         13 |
|    8 |    13 |         21 |
|    9 |    21 |         34 |
|   10 |    34 |         55 |
+------+-------+------------+</code></pre>
   </div>
   <p>
    How the CTE works:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <code class="literal">
        n
       </code>
       is a display column to indicate that
            the row contains the
       <code class="literal">
        n
       </code>
       -th Fibonacci
            number. For example, the 8th Fibonacci number is 13.
      </p>
     </li>
     <li class="listitem">
      <p>
       The
       <code class="literal">
        fib_n
       </code>
       column displays Fibonacci
            number
       <code class="literal">
        n
       </code>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       The
       <code class="literal">
        next_fib_n
       </code>
       column displays the next
            Fibonacci number after number
       <code class="literal">
        n
       </code>
       . This
            column provides the next series value to the next row, so
            that row can produce the sum of the two previous series
            values in its
       <code class="literal">
        fib_n
       </code>
       column.
      </p>
     </li>
     <li class="listitem">
      <p>
       Recursion ends when
       <code class="literal">
        n
       </code>
       reaches 10. This is
            an arbitrary choice, to limit the output to a small set of
            rows.
      </p>
     </li>
    </ul>
   </div>
   <p>
    The preceding output shows the entire CTE result. To select just
        part of it, add an appropriate
    <code class="literal">
     WHERE
    </code>
    clause
        to the top-level
    <a class="link" href="select.html" title="15.2.13 SELECT Statement">
     <code class="literal">
      SELECT
     </code>
    </a>
    . For
        example, to select the 8th Fibonacci number, do this:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa72431031"><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">WITH</span> <span class="token keyword">RECURSIVE</span> fibonacci <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
       <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
       <span class="token keyword">SELECT</span> fib_n <span class="token keyword">FROM</span> fibonacci <span class="token keyword">WHERE</span> n <span class="token operator">=</span> <span class="token number">8</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>
<span class="token output"><span class="token punctuation">|</span> fib_n <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>
<span class="token output"><span class="token punctuation">|</span>    13 <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></code></pre>
   </div>
   <h5>
    <a name="common-table-expressions-recursive-date-series">
    </a>
    Date Series Generation
   </h5>
   <p>
    A common table expression can generate a series of successive
        dates, which is useful for generating summaries that include a
        row for all dates in the series, including dates not represented
        in the summarized data.
   </p>
   <p>
    Suppose that a table of sales numbers contains these rows:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa37399719"><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> sales <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token datatype">date</span><span class="token punctuation">,</span> price<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>
<span class="token output"><span class="token punctuation">|</span> date       <span class="token punctuation">|</span> price  <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>
<span class="token output"><span class="token punctuation">|</span> 2017<span class="token punctuation">-</span>01<span class="token punctuation">-</span>03 <span class="token punctuation">|</span> 100.00 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2017<span class="token punctuation">-</span>01<span class="token punctuation">-</span>03 <span class="token punctuation">|</span> 200.00 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2017<span class="token punctuation">-</span>01<span class="token punctuation">-</span>06 <span class="token punctuation">|</span>  50.00 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2017<span class="token punctuation">-</span>01<span class="token punctuation">-</span>08 <span class="token punctuation">|</span>  10.00 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2017<span class="token punctuation">-</span>01<span class="token punctuation">-</span>08 <span class="token punctuation">|</span>  20.00 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2017<span class="token punctuation">-</span>01<span class="token punctuation">-</span>08 <span class="token punctuation">|</span> 150.00 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2017<span class="token punctuation">-</span>01<span class="token punctuation">-</span>10 <span class="token punctuation">|</span>   5.00 <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></code></pre>
   </div>
   <p>
    This query summarizes the sales per day:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa44738455"><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 datatype">date</span><span class="token punctuation">,</span> <span class="token function">SUM</span><span class="token punctuation">(</span>price<span class="token punctuation">)</span> <span class="token keyword">AS</span> sum_price
       <span class="token keyword">FROM</span> sales
       <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> <span class="token datatype">date</span>
       <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token datatype">date</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>
<span class="token output"><span class="token punctuation">|</span> date       <span class="token punctuation">|</span> sum_price <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>
<span class="token output"><span class="token punctuation">|</span> 2017<span class="token punctuation">-</span>01<span class="token punctuation">-</span>03 <span class="token punctuation">|</span>    300.00 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2017<span class="token punctuation">-</span>01<span class="token punctuation">-</span>06 <span class="token punctuation">|</span>     50.00 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2017<span class="token punctuation">-</span>01<span class="token punctuation">-</span>08 <span class="token punctuation">|</span>    180.00 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2017<span class="token punctuation">-</span>01<span class="token punctuation">-</span>10 <span class="token punctuation">|</span>      5.00 <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></code></pre>
   </div>
   <p>
    However, that result contains
    <span class="quote">
     “
     <span class="quote">
      holes
     </span>
     ”
    </span>
    for dates not
        represented in the range of dates spanned by the table. A result
        that represents all dates in the range can be produced using a
        recursive CTE to generate that set of dates, joined with a
    <code class="literal">
     LEFT JOIN
    </code>
    to the sales data.
   </p>
   <p>
    Here is the CTE to generate the date range series:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa81659329"><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">WITH</span> <span class="token keyword">RECURSIVE</span> dates <span class="token punctuation">(</span><span class="token datatype">date</span><span class="token punctuation">)</span> <span class="token keyword">AS</span>
<span class="token punctuation">(</span>
  <span class="token keyword">SELECT</span> <span class="token function">MIN</span><span class="token punctuation">(</span><span class="token datatype">date</span><span class="token punctuation">)</span> <span class="token keyword">FROM</span> sales
  <span class="token keyword">UNION</span> <span class="token keyword">ALL</span>
  <span class="token keyword">SELECT</span> <span class="token datatype">date</span> <span class="token operator">+</span> <span class="token keyword">INTERVAL</span> <span class="token number">1</span> <span class="token keyword">DAY</span> <span class="token keyword">FROM</span> dates
  <span class="token keyword">WHERE</span> <span class="token datatype">date</span> <span class="token operator">+</span> <span class="token keyword">INTERVAL</span> <span class="token number">1</span> <span class="token keyword">DAY</span> <span class="token operator">&lt;=</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token function">MAX</span><span class="token punctuation">(</span><span class="token datatype">date</span><span class="token punctuation">)</span> <span class="token keyword">FROM</span> sales<span class="token punctuation">)</span>
<span class="token punctuation">)</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> dates<span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    The CTE produces this result:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-none"><div class="docs-select-all right" id="sa18744047"><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">+------------+
| date       |
+------------+
| 2017-01-03 |
| 2017-01-04 |
| 2017-01-05 |
| 2017-01-06 |
| 2017-01-07 |
| 2017-01-08 |
| 2017-01-09 |
| 2017-01-10 |
+------------+</code></pre>
   </div>
   <p>
    How the CTE works:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       The nonrecursive
       <a class="link" href="select.html" title="15.2.13 SELECT Statement">
        <code class="literal">
         SELECT
        </code>
       </a>
       produces the lowest date in the date range spanned by the
       <code class="literal">
        sales
       </code>
       table.
      </p>
     </li>
     <li class="listitem">
      <p>
       Each row produced by the recursive
       <a class="link" href="select.html" title="15.2.13 SELECT Statement">
        <code class="literal">
         SELECT
        </code>
       </a>
       adds one day to the
            date produced by the previous row.
      </p>
     </li>
     <li class="listitem">
      <p>
       Recursion ends after the dates reach the highest date in the
            date range spanned by the
       <code class="literal">
        sales
       </code>
       table.
      </p>
     </li>
    </ul>
   </div>
   <p>
    Joining the CTE with a
    <code class="literal">
     LEFT JOIN
    </code>
    against the
    <code class="literal">
     sales
    </code>
    table produces the sales summary with a
        row for each date in the range:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa21718369"><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">WITH</span> <span class="token keyword">RECURSIVE</span> dates <span class="token punctuation">(</span><span class="token datatype">date</span><span class="token punctuation">)</span> <span class="token keyword">AS</span>
<span class="token punctuation">(</span>
  <span class="token keyword">SELECT</span> <span class="token function">MIN</span><span class="token punctuation">(</span><span class="token datatype">date</span><span class="token punctuation">)</span> <span class="token keyword">FROM</span> sales
  <span class="token keyword">UNION</span> <span class="token keyword">ALL</span>
  <span class="token keyword">SELECT</span> <span class="token datatype">date</span> <span class="token operator">+</span> <span class="token keyword">INTERVAL</span> <span class="token number">1</span> <span class="token keyword">DAY</span> <span class="token keyword">FROM</span> dates
  <span class="token keyword">WHERE</span> <span class="token datatype">date</span> <span class="token operator">+</span> <span class="token keyword">INTERVAL</span> <span class="token number">1</span> <span class="token keyword">DAY</span> <span class="token operator">&lt;=</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token function">MAX</span><span class="token punctuation">(</span><span class="token datatype">date</span><span class="token punctuation">)</span> <span class="token keyword">FROM</span> sales<span class="token punctuation">)</span>
<span class="token punctuation">)</span>
<span class="token keyword">SELECT</span> dates<span class="token punctuation">.</span><span class="token datatype">date</span><span class="token punctuation">,</span> <span class="token function">COALESCE</span><span class="token punctuation">(</span><span class="token function">SUM</span><span class="token punctuation">(</span>price<span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token number">0</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> sum_price
<span class="token keyword">FROM</span> dates <span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> sales <span class="token keyword">ON</span> dates<span class="token punctuation">.</span><span class="token datatype">date</span> <span class="token operator">=</span> sales<span class="token punctuation">.</span><span class="token datatype">date</span>
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> dates<span class="token punctuation">.</span><span class="token datatype">date</span>
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> dates<span class="token punctuation">.</span><span class="token datatype">date</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    The output looks like this:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-none"><div class="docs-select-all right" id="sa40731376"><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">+------------+-----------+
| date       | sum_price |
+------------+-----------+
| 2017-01-03 |    300.00 |
| 2017-01-04 |      0.00 |
| 2017-01-05 |      0.00 |
| 2017-01-06 |     50.00 |
| 2017-01-07 |      0.00 |
| 2017-01-08 |    180.00 |
| 2017-01-09 |      0.00 |
| 2017-01-10 |      5.00 |
+------------+-----------+</code></pre>
   </div>
   <p>
    Some points to note:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Are the queries inefficient, particularly the one with the
       <a class="link" href="aggregate-functions.html#function_max">
        <code class="literal">
         MAX()
        </code>
       </a>
       subquery executed for
            each row in the recursive
       <a class="link" href="select.html" title="15.2.13 SELECT Statement">
        <code class="literal">
         SELECT
        </code>
       </a>
       ?
       <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
        <code class="literal">
         EXPLAIN
        </code>
       </a>
       shows that the
            subquery containing
       <a class="link" href="aggregate-functions.html#function_max">
        <code class="literal">
         MAX()
        </code>
       </a>
       is
            evaluated only once and the result is cached.
      </p>
     </li>
     <li class="listitem">
      <p>
       The use of
       <a class="link" href="comparison-operators.html#function_coalesce">
        <code class="literal">
         COALESCE()
        </code>
       </a>
       avoids
            displaying
       <code class="literal">
        NULL
       </code>
       in the
       <code class="literal">
        sum_price
       </code>
       column on days for which no
            sales data occur in the
       <code class="literal">
        sales
       </code>
       table.
      </p>
     </li>
    </ul>
   </div>
   <h5>
    <a name="common-table-expressions-recursive-hierarchy-traversal">
    </a>
    Hierarchical Data Traversal
   </h5>
   <p>
    Recursive common table expressions are useful for traversing
        data that forms a hierarchy. Consider these statements that
        create a small data set that shows, for each employee in a
        company, the employee name and ID number, and the ID of the
        employee's manager. The top-level employee (the CEO), has a
        manager ID of
    <code class="literal">
     NULL
    </code>
    (no manager).
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa51828502"><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">CREATE</span> <span class="token keyword">TABLE</span> employees <span class="token punctuation">(</span>
  id         <span class="token datatype">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
  <span class="token keyword">name</span>       <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
  manager_id <span class="token datatype">INT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
  <span class="token keyword">INDEX</span> <span class="token punctuation">(</span>manager_id<span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token keyword">FOREIGN</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>manager_id<span class="token punctuation">)</span> <span class="token keyword">REFERENCES</span> employees <span class="token punctuation">(</span>id<span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> employees <span class="token keyword">VALUES</span>
<span class="token punctuation">(</span><span class="token number">333</span><span class="token punctuation">,</span> <span class="token string">"Yasmina"</span><span class="token punctuation">,</span> <span class="token boolean">NULL</span><span class="token punctuation">)</span><span class="token punctuation">,</span>  <span class="token comment" spellcheck="true"># Yasmina is the CEO (manager_id is NULL)</span>
<span class="token punctuation">(</span><span class="token number">198</span><span class="token punctuation">,</span> <span class="token string">"John"</span><span class="token punctuation">,</span> <span class="token number">333</span><span class="token punctuation">)</span><span class="token punctuation">,</span>      <span class="token comment" spellcheck="true"># John has ID 198 and reports to 333 (Yasmina)</span>
<span class="token punctuation">(</span><span class="token number">692</span><span class="token punctuation">,</span> <span class="token string">"Tarek"</span><span class="token punctuation">,</span> <span class="token number">333</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">29</span><span class="token punctuation">,</span> <span class="token string">"Pedro"</span><span class="token punctuation">,</span> <span class="token number">198</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">4610</span><span class="token punctuation">,</span> <span class="token string">"Sarah"</span><span class="token punctuation">,</span> <span class="token number">29</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">72</span><span class="token punctuation">,</span> <span class="token string">"Pierre"</span><span class="token punctuation">,</span> <span class="token number">29</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">123</span><span class="token punctuation">,</span> <span class="token string">"Adil"</span><span class="token punctuation">,</span> <span class="token number">692</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    The resulting data set looks like this:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa67534927"><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> employees <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> id<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 class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> id   <span class="token punctuation">|</span> name    <span class="token punctuation">|</span> manager_id <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 class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>   29 <span class="token punctuation">|</span> Pedro   <span class="token punctuation">|</span>        198 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>   72 <span class="token punctuation">|</span> Pierre  <span class="token punctuation">|</span>         29 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  123 <span class="token punctuation">|</span> Adil    <span class="token punctuation">|</span>        692 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  198 <span class="token punctuation">|</span> John    <span class="token punctuation">|</span>        333 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  333 <span class="token punctuation">|</span> Yasmina <span class="token punctuation">|</span>       NULL <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  692 <span class="token punctuation">|</span> Tarek   <span class="token punctuation">|</span>        333 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 4610 <span class="token punctuation">|</span> Sarah   <span class="token punctuation">|</span>         29 <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 class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
   </div>
   <p>
    To produce the organizational chart with the management chain
        for each employee (that is, the path from CEO to employee), use
        a recursive CTE:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa50874707"><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">WITH</span> <span class="token keyword">RECURSIVE</span> employee_paths <span class="token punctuation">(</span>id<span class="token punctuation">,</span> <span class="token keyword">name</span><span class="token punctuation">,</span> <span class="token keyword">path</span><span class="token punctuation">)</span> <span class="token keyword">AS</span>
<span class="token punctuation">(</span>
  <span class="token keyword">SELECT</span> id<span class="token punctuation">,</span> <span class="token keyword">name</span><span class="token punctuation">,</span> <span class="token function">CAST</span><span class="token punctuation">(</span>id <span class="token keyword">AS</span> <span class="token datatype">CHAR</span><span class="token punctuation">(</span><span class="token number">200</span><span class="token punctuation">)</span><span class="token punctuation">)</span>
    <span class="token keyword">FROM</span> employees
    <span class="token keyword">WHERE</span> manager_id <span class="token operator">IS</span> <span class="token boolean">NULL</span>
  <span class="token keyword">UNION</span> <span class="token keyword">ALL</span>
  <span class="token keyword">SELECT</span> e<span class="token punctuation">.</span>id<span class="token punctuation">,</span> e<span class="token punctuation">.</span><span class="token keyword">name</span><span class="token punctuation">,</span> <span class="token function">CONCAT</span><span class="token punctuation">(</span>ep<span class="token punctuation">.</span><span class="token keyword">path</span><span class="token punctuation">,</span> <span class="token string">','</span><span class="token punctuation">,</span> e<span class="token punctuation">.</span>id<span class="token punctuation">)</span>
    <span class="token keyword">FROM</span> employee_paths <span class="token keyword">AS</span> ep <span class="token keyword">JOIN</span> employees <span class="token keyword">AS</span> e
      <span class="token keyword">ON</span> ep<span class="token punctuation">.</span>id <span class="token operator">=</span> e<span class="token punctuation">.</span>manager_id
<span class="token punctuation">)</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> employee_paths <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token keyword">path</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    The CTE produces this output:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-none"><div class="docs-select-all right" id="sa2569499"><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">+------+---------+-----------------+
| id   | name    | path            |
+------+---------+-----------------+
|  333 | Yasmina | 333             |
|  198 | John    | 333,198         |
|   29 | Pedro   | 333,198,29      |
| 4610 | Sarah   | 333,198,29,4610 |
|   72 | Pierre  | 333,198,29,72   |
|  692 | Tarek   | 333,692         |
|  123 | Adil    | 333,692,123     |
+------+---------+-----------------+</code></pre>
   </div>
   <p>
    How the CTE works:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       The nonrecursive
       <a class="link" href="select.html" title="15.2.13 SELECT Statement">
        <code class="literal">
         SELECT
        </code>
       </a>
       produces the row for the CEO (the row with a
       <code class="literal">
        NULL
       </code>
       manager ID).
      </p>
      <p>
       The
       <code class="literal">
        path
       </code>
       column is widened to
       <code class="literal">
        CHAR(200)
       </code>
       to ensure that there is room
            for the longer
       <code class="literal">
        path
       </code>
       values produced by
            the recursive
       <a class="link" href="select.html" title="15.2.13 SELECT Statement">
        <code class="literal">
         SELECT
        </code>
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       Each row produced by the recursive
       <a class="link" href="select.html" title="15.2.13 SELECT Statement">
        <code class="literal">
         SELECT
        </code>
       </a>
       finds all employees
            who report directly to an employee produced by a previous
            row. For each such employee, the row includes the employee
            ID and name, and the employee management chain. The chain is
            the manager's chain, with the employee ID added to the end.
      </p>
     </li>
     <li class="listitem">
      <p>
       Recursion ends when employees have no others who report to
            them.
      </p>
     </li>
    </ul>
   </div>
   <p>
    To find the path for a specific employee or employees, add a
    <code class="literal">
     WHERE
    </code>
    clause to the top-level
    <a class="link" href="select.html" title="15.2.13 SELECT Statement">
     <code class="literal">
      SELECT
     </code>
    </a>
    . For example, to display
        the results for Tarek and Sarah, modify that
    <a class="link" href="select.html" title="15.2.13 SELECT Statement">
     <code class="literal">
      SELECT
     </code>
    </a>
    like this:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa3843350"><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">WITH</span> <span class="token keyword">RECURSIVE</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
       <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
       <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> employees_extended
       <span class="token keyword">WHERE</span> id <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token number">692</span><span class="token punctuation">,</span> <span class="token number">4610</span><span class="token punctuation">)</span>
       <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token keyword">path</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 class="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> id   <span class="token punctuation">|</span> name  <span class="token punctuation">|</span> path            <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 class="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> 4610 <span class="token punctuation">|</span> Sarah <span class="token punctuation">|</span> 333,198,29,4610 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  692 <span class="token punctuation">|</span> Tarek <span class="token punctuation">|</span> 333,692         <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 class="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>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="common-table-expressions-similar-constructs">
       </a>
       Common Table Expressions Compared to Similar Constructs
      </h4>
     </div>
    </div>
   </div>
   <p>
    Common table expressions (CTEs) are similar to derived tables in
        some ways:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Both constructs are named.
      </p>
     </li>
     <li class="listitem">
      <p>
       Both constructs exist for the scope of a single statement.
      </p>
     </li>
    </ul>
   </div>
   <p>
    Because of these similarities, CTEs and derived tables often can
        be used interchangeably. As a trivial example, these statements
        are equivalent:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa10390009"><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">WITH</span> cte <span class="token keyword">AS</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token number">1</span><span class="token punctuation">)</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> cte<span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token number">1</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> dt<span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    However, CTEs have some advantages over derived tables:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       A derived table can be referenced only a single time within
            a query. A CTE can be referenced multiple times. To use
            multiple instances of a derived table result, you must
            derive the result multiple times.
      </p>
     </li>
     <li class="listitem">
      <p>
       A CTE can be self-referencing (recursive).
      </p>
     </li>
     <li class="listitem">
      <p>
       One CTE can refer to another.
      </p>
     </li>
     <li class="listitem">
      <p>
       A CTE may be easier to read when its definition appears at
            the beginning of the statement rather than embedded within
            it.
      </p>
     </li>
    </ul>
   </div>
   <p>
    CTEs are similar to tables created with
    <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
     <code class="literal">
      CREATE [TEMPORARY]
        TABLE
     </code>
    </a>
    but need not be defined or dropped explicitly.
        For a CTE, you need no privileges to create tables.
   </p>
  </div>
 </div>
 <br/>
</div>
