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_name FROM 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_name FROM 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 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’ 

