类别：: 查询语法

JOIN¶

JOIN 操作将两个表（或其他类似表的来源，如视图或表函数）中的行进行组合，以创建可在查询中使用的新组合行。有关联接的概念说明，请参阅使用联接。

本主题介绍如何在 FROM 子句中使用 JOIN 分子句。JOIN 分子句显式或隐式指定如何将一个表中的行与另一个表中的相应行关联起来。您也可以使用 ASOF JOIN 分子句，它的用途是：当时间戳列上的时间序列数据的值相邻、相继或完全匹配时，联接这些数据。

尽管联接表的推荐方法是在 FROM 子句的 ON 分子句中使用 JOIN，但联接表的另一种方法是使用 WHERE 子句。有关详细信息，请参阅 WHERE 子句的文档。

语法¶

使用下列之一：

SELECT ...
FROM <object_ref1> [
                     {
                       INNER
                       | { LEFT | RIGHT | FULL } [ OUTER ]
                     }
                     [ DIRECTED ]
                   ]
                   JOIN <object_ref2>
  [ ON <condition> ]
[ ... ]

Copy

SELECT *
FROM <object_ref1> [
                     {
                       INNER
                       | { LEFT | RIGHT | FULL } [ OUTER ]
                     }
                     [ DIRECTED ]
                   ]
                   JOIN <object_ref2>
  [ USING( <column_list> ) ]
[ ... ]

Copy

SELECT ...
FROM <object_ref1> [
                     {
                       NATURAL [
                                 {
                                   INNER
                                   | { LEFT | RIGHT | FULL } [ OUTER ]
                                 }
                                 [ DIRECTED ]
                               ]
                       | CROSS  [ DIRECTED ]
                     }
                   ]
                   JOIN <object_ref2>
[ ... ]

Copy

参数¶

object_ref1 和 object_ref2

每个对象引用都是一个表或类似表的数据源。

JOIN

使用 JOIN 关键字指定应联接的表。将 JOIN 与其他与联接相关的关键字（例如 INNER 或 OUTER）结合起来，以指定联接的类型。

联接的语义如下所示（为简洁起见，本主题对 object_ref1 和 object_ref2 分别使用 o1 和 o2 ）。

联接类型	语义
`o1 INNER JOIN o2`	对于 `o1` 的每一行，根据 `o2` 分子句为匹配的 `ON condition` 的每一行生成一行。（您还可以使用逗号来指定内部联接。有关示例，请参阅示例部分。）如果使用不带 `ON` 子句的 `INNER JOIN`，或者使用不带 `WHERE` 子句的逗号，结果与使用 `CROSS JOIN` 相同：笛卡尔积，`o1` 的每一行都与 `o2` 的每一行配对。
`o1 LEFT OUTER JOIN o2`	对于在 `o2` 中没有匹配项的 `o1` 的每一行，内部联接的结果都会增加一行。引用 `o2` 的结果列包含 null。
`o1 RIGHT OUTER JOIN o2`	对于在 `o1` 中没有匹配项的 `o2` 的每一行，内部联接的结果都会增加一行。引用 `o1` 的结果列包含 null。
`o1 FULL OUTER JOIN o2`	返回所有联接的行，加上每个不匹配的左侧行的一行（在右侧用 null 扩展），加上每个不匹配的右侧行的一行（在左侧用 null 扩展）。
`o1 CROSS JOIN o2`	对于来自 `o1` 和 `o2` 的行的每个可能组合（即笛卡尔积），联接的表包含一个由 `o1` 中的所有列和 `o2` 中的所有列组成的行。`CROSS JOIN` 不能与 `ON condition` 子句结合使用。但是，您可以使用 `WHERE` 子句来筛选结果。
`o1 NATURAL JOIN o2`	`NATURAL JOIN` 与两个表的公共列上的显式 `JOIN` 相同，只是公共列在输出中只出现一次。（自然联接假定具有相同名称但位于不同表中的列包含相应的数据。有关示例，请参阅示例部分。`NATURAL JOIN` 可以与 `OUTER JOIN` 结合使用。`NATURAL JOIN` 不能与 `ON condition` 子句结合使用，因为已隐含 `JOIN` 条件。但是，您可以使用 `WHERE` 子句来筛选结果。

