<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="lateral-derived-tables">
      </a>
      15.2.15.9 Lateral Derived Tables
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045179695792">
  </a>
  <a class="indexterm" name="idm46045179694304">
  </a>
  <p>
   A derived table cannot normally refer to (depend on) columns of
        preceding tables in the same
   <code class="literal">
    FROM
   </code>
   clause. A
        derived table may be defined as a lateral derived table to
        specify that such references are permitted.
  </p>
  <p>
   Nonlateral derived tables are specified using the syntax
        discussed in
   <a class="xref" href="derived-tables.html" title="15.2.15.8 Derived Tables">
    Section 15.2.15.8, “Derived Tables”
   </a>
   . The syntax for a
        lateral derived table is the same as for a nonlateral derived
        table except that the keyword
   <code class="literal">
    LATERAL
   </code>
   is
        specified before the derived table specification. The
   <code class="literal">
    LATERAL
   </code>
   keyword must precede each table to be
        used as a lateral derived table.
  </p>
  <p>
   Lateral derived tables are subject to these restrictions:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      A lateral derived table can occur only in a
      <code class="literal">
       FROM
      </code>
      clause, either in a list of tables
            separated with commas or in a join specification
            (
      <code class="literal">
       JOIN
      </code>
      ,
      <code class="literal">
       INNER JOIN
      </code>
      ,
      <code class="literal">
       CROSS JOIN
      </code>
      ,
      <code class="literal">
       LEFT [OUTER]
            JOIN
      </code>
      , or
      <code class="literal">
       RIGHT [OUTER] JOIN
      </code>
      ).
     </p>
    </li>
    <li class="listitem">
     <p>
      If a lateral derived table is in the right operand of a join
            clause and contains a reference to the left operand, the
            join operation must be an
      <code class="literal">
       INNER JOIN
      </code>
      ,
      <code class="literal">
       CROSS JOIN
      </code>
      , or
      <code class="literal">
       LEFT [OUTER]
            JOIN
      </code>
      .
     </p>
     <p>
      If the table is in the left operand and contains a reference
            to the right operand, the join operation must be an
      <code class="literal">
       INNER JOIN
      </code>
      ,
      <code class="literal">
       CROSS
            JOIN
      </code>
      , or
      <code class="literal">
       RIGHT [OUTER] JOIN
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      If a lateral derived table references an aggregate function,
            the function's aggregation query cannot be the one that owns
            the
      <code class="literal">
       FROM
      </code>
      clause in which the lateral
            derived table occurs.
     </p>
    </li>
    <li class="listitem">
     <p>
      In accordance with the SQL standard, MySQL always treats a
            join with a table function such as
      <a class="link" href="json-table-functions.html#function_json-table">
       <code class="literal">
        JSON_TABLE()
       </code>
      </a>
      as though
      <code class="literal">
       LATERAL
      </code>
      had been used. Since the
      <code class="literal">
       LATERAL
      </code>
      keyword is implicit, it is not
            allowed before
      <code class="literal">
       JSON_TABLE()
      </code>
      ; this is also
            according to the SQL standard.
     </p>
    </li>
   </ul>
  </div>
  <p>
   The following discussion shows how lateral derived tables make
        possible certain SQL operations that cannot be done with
        nonlateral derived tables or that require less-efficient
        workarounds.
  </p>
  <p>
   Suppose that we want to solve this problem: Given a table of
        people in a sales force (where each row describes a member of
        the sales force), and a table of all sales (where each row
        describes a sale: salesperson, customer, amount, date),
        determine the size and customer of the largest sale for each
        salesperson. This problem can be approached two ways.
  </p>
  <p>
   First approach to solving the problem: For each salesperson,
        calculate the maximum sale size, and also find the customer who
        provided this maximum. In MySQL, that can be done like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa71762374"><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>
  salesperson<span class="token punctuation">.</span><span class="token keyword">name</span><span class="token punctuation">,</span>
  <span class="token comment" spellcheck="true">-- find maximum sale size for this salesperson</span>
  <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token function">MAX</span><span class="token punctuation">(</span>amount<span class="token punctuation">)</span> <span class="token keyword">AS</span> amount
    <span class="token keyword">FROM</span> all_sales
    <span class="token keyword">WHERE</span> all_sales<span class="token punctuation">.</span>salesperson_id <span class="token operator">=</span> salesperson<span class="token punctuation">.</span>id<span class="token punctuation">)</span>
  <span class="token keyword">AS</span> amount<span class="token punctuation">,</span>
  <span class="token comment" spellcheck="true">-- find customer for this maximum size</span>
  <span class="token punctuation">(</span><span class="token keyword">SELECT</span> customer_name
    <span class="token keyword">FROM</span> all_sales
    <span class="token keyword">WHERE</span> all_sales<span class="token punctuation">.</span>salesperson_id <span class="token operator">=</span> salesperson<span class="token punctuation">.</span>id
    <span class="token operator">AND</span> all_sales<span class="token punctuation">.</span>amount <span class="token operator">=</span>
         <span class="token comment" spellcheck="true">-- find maximum size, again</span>
         <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token function">MAX</span><span class="token punctuation">(</span>amount<span class="token punctuation">)</span> <span class="token keyword">AS</span> amount
           <span class="token keyword">FROM</span> all_sales
           <span class="token keyword">WHERE</span> all_sales<span class="token punctuation">.</span>salesperson_id <span class="token operator">=</span> salesperson<span class="token punctuation">.</span>id<span class="token punctuation">)</span><span class="token punctuation">)</span>
  <span class="token keyword">AS</span> customer_name
