使用横向联接

在 FROM 子句中，LATERAL 结构允许内联视图引用前面表表达式中的列。

例如，如果内联视图是 子查询，则子查询可以处理子查询左侧表中的行。例如：

SELECT ...
  FROM left_hand_table_expression AS lhte,
    LATERAL (SELECT col_1 FROM table_2 AS t2 WHERE t2.col_1 = lhte.col_1);

这种行为有点类似于 相关子查询。LATERAL 关键字后的子查询类似于相关子查询本身，left_hand_table_expression 类似于外部查询。与相关子查询不同，横向联接可以返回多行，每行可以有多列。

其他类型的联接不会直接将左侧表表达式的行传递给右侧表表达式进行处理。

横向联接的常见用途是将其与 FLATTEN 表函数的调用结合，以处理复杂的数据结构（例如 ARRAY 或 VARIANT 数据类型），并提取值。有关示例，请参阅 LATERAL。

与其他类型的联接的输出不同，横向联接的输出仅包括从内联视图（子查询）生成的行；子查询的行生成后，它们不会交叉联接到左侧表中的所有行。

术语

考虑以下代码片段：

... FROM te1, LATERAL iv1 ...

横向联接的左侧是一个表表达式 (te1)。横向联接的右侧是一个内联视图 (iv1)。

  • 表表达式：在本主题中，横向联接左侧的表表达式（如上面命名为 te1 的表表达式）几乎可以是计算结果为表的任何有效表达式。例如：

    • 表。

    • 视图。

    • 子查询。

    • 表函数的输出。

    • 较早联接的结果（横向联接或其他类型的联接）。

  • 内联视图：在本主题中，横向联接（在本示例中为 iv1）右侧的表达式称为“内联视图”。在此上下文中，有效的内联视图可以是以下视图之一：

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

    • 子查询。

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

    内联视图不能是表。

  • 交叉联接：在本主题中，“交叉联接”一词不仅指显式交叉联接，还指内部联接和外部联接，包括所有变体（自然联接、左/右/完全外部联接等）。

联接回顾

联接是两步过程。首先，服务器将两行配对，这两行通常位于不同的表中，并且几乎总是以某种方式相关。第二，服务器将配对中每行的列联接为单行。

许多示例查询使用如下所示的数据：

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, employees AS e
  WHERE e.department_ID = d.department_ID
  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          |
+---------------+-------------+-------------+-----------+---------------+---------------+

如您所见，行根据匹配部门 IDs 配对。

联接从两个对应（已配对）的输入行中获取列，并生成包含两个输入行中所有列的一个输出行。（当然，通过修改 SELECT 列表，您可以更改列；但是，在最简单的情况下，所有输入列都包含在输出中。）

横向联接以不同的方式将行配对。但是，该过程的后半部分，即配对行的“联接”相似：输出行将（几乎总是）包含来自输入行对中每个成员的一列或多列。

横向联接如何将行配对

横向联接的行为不同于其他类型的联接。横向联接的行为就像服务器执行了类似于以下内容的循环：

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

本部分重点介绍该过程中的“配对”部分，该部分与横向联接不同。

LATERAL 结构允许横向联接右侧的内联视图引用视图之外表表达式中的列。（在下面的示例中，“内联视图”实际上是一个子查询。）

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          |
+---------------+-------------+-------------+-----------+---------------+---------------+

在本示例中，右侧子查询中的 WHERE 子句引用左侧表中的值。

横向联接和交叉联接之间的区别要比仅访问列大得多。下面几个段落对比这两种类型的联接，首先是传统的交叉联接。

交叉联接将左侧表的每一行与右侧表的每一行组合在一起。结果是笛卡尔积。

从概念上讲，交叉联接类似于嵌套循环，如下面伪代码所示：

for each row in left_hand_table LHT:
  for each row in right_hand_table RHT:
    concatenate the columns of the RHT to the columns of the LHT

If the table on the left has n rows and the table on the right has m rows, the result of the cross join has n x m rows. For example, if the table on the left has 1000 rows and the table on the right has 100 rows, the result of the inner join is 100,000 rows. This is just what you would expect from nested loops; if the outer loop executes 1000 times and the inner loop executes 100 times per iteration of the outer loop, the innermost statement executes 100,000 times. (Of course, SQL programmers rarely write pure cross joins without any join conditions in the FROM clause or WHERE clause.)

横向联接对记录方式有很大不同。下面是实现横向联接的伪代码：

for each row in left_hand_table LHT:
  execute right_hand_subquery RHS using the values from the LHT row,
    and concatenate LHT columns to RHS columns

横向联接只有一个循环，而不是两个嵌套循环，这改变了输出。

对于交叉联接，输出为 100,000 行。如果横向联接在左侧具有 1000 行的相同表，并使用每个输入行会发出一个输出行的右侧内联视图（例如子查询），则横向联接的输出将是 1000 行，而不是 100,000 行。