DIRECTED 关键字指定 定向联接，其将强制执行表的联接顺序。先扫描第一个或左边的表，然后再扫描第二个或右边的表。例如，o1 INNER DIRECTED JOIN o2 先扫描 o1 表，然后再扫描 o2 表。定向联接在以下情况下很有用：

您要将工作负载迁移到具有联接顺序指令的 Snowflake。
您想通过按特定顺序扫描联接表来提高性能。

默认值：INNER JOIN

如果使用单词 JOIN 时没有指定 INNER 或 OUTER，则该 JOIN 是内部联接。

If the DIRECTED keyword is added, the join type --- for example, INNER, LEFT, RIGHT, or FULL --- is required.

另请参阅：

ON condition

布尔表达式，用于定义被视为匹配的 JOIN 两侧的行，例如：

ON object_ref2.id_number = object_ref1.id_number

Copy

条件在 WHERE 子句文档中有更详细的讨论。

ON 禁止使用 CROSS JOIN 子句。

ON 子句对于 NATURAL JOIN 是不必要的，并且是禁止的，因为联接列已隐含。

对于其他联接，ON 子句是可选的。但是，省略 ON 子句会产生笛卡尔积；object_ref1 的每一行都与 object_ref2 的每一行配对。笛卡尔积可以产生非常大的输出量，几乎所有输出都由实际上并不相关的行对组成，这会消耗大量资源，并且通常是用户错误。

USING( column_list )

要联接的两个表之间共有的列的列表；这些列用作联接列。在要联接的每个表中，列必须具有相同的名称和含义。

例如，假设 SQL 语句包含：

... o1 JOIN o2
    USING (key_column)

Copy

在简单的情况下，这相当于：

... o1 JOIN o2
    ON o2.key_column = o1.key_column

Copy

在标准 JOIN 语法中，投影列表（SELECT 关键字后的列和其他表达式的列表）是 *。这导致查询只返回 key_column 一次。这些列按以下顺序返回：

USING 子句中的列按指定顺序排列。
USING 子句中未指定左侧表列。
USING 子句中未指定右侧表列。

有关标准和非标准用法的示例，请参阅示例部分。

使用说明¶

以下限制适用于 SQL UDTFs 以外的表函数：

不能在横向表函数（SQL UDTF 除外）中指定 ON、USING 或 NATURAL JOIN 子句。

例如，不允许使用以下语法：

SELECT ... FROM my_table
  JOIN TABLE(FLATTEN(input=>[col_a]))
  ON ... ;

Copy

SELECT ... FROM my_table
  INNER JOIN TABLE(FLATTEN(input=>[col_a]))
  ON ... ;

Copy

SELECT ... FROM my_table
  JOIN TABLE(my_js_udtf(col_a))
  ON ... ;

Copy

SELECT ... FROM my_table
  INNER JOIN TABLE(my_js_udtf(col_a))
  ON ... ;

Copy

不能在表函数（SQL UDTF 除外）的外部横向联接中指定 ON、USING 或 NATURAL JOIN 子句。

例如，不允许使用以下语法：

SELECT ... FROM my_table
  LEFT JOIN TABLE(FLATTEN(input=>[a]))
  ON ... ;

Copy

SELECT ... FROM my_table
  FULL JOIN TABLE(FLATTEN(input=>[a]))
  ON ... ;

Copy

SELECT ... FROM my_table
  LEFT JOIN TABLE(my_js_udtf(a))
  ON ... ;

Copy

SELECT ... FROM my_table
  FULL JOIN TABLE(my_js_udtf(a))
  ON ... ;

Copy

使用此语法会导致以下错误：

