Rechercher dans le manuel MySQL
13.2.11.9 Lateral Derived Tables
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
, orRIGHT [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
, orLEFT [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
, orRIGHT [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, theLATERAL
word is not allowed beforeJSON_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:
- salesperson.name,
- -- find maximum sale size for this salesperson
- FROM all_sales
- AS amount,
- -- find customer for this maximum size
- (SELECT customer_name
- FROM all_sales
- WHERE all_sales.salesperson_id = salesperson.id
- AND all_sales.amount =
- -- find maximum size, again
- FROM all_sales
- AS customer_name
- 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:
- salesperson.name,
- max_sale.amount,
- max_sale_customer.customer_name
- salesperson,
- -- calculate maximum size, cache it in transient derived table max_sale
- FROM all_sales
- AS max_sale,
- -- find customer, reusing cached maximum size
- (SELECT customer_name
- FROM all_sales
- WHERE all_sales.salesperson_id = salesperson.id
- AND all_sales.amount =
- -- the cached maximum size
- max_sale.amount)
- 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”):
- salesperson.name,
- max_sale.amount,
- max_sale_customer.customer_name
- salesperson,
- -- calculate maximum size, cache it in transient derived table max_sale
- LATERAL
- FROM all_sales
- AS max_sale,
- -- find customer, reusing cached maximum size
- LATERAL
- (SELECT customer_name
- FROM all_sales
- WHERE all_sales.salesperson_id = salesperson.id
- AND all_sales.amount =
- -- the cached maximum size
- max_sale.amount)
- 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:
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:
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:
In short, LATERAL
is the efficient solution
to all drawbacks in the two approaches just discussed.
Nederlandse vertaling
U hebt gevraagd om deze site in het Nederlands te bezoeken. Voor nu wordt alleen de interface vertaald, maar nog niet alle inhoud.Als je me wilt helpen met vertalingen, is je bijdrage welkom. Het enige dat u hoeft te doen, is u op de site registreren en mij een bericht sturen waarin u wordt gevraagd om u toe te voegen aan de groep vertalers, zodat u de gewenste pagina's kunt vertalen. Een link onderaan elke vertaalde pagina geeft aan dat u de vertaler bent en heeft een link naar uw profiel.
Bij voorbaat dank.
Document heeft de 26/06/2006 gemaakt, de laatste keer de 26/10/2018 gewijzigd
Bron van het afgedrukte document:https://www.gaudry.be/nl/mysql-rf-lateral-derived-tables.html
De infobrol is een persoonlijke site waarvan de inhoud uitsluitend mijn verantwoordelijkheid is. De tekst is beschikbaar onder CreativeCommons-licentie (BY-NC-SA). Meer info op de gebruiksvoorwaarden en de auteur.
Referenties
Deze verwijzingen en links verwijzen naar documenten die geraadpleegd zijn tijdens het schrijven van deze pagina, of die aanvullende informatie kunnen geven, maar de auteurs van deze bronnen kunnen niet verantwoordelijk worden gehouden voor de inhoud van deze pagina.
De auteur Deze site is als enige verantwoordelijk voor de manier waarop de verschillende concepten, en de vrijheden die met de referentiewerken worden genomen, hier worden gepresenteerd. Vergeet niet dat u meerdere broninformatie moet doorgeven om het risico op fouten te verkleinen.