Movatterモバイル変換

A derived table cannot normally refer to (depend on) columns of preceding tables in the sameFROM clause. 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 inSection 15.2.15.8, “Derived Tables”. The syntax for a lateral derived table is the same as for a nonlateral derived table except that the keywordLATERAL is specified before the derived table specification. TheLATERAL keyword must precede each table to be used as a lateral derived table.

Lateral derived tables are subject to these restrictions:

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:

SELECT  salesperson.name,  -- find maximum sale size for this salesperson  (SELECT MAX(amount) AS amount    FROM all_sales    WHERE all_sales.salesperson_id = salesperson.id)  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         (SELECT MAX(amount) AS amount           FROM all_sales           WHERE all_sales.salesperson_id = salesperson.id))  AS customer_nameFROM  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:

SELECT  salesperson.name,  max_sale.amount,  max_sale_customer.customer_nameFROM  salesperson,  -- calculate maximum size, cache it in transient derived table max_sale  (SELECT MAX(amount) AS amount    FROM all_sales    WHERE all_sales.salesperson_id = salesperson.id)  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 sameFROM clause. Derived tables must be constant over the query's duration, not contain references to columns of otherFROM 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 theLATERAL keyword (which means“this derived table depends on previous tables on its left side”):

SELECT  salesperson.name,  max_sale.amount,  max_sale_customer.customer_nameFROM  salesperson,  -- calculate maximum size, cache it in transient derived table max_sale  LATERAL  (SELECT MAX(amount) AS amount    FROM all_sales    WHERE all_sales.salesperson_id = salesperson.id)  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 theSELECT list could return multiple columns:

SELECT  salesperson.name,  -- find maximum size and customer at same time  (SELECT amount, customer_name    FROM all_sales    WHERE all_sales.salesperson_id = salesperson.id    ORDER BY amount DESC LIMIT 1)FROM  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:

SELECT  salesperson.name,  max_sale.amount,  max_sale.customer_nameFROM  salesperson,  -- find maximum size and customer at same time  (SELECT amount, customer_name    FROM all_sales    WHERE all_sales.salesperson_id = salesperson.id    ORDER BY amount DESC LIMIT 1)  AS max_sale;

However, that also does not work. The derived table is dependent on thesalesperson table and thus fails withoutLATERAL:

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

Adding theLATERAL keyword makes the query legal:

SELECT  salesperson.name,  max_sale.amount,  max_sale.customer_nameFROM  salesperson,  -- find maximum size and customer at same time  LATERAL  (SELECT amount, customer_name    FROM all_sales    WHERE all_sales.salesperson_id = salesperson.id    ORDER BY amount DESC LIMIT 1)  AS max_sale;

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