000002 (0A000): Unsupported feature
  'lateral table function called with OUTER JOIN syntax
   or a join predicate (ON clause)'

如果您使用的是逗号而不是 JOIN 关键字，则这些限制不适用。
```
SELECT ... FROM my_table,
  TABLE(FLATTEN(input=>[col_a]))
  ON ... ;
```
Copy

示例¶

许多 JOIN 示例使用两个表：t1 和 t2。创建这些表并插入数据：

CREATE TABLE t1 (col1 INTEGER);

INSERT INTO t1 (col1) VALUES
  (2),
  (3),
  (4);

CREATE TABLE t2 (col1 INTEGER);

INSERT INTO t2 (col1) VALUES
  (1),
  (2),
  (2),
  (3);

Copy

以下示例使用联接运行查询：

使用内部联接运行查询
使用左外部联接运行查询
使用右外部联接运行查询
使用完整外部联接运行查询
使用交叉联接运行查询
使用自然联接运行查询
运行在 FROM 子句中组合联接的查询
通过使用 USING 子句的联接运行查询

使用内部联接运行查询¶

以下示例使用内部联接运行查询：

SELECT t1.col1, t2.col1
  FROM t1 INNER JOIN t2
    ON t2.col1 = t1.col1
  ORDER BY 1,2;

Copy

+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
+------+------+

使用内部定向联接运行相同的查询，以强制执行联接顺序，从而先扫描左侧的表：

SELECT t1.col1, t2.col1
  FROM t1 INNER DIRECTED JOIN t2
    ON t2.col1 = t1.col1
  ORDER BY 1,2;

Copy

+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
+------+------+

使用左外部联接运行查询¶

以下示例使用左外部联接运行查询：

SELECT t1.col1, t2.col1
  FROM t1 LEFT OUTER JOIN t2
    ON t2.col1 = t1.col1
  ORDER BY 1,2;

Copy

在输出中，表 t1 中该行的值为 NULL，在表 t2 中没有匹配行：

+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
|    4 | NULL |
+------+------+

使用右外部联接运行查询¶

以下示例使用右外部联接运行查询：

SELECT t1.col1, t2.col1
  FROM t1 RIGHT OUTER JOIN t2
    ON t2.col1 = t1.col1
  ORDER BY 1,2;

Copy

在输出中，表 t1 中该行的值为 NULL，在表 t2 中没有匹配行。

+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
| NULL |    1 |
+------+------+

使用完整外部联接运行查询¶

以下示例使用完整外部联接运行查询：

SELECT t1.col1, t2.col1
  FROM t1 FULL OUTER JOIN t2
    ON t2.col1 = t1.col1
  ORDER BY 1,2;

Copy

由于每个表都有一行在另一个表中没有匹配的行，因此输出包含两个具有 NULL 值的行：

+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
|    4 | NULL |
| NULL |    1 |
+------+------+

使用交叉联接运行查询¶

以下示例使用交叉联接运行查询：

备注

交叉连接没有 ON 子句。

SELECT t1.col1, t2.col1
  FROM t1 CROSS JOIN t2
  ORDER BY 1, 2;

Copy

输出显示查询会生成笛卡尔积：

+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    1 |
|    2 |    2 |
|    2 |    2 |
|    2 |    3 |
|    3 |    1 |
|    3 |    2 |
|    3 |    2 |
|    3 |    3 |
|    4 |    1 |
|    4 |    2 |
|    4 |    2 |
|    4 |    3 |
+------+------+

交叉联接可以按 WHERE 子句进行筛选，如以下示例所示：

SELECT t1.col1, t2.col1
  FROM t1 CROSS JOIN t2
  WHERE t2.col1 = t1.col1
  ORDER BY 1, 2;

Copy

+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
+------+------+

使用自然联接运行查询¶

以下示例展示了有自然联接的查询。首先，创建两个表并插入数据：

CREATE OR REPLACE TABLE d1 (
  id NUMBER,
  name VARCHAR);