您可以如下设想横向联接：对于左侧表中的每个输入行，右侧的内联视图会生成 0 行或更多行。然后，这些来自子查询的输出行中的每一行都联接到输入行（不 联接到左侧的整个表），以生成一行，其中包含从子查询选择的列和从 LHT 输入行选择的列。

横向联接右侧的内联视图并不一定为每个输入行生成一个输出行。对于任何一个输入行，右侧的输出可能是 0 行、1 行或多行。这些输出行中的每一行将联接到原始输入行的列。

如果子查询没有正好为每个输入行生成一个输出行，则横向联接不一定生成与左侧表中数量完全相同的行。如果左侧表有 1000 行，而内联视图为每个输入行生成 2 个输出行，则横向联接的结果是 2000 行。

到目前为止，在每个横向联接示例中，外部查询中没有 ON 子句或 WHERE 子句来配对记录。配对（如果有）由内联视图根据传入内联视图中的单个行完成。当内联视图是带 WHERE 子句的子查询时，这一点相当明确。在其他情况下，例如当右侧表达式是表函数而不是子查询时，它不一定那么明显。（后面的示例展示了一个右侧表达式，它使用 FLATTEN 表函数而不是子查询。）

精通相关子查询或表函数联接的读者可能会发现以下比较有助于理解横向联接与交叉联接的区别。不熟悉相关子查询或联接表函数的读者可以跳过这些部分。

相关子查询和横向联接之间的相似性

横向联接类似于相关子查询：

  • 在相关子查询中，子查询对外部查询中的每行执行一次。

  • 在横向联接中，右侧子查询（内联视图）对左侧表表达式中的每一行执行一次。

但是，相关子查询和横向联接并不相同。一个区别是，在横向联接中，子查询可以为每个输入行生成多个输出行，每个输出行可以包含多列。相关子查询仅为每个输入行返回一个输出行，每个输出行必须仅包含一列。

联接表函数与横向联接的相似性

横向联接类似于表和用户定义的表函数 (UDTF) 之间的“联接”。例如，请考虑以下 SQL 语句：

SELECT *
  FROM t1, TABLE(udtf2(t1.col1))
  ...
  ;

实现表和 UDTF 之间的联接的伪代码是：

for each row in left_hand_table LHT:
  udtf2(row) -- that is, call udtf2() with the value(s) from the LHT row.

这与实现横向联接的代码基本相同：

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

示例：使用带 FLATTEN 表函数的横向联接

横向联接经常与内置 FLATTEN 表函数一起使用。FLATTEN 函数经常与可以存储多个值的数据类型（如 ARRAY、VARIANT 和 OBJECT）一起使用。例如，数组通常包含多个值。同样，VARIANT 列可以包含 JSON 数据值，其中可能包含字典（哈希）或列表。（而这反过来又可能包含其他值。）

您可以按如下方式创建 ARRAY 值：

UPDATE employees SET project_names = ARRAY_CONSTRUCT('Materialized Views', 'UDFs')
  WHERE employee_ID = 101;
UPDATE employees SET project_names = ARRAY_CONSTRUCT('Materialized Views', 'Lateral Joins')
  WHERE employee_ID = 102;

FLATTEN 函数可以从这些值内部提取值。该函数获取类型为 VARIANT、OBJECT 或 ARRAY 的单个表达式，并将该表达式中的值提取到一组行中（0 行或多行，每行包含 1 列或多列）。这组行相当于视图或表。此视图仅在定义它的语句期间存在，因此通常称为“内联视图”。

以下示例使用 FLATTEN 从数组中提取值（不使用横向联接）：

SELECT index, value AS project_name
  FROM TABLE(FLATTEN(INPUT => ARRAY_CONSTRUCT('project1', 'project2')));

+-------+--------------+
| INDEX | PROJECT_NAME |
|-------+--------------|
|     0 | "project1"   |
|     1 | "project2"   |
+-------+--------------+

FLATTEN 生成的内联视图可以（但不是必须）与 LATERAL 关键字一起使用。例如：

SELECT * FROM table1, LATERAL FLATTEN(...);

当与 LATERAL 关键字一起使用时，内联视图可以包含它前面的表中列的引用：

SELECT emp.employee_ID, emp.last_name, index, value AS project_name
  FROM employees AS emp,
    LATERAL FLATTEN(INPUT => emp.project_names) AS proj_names
  ORDER BY employee_ID;

+-------------+-----------+-------+----------------------+
| EMPLOYEE_ID | LAST_NAME | INDEX | PROJECT_NAME         |
|-------------+-----------+-------+----------------------|
|         101 | Richards  |     0 | "Materialized Views" |
|         101 | Richards  |     1 | "UDFs"               |
|         102 | Paulson   |     0 | "Materialized Views" |
|         102 | Paulson   |     1 | "Lateral Joins"      |
+-------------+-----------+-------+----------------------+