What does "A derived table cannot contain references to other tables of the same SELECT" mean? I looked it up in the MySQL documentation
SELECT
salesperson.name,
max_sale.amount,
max_sale_customer.customer_name
FROM
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 derived table cannot contain references to other tables of the same SELECT (use a LATERAL derived table for that; see Section 13.2.11.9, “Lateral Derived Tables”)
The key to understanding this is in the manual you have read:
Derived tables must be constant over the query's duration, not contain references to columns of other FROM clause tables.
That is, think of the derived table as running at the initial time of the query, before any rows from the joined tables have been read. If the derived table subquery includes any references to joined tables, then the result of the derived table would depend on data read from those tables. Can conflicts with the requirement that a derived table be constant.
LATERAL changes that. It ensures that the derived table is evaluated later, after the query begins to read rows from the joined tables.
P.S.: The comment from jarlh above is not strictly correct. It is recommended for other reasons to use explicit JOIN syntax (e.g. it supports outer joins and so on), but it's not required for the sake of LATERAL.