<span class="token keyword">FROM</span>
  salesperson<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   That query is inefficient because it calculates the maximum size
        twice per salesperson (once in the first subquery and once in
        the second).
  </p>
  <p>
   We can try to achieve an efficiency gain by calculating the
        maximum once per salesperson and
   <span class="quote">
    “
    <span class="quote">
     caching
    </span>
    ”
   </span>
   it in a
        derived table, as shown by this modified query:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa92367872"><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>
  salesperson<span class="token punctuation">.</span><span class="token keyword">name</span><span class="token punctuation">,</span>
  max_sale<span class="token punctuation">.</span>amount<span class="token punctuation">,</span>
  max_sale_customer<span class="token punctuation">.</span>customer_name
<span class="token keyword">FROM</span>
  salesperson<span class="token punctuation">,</span>
  <span class="token comment" spellcheck="true">-- calculate maximum size, cache it in transient derived table max_sale</span>
  <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token function">MAX</span><span class="token punctuation">(</span>amount<span class="token punctuation">)</span> <span class="token keyword">AS</span> amount
    <span class="token keyword">FROM</span> all_sales
    <span class="token keyword">WHERE</span> all_sales<span class="token punctuation">.</span>salesperson_id <span class="token operator">=</span> salesperson<span class="token punctuation">.</span>id<span class="token punctuation">)</span>
  <span class="token keyword">AS</span> max_sale<span class="token punctuation">,</span>
  <span class="token comment" spellcheck="true">-- find customer, reusing cached maximum size</span>
  <span class="token punctuation">(</span><span class="token keyword">SELECT</span> customer_name
    <span class="token keyword">FROM</span> all_sales
    <span class="token keyword">WHERE</span> all_sales<span class="token punctuation">.</span>salesperson_id <span class="token operator">=</span> salesperson<span class="token punctuation">.</span>id
    <span class="token operator">AND</span> all_sales<span class="token punctuation">.</span>amount <span class="token operator">=</span>
        <span class="token comment" spellcheck="true">-- the cached maximum size</span>
        max_sale<span class="token punctuation">.</span>amount<span class="token punctuation">)</span>
  <span class="token keyword">AS</span> max_sale_customer<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   However, the query is illegal in SQL-92 because derived tables
        cannot depend on other tables in the same
   <code class="literal">
    FROM
   </code>
   clause. Derived tables must be constant
        over the query's duration, not contain references to columns of
        other
   <code class="literal">
    FROM
   </code>
   clause tables. As written, the
        query produces this error:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-none"><div class="docs-select-all right" id="sa88614592"><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 1054 (42S22): Unknown column 'salesperson.id' in 'where clause'</code></pre>
  </div>
  <p>
   In SQL:1999, the query becomes legal if the derived tables are
        preceded by the
   <code class="literal">
    LATERAL
   </code>
   keyword (which means
   <span class="quote">
    “
    <span class="quote">
     this derived table depends on previous tables on its left
        side
    </span>
    ”
   </span>
   ):
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa24628487"><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>
  salesperson<span class="token punctuation">.</span><span class="token keyword">name</span><span class="token punctuation">,</span>
  max_sale<span class="token punctuation">.</span>amount<span class="token punctuation">,</span>
  max_sale_customer<span class="token punctuation">.</span>customer_name
