Rechercher dans le manuel MySQL

13.2.11.9 Lateral Derived Tables

Contents

A derived table cannot normally refer to (depend on) columns of preceding tables in the same FROM clause. As of MySQL 8.0.14, a derived table may be defined as a lateral derived table to specify that such references are permitted.

Nonlateral derived tables are specified using the syntax discussed in Section 13.2.11.8, “Derived Tables”. The syntax for a lateral derived table is the same as for a nonlateral derived table except that the keyword LATERAL is specified before the derived table specification. The LATERAL keyword must precede each table to be used as a lateral derived table.

Lateral derived tables are subject to these restrictions:

  • A lateral derived table can occur only in a FROM clause, either in a list of tables separated with commas or in a join specification (JOIN, INNER JOIN, CROSS JOIN, LEFT [OUTER] JOIN, or RIGHT [OUTER] JOIN).

  • 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 INNER JOIN, CROSS JOIN, or LEFT [OUTER] JOIN.

    If the table is in the left operand and contains a reference to the right operand, the join operation must be an INNER JOIN, CROSS JOIN, or RIGHT [OUTER] JOIN.

  • If a lateral derived table references an aggregate function, the function's aggregation query cannot be the one that owns the FROM clause in which the lateral derived table occurs.

  • Per the SQL standard, a table function has an implicit LATERAL, so it behaves as in MySQL 8.0 versions prior to 8.0.14. However, per the standard, the LATERAL word is not allowed before JSON_TABLE(), even though it is implicit.

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.

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.

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:

  1. SELECT
  2.   salesperson.name,
  3.   -- find maximum sale size for this salesperson
  4.   (SELECT MAX(amount) AS amount
  5.     FROM all_sales
  6.     WHERE all_sales.salesperson_id = salesperson.id)
  7.   AS amount,
  8.   -- find customer for this maximum size
  9.   (SELECT customer_name
  10.     FROM all_sales
  11.     WHERE all_sales.salesperson_id = salesperson.id
  12.     AND all_sales.amount =
  13.          -- find maximum size, again
  14.          (SELECT MAX(amount) AS amount
  15.            FROM all_sales
  16.            WHERE all_sales.salesperson_id = salesperson.id))
  17.   AS customer_name
  18. FROM
  19.   salesperson;

That query is inefficient because it calculates the maximum size twice per salesperson (once in the first subquery and once in the second).

We can try to achieve an efficiency gain by calculating the maximum once per salesperson and “caching” it in a derived table, as shown by this modified query:

  1. SELECT
  2.   salesperson.name,
  3.   max_sale.amount,
  4.   max_sale_customer.customer_name
  5. FROM
  6.   salesperson,
  7.   -- calculate maximum size, cache it in transient derived table max_sale
  8.   (SELECT MAX(amount) AS amount
  9.     FROM all_sales
  10.     WHERE all_sales.salesperson_id = salesperson.id)
  11.   AS max_sale,
  12.   -- find customer, reusing cached maximum size
  13.   (SELECT customer_name
  14.     FROM all_sales
  15.     WHERE all_sales.salesperson_id = salesperson.id
  16.     AND all_sales.amount =
  17.         -- the cached maximum size
  18.         max_sale.amount)
  19.   AS max_sale_customer;

However, the query is illegal in SQL-92 because derived tables cannot depend on other tables in the same FROM clause. Derived tables must be constant over the query's duration, not contain references to columns of other FROM clause tables. As written, the query produces this error: 

ERROR 1054 (42S22): Unknown column 'salesperson.id' in 'where clause'

In SQL:1999, the query becomes legal if the derived tables are preceded by the LATERAL keyword (which means “this derived table depends on previous tables on its left side”):

  1. SELECT
  2.   salesperson.name,
  3.   max_sale.amount,
  4.   max_sale_customer.customer_name
  5. FROM
  6.   salesperson,
  7.   -- calculate maximum size, cache it in transient derived table max_sale
  8.   LATERAL
  9.   (SELECT MAX(amount) AS amount
  10.     FROM all_sales
  11.     WHERE all_sales.salesperson_id = salesperson.id)
  12.   AS max_sale,
  13.   -- find customer, reusing cached maximum size
  14.   LATERAL
  15.   (SELECT customer_name
  16.     FROM all_sales
  17.     WHERE all_sales.salesperson_id = salesperson.id
  18.     AND all_sales.amount =
  19.         -- the cached maximum size
  20.         max_sale.amount)
  21.   AS max_sale_customer;

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.

Second approach to solving the problem: A different solution could be used if a subquery in the SELECT list could return multiple columns:

  1. SELECT
  2.   salesperson.name,
  3.   -- find maximum size and customer at same time
  4.   (SELECT amount, customer_name
  5.     FROM all_sales
  6.     WHERE all_sales.salesperson_id = salesperson.id
  7.     ORDER BY amount DESC LIMIT 1)
  8. FROM
  9.   salesperson;

That is efficient but illegal. It does not work because such subqueries can return only a single column: 

ERROR 1241 (21000): Operand should contain 1 column(s)

One attempt at rewriting the query is to select multiple columns from a derived table:

  1. SELECT
  2.   salesperson.name,
  3.   max_sale.amount,
  4.   max_sale.customer_name
  5. FROM
  6.   salesperson,
  7.   -- find maximum size and customer at same time
  8.   (SELECT amount, customer_name
  9.     FROM all_sales
  10.     WHERE all_sales.salesperson_id = salesperson.id
  11.     ORDER BY amount DESC LIMIT 1)
  12.   AS max_sale;

However, that also does not work. The derived table is dependent on the salesperson table and thus fails without LATERAL: 

ERROR 1054 (42S22): Unknown column 'salesperson.id' in 'where clause'

Adding the LATERAL keyword makes the query legal:

  1. SELECT
  2.   salesperson.name,
  3.   max_sale.amount,
  4.   max_sale.customer_name
  5. FROM
  6.   salesperson,
  7.   -- find maximum size and customer at same time
  8.   LATERAL
  9.   (SELECT amount, customer_name
  10.     FROM all_sales
  11.     WHERE all_sales.salesperson_id = salesperson.id
  12.     ORDER BY amount DESC LIMIT 1)
  13.   AS max_sale;

In short, LATERAL is the efficient solution to all drawbacks in the two approaches just discussed.


Find a PHP function

Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-lateral-derived-tables.html

The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.

References

  1. View the html document Language of the document:en Manuel MySQL : https://dev.mysql.com/

These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.

Contents Haut