INSERT INTO d1 (id, name) VALUES
  (1,'a'),
  (2,'b'),
  (4,'c');

CREATE OR REPLACE TABLE d2 (
  id NUMBER,
  value VARCHAR);

INSERT INTO d2 (id, value) VALUES
  (1,'xx'),
  (2,'yy'),
  (5,'zz');

Copy

使用自然联接运行查询：

SELECT *
  FROM d1 NATURAL INNER JOIN d2
  ORDER BY id;

Copy

输出显示自然联接会生成与相应的内部联接相同的输出，只是输出不包含联接列的第二个副本：

+----+------+-------+
| ID | NAME | VALUE |
|----+------+-------|
|  1 | a    | xx    |
|  2 | b    | yy    |
+----+------+-------+

以下示例显示您可以将自然联接与外部联接组合：

SELECT *
  FROM d1 NATURAL FULL OUTER JOIN d2
  ORDER BY id;

Copy

+----+------+-------+
| ID | NAME | VALUE |
|----+------+-------|
|  1 | a    | xx    |
|  2 | b    | yy    |
|  4 | c    | NULL  |
|  5 | NULL | zz    |
+----+------+-------+

运行在 FROM 子句中组合联接的查询¶

您可以在 FROM 子句中组合。创建第三个表：

CREATE TABLE t3 (col1 INTEGER);

INSERT INTO t3 (col1) VALUES
  (2),
  (6);

Copy

运行在 FROM 子句中链接两个联接的查询：

SELECT t1.*, t2.*, t3.*
  FROM t1
    LEFT OUTER JOIN t2 ON (t1.col1 = t2.col1)
    RIGHT OUTER JOIN t3 ON (t3.col1 = t2.col1)
  ORDER BY t1.col1;

Copy

+------+------+------+
| COL1 | COL1 | COL1 |
|------+------+------|
|    2 |    2 |    2 |
|    2 |    2 |    2 |
| NULL | NULL |    6 |
+------+------+------+

在此类查询中，结果是根据从左到右发生的联接来确定的，不过如果不同的联接顺序将产生相同的结果，优化器可能会对联接重新排序。如果右侧外部联接应在左侧外部联接之前进行，则以如下方式编写查询：

SELECT t1.*, t2.*, t3.*
FROM t1
  LEFT OUTER JOIN
    (t2 RIGHT OUTER JOIN t3 ON (t3.col1 = t2.col1))
  ON (t1.col1 = t2.col1)
ORDER BY t1.col1;

Copy

+------+------+------+
| COL1 | COL1 | COL1 |
|------+------+------|
|    2 |    2 |    2 |
|    2 |    2 |    2 |
|    3 | NULL | NULL |
|    4 | NULL | NULL |
+------+------+------+

通过使用 USING 子句的联接运行查询¶

下面的两个示例显示了 USING 子句的标准 (ISO 9075) 和非标准用法。两者都由 Snowflake 支持。

第一个示例显示了标准用法。具体而言，投影列表正好包含 *。

WITH
  l AS (
       SELECT 'a' AS userid
       ),
  r AS (
       SELECT 'b' AS userid
       )
SELECT *
  FROM l LEFT JOIN r USING(userid);

Copy

即使示例查询联接了两个表，并且每个表都有一列，而且查询要求提供所有列，但输出包含一列，而不是两列：

+--------+
| USERID |
|--------|
| a      |
+--------+

以下示例演示了非标准用法。投影列表包含 * 以外的内容：

WITH
  l AS (
       SELECT 'a' AS userid
     ),
  r AS (
       SELECT 'b' AS userid
       )
SELECT l.userid as UI_L,
       r.userid as UI_R
  FROM l LEFT JOIN r USING(userid);

Copy

输出包含两列，第二列包含第二个表中的值或 NULL：

+------+------+
| UI_L | UI_R |
|------+------|
| a    | NULL |
+------+------+