<span class="token keyword">FROM</span>
  salesperson<span class="token punctuation">,</span>
  <span class="token comment" spellcheck="true">-- calculate maximum size, cache it in transient derived table max_sale</span>
  <span class="token keyword">LATERAL</span>
  <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token function">MAX</span><span class="token punctuation">(</span>amount<span class="token punctuation">)</span> <span class="token keyword">AS</span> amount
    <span class="token keyword">FROM</span> all_sales
    <span class="token keyword">WHERE</span> all_sales<span class="token punctuation">.</span>salesperson_id <span class="token operator">=</span> salesperson<span class="token punctuation">.</span>id<span class="token punctuation">)</span>
  <span class="token keyword">AS</span> max_sale<span class="token punctuation">,</span>
  <span class="token comment" spellcheck="true">-- find customer, reusing cached maximum size</span>
  <span class="token keyword">LATERAL</span>
  <span class="token punctuation">(</span><span class="token keyword">SELECT</span> customer_name
    <span class="token keyword">FROM</span> all_sales
    <span class="token keyword">WHERE</span> all_sales<span class="token punctuation">.</span>salesperson_id <span class="token operator">=</span> salesperson<span class="token punctuation">.</span>id
    <span class="token operator">AND</span> all_sales<span class="token punctuation">.</span>amount <span class="token operator">=</span>
        <span class="token comment" spellcheck="true">-- the cached maximum size</span>
        max_sale<span class="token punctuation">.</span>amount<span class="token punctuation">)</span>
  <span class="token keyword">AS</span> max_sale_customer<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   A lateral derived table need not be constant and is brought up
        to date each time a new row from a preceding table on which it
        depends is processed by the top query.
  </p>
  <p>
   Second approach to solving the problem: A different solution
        could be used if a subquery in the
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   list could return multiple
        columns:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa66160852"><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>
  salesperson<span class="token punctuation">.</span><span class="token keyword">name</span><span class="token punctuation">,</span>
  <span class="token comment" spellcheck="true">-- find maximum size and customer at same time</span>
  <span class="token punctuation">(</span><span class="token keyword">SELECT</span> amount<span class="token punctuation">,</span> customer_name
    <span class="token keyword">FROM</span> all_sales
    <span class="token keyword">WHERE</span> all_sales<span class="token punctuation">.</span>salesperson_id <span class="token operator">=</span> salesperson<span class="token punctuation">.</span>id
    <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> amount <span class="token keyword">DESC</span> <span class="token keyword">LIMIT</span> <span class="token number">1</span><span class="token punctuation">)</span>
<span class="token keyword">FROM</span>
  salesperson<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   That is efficient but illegal. It does not work because such
        subqueries can return only a single column:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-none"><div class="docs-select-all right" id="sa74049893"><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 1241 (21000): Operand should contain 1 column(s)</code></pre>
  </div>
  <p>
   One attempt at rewriting the query is to select multiple columns
        from a derived table:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa69865235"><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>
  salesperson<span class="token punctuation">.</span><span class="token keyword">name</span><span class="token punctuation">,</span>
  max_sale<span class="token punctuation">.</span>amount<span class="token punctuation">,</span>
  max_sale<span class="token punctuation">.</span>customer_name
<span class="token keyword">FROM</span>
  salesperson<span class="token punctuation">,</span>
  <span class="token comment" spellcheck="true">-- find maximum size and customer at same time</span>
  <span class="token punctuation">(</span><span class="token keyword">SELECT</span> amount<span class="token punctuation">,</span> customer_name
    <span class="token keyword">FROM</span> all_sales
    <span class="token keyword">WHERE</span> all_sales<span class="token punctuation">.</span>salesperson_id <span class="token operator">=</span> salesperson<span class="token punctuation">.</span>id
    <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> amount <span class="token keyword">DESC</span> <span class="token keyword">LIMIT</span> <span class="token number">1</span><span class="token punctuation">)</span>
  <span class="token keyword">AS</span> max_sale<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   However, that also does not work. The derived table is dependent
        on the
   <code class="literal">
    salesperson
   </code>
   table and thus fails
        without
   <code class="literal">
    LATERAL
   </code>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-none"><div class="docs-select-all right" id="sa22250643"><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 1054 (42S22): Unknown column 'salesperson.id' in 'where clause'</code></pre>
  </div>
  <p>
   Adding the
   <code class="literal">
    LATERAL
   </code>
   keyword makes the query
        legal:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa71756068"><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>
  salesperson<span class="token punctuation">.</span><span class="token keyword">name</span><span class="token punctuation">,</span>
  max_sale<span class="token punctuation">.</span>amount<span class="token punctuation">,</span>
  max_sale<span class="token punctuation">.</span>customer_name
<span class="token keyword">FROM</span>
  salesperson<span class="token punctuation">,</span>
  <span class="token comment" spellcheck="true">-- find maximum size and customer at same time</span>
  <span class="token keyword">LATERAL</span>
  <span class="token punctuation">(</span><span class="token keyword">SELECT</span> amount<span class="token punctuation">,</span> customer_name
    <span class="token keyword">FROM</span> all_sales
    <span class="token keyword">WHERE</span> all_sales<span class="token punctuation">.</span>salesperson_id <span class="token operator">=</span> salesperson<span class="token punctuation">.</span>id
    <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> amount <span class="token keyword">DESC</span> <span class="token keyword">LIMIT</span> <span class="token number">1</span><span class="token punctuation">)</span>
  <span class="token keyword">AS</span> max_sale<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   In short,
   <code class="literal">
    LATERAL
   </code>
   is the efficient solution
        to all drawbacks in the two approaches just discussed.
  </p>
 </div>
 <br/>
</div>
