MySQL 8.0-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.

派生表通常不能在同一个FROM子句中引用(依赖于)前面表的列。在MySQL 8.0.14中，派生表可以被定义为侧向派生表，以指定这种引用是允许的。

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.

非横向派生表使用13.2.11.8节“派生表”中讨论的语法来指定。侧面派生表的语法与非侧面派生表的语法相同，只是关键字lateral在派生表规范之前指定。横向关键字必须位于每个表之前，才能用作横向派生表。

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, or RIGHT [OUTER] JOIN).

• 侧面派生表只能出现在FROM子句中，或者出现在用逗号分隔的表列表中，或者出现在连接规范中(join、INNER join、CROSS join、LEFT [OUTER] join或RIGHT [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, or LEFT [OUTER] JOIN. 如果侧向派生表位于连接子句的右操作数中，并且包含对左操作数的引用，则连接操作必须是INNER join、CROSS join或left [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, or RIGHT [OUTER] JOIN. 如果表在左操作数中，并且包含对右操作数的引用，则连接操作必须是INNER join、CROSS join或right [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.

• 如果侧派生表引用聚合函数，则该函数的聚合查询不能是包含该侧派生表的FROM子句的聚合查询。

• 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, the LATERAL word is not allowed before JSON_TABLE(), even though it is implicit.

• 根据SQL标准，表函数有一个隐式的LATERAL，所以它的行为就像在8.0.14之前的MySQL 8.0版本中一样。然而，根据标准，LATERAL单词不允许出现在JSON_TABLE()之前，即使它是隐式的。

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操作成为可能，这些操作无法用非侧向派生表完成，或者需要更低效率的解决方案。

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:

解决问题的第一个方法:对每个销售人员，计算最大销售规模，并找出提供这个最大销售规模的客户。在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. 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:

但是，在SQL-92中查询是非法的，因为派生表不能依赖于同一FROM子句中的其他表。派生表必须在查询期间保持不变，不包含对其他FROM子句表列的引用。如上所述，查询产生如下错误:

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.

横向派生表不需要是常量，每当top查询处理它所依赖的前一个表中的新行时，就会更新它。

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:

然而，这也不起作用。派生的表依赖于销售人员表，因此没有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是上述两种方法中所有缺陷的有效解决方案。