A derived table cannot normally refer to (depend on) columns of preceding tables in the same FROM clause. 派生表通常不能引用（依赖）同一FROM子句中前面表的列。As of MySQL 8.0.14, a derived table may be defined as a lateral derived table to specify that such references are permitted.从MySQL 8.0.14开始，派生表可以定义为横向派生表，以指定允许这样的引用。

Nonlateral derived tables are specified using the syntax discussed in Section 13.2.11.8, “Derived Tables”. 使用第13.2.11.8节，“派生表”中讨论的语法指定非横向派生表。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. 横向派生表的语法与非横向派生表的语法相同，只是关键字LATERAL是在派生表规范之前指定的。The LATERAL keyword must precede each table to be used as a lateral derived table.LATERAL关键字必须在用作横向派生表的每个表之前。

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.下面的讨论说明了横向派生表如何使某些SQL操作成为可能，这些SQL操作无法用非横向派生表完成，或者需要效率较低的解决方法。

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. 假设我们要解决这个问题：给定一个sales force中的人员表（其中每一行描述sales force的一个成员）和一个包含所有sales的表（其中每一行描述一个销售人员：salesperson、customer、amount、date)，确定每个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:在MySQL中，可以这样做：

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. 但是，该查询在SQL-92中是非法的，因为派生表不能依赖于同一FROM子句中的其他表。Derived tables must be constant over the query's duration, not contain references to columns of other FROM clause tables. 派生表在查询的持续时间内必须是常量，不包含对其他FROM子句表的列的引用。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”):在SQL:1999中，如果派生表前面有LATERAL关键字（这意味着“此派生表依赖于其左侧的先前表”），则查询将变为合法查询：

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 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:解决问题的第二种方法：如果SELECT列表中的子查询可以返回多个列，则可以使用不同的解决方案：

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_name
FROM
  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 the salesperson table and thus fails without LATERAL:派生表依赖于salesperson表，因此在没有LATERAL的情况下失败：

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

Adding the LATERAL keyword makes the query legal:添加LATERAL关键字使查询合法：

SELECT
  salesperson.name,
  max_sale.amount,
  max_sale.customer_name
FROM
  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.简言之，LATERAL是解决上述两种方法所有缺点的有效方法。