类别：

查询语法

LATERAL

在 FROM 子句中， LATERAL 关键字允许内联视图引用该内联视图之前的表表达式中的列。

横向联接的行为更像相关子查询，而不像大多数典型联接。横向联接的行为就像服务器执行了类似于以下内容的循环：

for each row in left_hand_table LHT:
    execute right_hand_subquery RHS using the values from the current row in the LHT

与非横向联接的输出不同，横向联接的输出仅包括从内联视图生成的行。无需显式 ON 子句将行从左侧联接到右侧；由于内联视图引用了左侧表表达式中的列，因此关系已经建立。

另请参阅：使用横向联接。

何时使用 LATERAL

LATERAL 是适用于以下用例的宝贵工具：

  • 在嵌套数据上链接表函数：当需要在数组中展平数组或在多个级别的嵌套 JSON 中导航时，每个后续表函数调用都必须引用前一个表函数的输出。横向联接使这成为可能。

  • 使用特定于行的实参调用表函数：当表函数（例如 UDTF) 需要从左侧表中接收每行的不同输入值。

对于展平单级数组等简单情况，不使用横向连接，TABLE(FLATTEN(...)) 也能产生相同的结果。仅当内联视图必须引用 FROM 子句中前一个表达式中才能获得的列时，才需要使用横向联接。

语法

SELECT ...
  FROM <left_hand_table_expression>, LATERAL ( <inline_view> )
...

参数

left_hand_table_expression

这是行的源，例如：

  • 表。

  • 视图。

  • 子查询。

  • 表函数。

  • 先前联接的结果。

inline_view

inline_view 可以是：

  • 内联视图：在语句中定义的视图，仅在语句的持续时间内有效。

  • 子查询。

  • 表函数：内置表函数（如 FLATTEN）或用户定义的表函数 (UDTF)。

inline_view 不能是纯表引用。它必须是一个可以根据左侧表表达式中的值处理或筛选行的表达式，例如带有 WHERE 子句或表函数调用。

使用说明

  • 关键字 LATERAL 后面的内联视图只能引用内联视图本身以及 FROM 子句内联视图左侧的表中的列。

    SELECT *
  FROM table_reference_me, LATERAL (...), table_do_not_reference_me ...

  • 尽管内联视图通常引用 left_hand_table_expression 中的一个或多个列，但并不要求这样做。

  • 正如 INNER JOIN 语法可以使用逗号或关键字 INNER JOIN 一样，横向联接也可以使用逗号或关键字 INNER JOIN。例如：

    FROM departments AS d INNER JOIN LATERAL (...)

  • 不能指定 ON、USING 或 NATURAL JOIN 子句：

    • 横向表函数（SQL UDTF 除外)

    • 与表函数的外部横向联接（ SQL UDTF 除外)

    有关详细信息，请参阅 JOIN 主题中的使用说明。

  • left_hand_table_expression 不能是 UNPIVOT 结果集。在 LATERAL 联接中尝试引用 UNPIVOT 别名会导致错误。作为替代方案，可先将 UNPIVOT 结果物化到一个临时表中，然后将该表用作左侧表达式。有关更多信息，请参阅 UNPIVOT。

示例

另请参阅 示例：使用带 FLATTEN 表函数的横向联接 和 使用 FLATTEN 筛选 WHERE 子句中的结果。

以下示例使用这些表：

CREATE TABLE departments (department_id INTEGER, name VARCHAR);
CREATE TABLE employees (employee_ID INTEGER, last_name VARCHAR,
  department_ID INTEGER, project_names ARRAY);

INSERT INTO departments (department_ID, name) VALUES
  (1, 'Engineering'),
  (2, 'Support');
INSERT INTO employees (employee_ID, last_name, department_ID) VALUES
  (101, 'Richards', 1),
  (102, 'Paulson',  1),
  (103, 'Johnson',  2);

下面的查询是一个带有子查询的横向联接：

SELECT *
  FROM departments AS d,
    LATERAL (SELECT * FROM employees AS e WHERE e.department_ID = d.department_ID) AS iv2
  ORDER BY employee_ID;

+---------------+-------------+-------------+-----------+---------------+---------------+
| DEPARTMENT_ID | NAME        | EMPLOYEE_ID | LAST_NAME | DEPARTMENT_ID | PROJECT_NAMES |
|---------------+-------------+-------------+-----------+---------------+---------------|
|             1 | Engineering |         101 | Richards  |             1 | NULL          |
|             1 | Engineering |         102 | Paulson   |             1 | NULL          |
|             2 | Support     |         103 | Johnson   |             2 | NULL          |
+---------------+-------------+-------------+-----------+---------------+---------------+

下面的 SQL 语句是等效的，并产生相同的输出。它使用关键字 INNER JOIN 而不是 FROM 子句中的逗号。

SELECT *
  FROM departments AS d INNER JOIN
    LATERAL (SELECT * FROM employees AS e WHERE e.department_ID = d.department_ID) AS iv2
  ORDER BY employee_ID;

为嵌套数据链接 LATERAL FLATTEN

当您需要链接多个 FLATTEN 调用访问嵌套数据结构时，必须使用 LATERAL。在以下示例中，第二个 FLATTEN 必须引用第一个 FLATTEN 的输出，这只能通过 LATERAL 才能实现。

CREATE OR REPLACE TABLE persons AS
  SELECT column1 AS id, PARSE_JSON(column2) AS c
    FROM VALUES
      (12712555,
       '{ "name": { "first": "John", "last": "Smith" },
          "contact": [{ "business": [
            { "type": "phone", "content": "555-1234" },
            { "type": "email", "content": "j.smith@example.com" }
          ]}]}'),
      (98127771,
       '{ "name": { "first": "Jane", "last": "Doe" },
          "contact": [{ "business": [
            { "type": "phone", "content": "555-1236" },
            { "type": "email", "content": "j.doe@example.com" }
          ]}]}');

以下查询使用两个 LATERAL FLATTEN 调用。第一次调用将 contact 数组展平，第二个将每个联系人中的 business 数组展平。第二个 FLATTEN 调用引用 f.value，它来自第一个 FLATTEN 调用的输出。

SELECT id,
    f1.value:type::STRING AS contact_type,
    f1.value:content::STRING AS contact_details
  FROM persons p,
    LATERAL FLATTEN(INPUT => p.c, PATH => 'contact') f,
    LATERAL FLATTEN(INPUT => f.value:business) f1;

+----------+--------------+---------------------+
|       ID | CONTACT_TYPE | CONTACT_DETAILS     |
|----------+--------------+---------------------|
| 12712555 | phone        | 555-1234            |
| 12712555 | email        | j.smith@example.com |
| 98127771 | phone        | 555-1236            |
| 98127771 | email        | j.doe@example.com   |
+----------+--------------+---------------------+

如果没有 LATERAL，则无法编写此查询，因为第二个 FLATTEN 调用取决于第一个 FLATTEN 调用的输出。

LATERAL 与其他方法的比较

下表总结了与其他方法相比时，何时使用 LATERAL：

用例

建议

展平单级数组

在没有 LATERAL 的情况下使用 TABLE(FLATTEN(...)) 也能得到相同结果，LATERAL 是可选的。

展平嵌套数组（数组中的数组）

LATERAL 需要链接 FLATTEN 调用。

根据当前行筛选另一个表中的行

可以使用 SELECT 列表中的相关子查询或 LATERAL。LATERAL 可以返回多行和多列；SELECT 中的相关子查询无法做到这一点。

使用特定于行输入的调用表函数

LATERAL 允许表函数为每行接收不同的实参。