类别：

查询语法

JOIN

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

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

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

语法

使用下列之一：

SELECT ...
FROM <object_ref1> [
                     {
                       INNER
                       | { LEFT | RIGHT | FULL } [ OUTER ]
                     }
                   ]
                   JOIN <object_ref2>
  [ ON <condition> ]
[ ... ]
Copy
SELECT *
FROM <object_ref1> [
                     {
                       INNER
                       | { LEFT | RIGHT | FULL } [ OUTER ]
                     }
                   ]
                   JOIN <object_ref2>
  [ USING( <column_list> ) ]
[ ... ]
Copy
SELECT ...
FROM <object_ref1> [
                     {
                       | NATURAL [ { LEFT | RIGHT | FULL } [ OUTER ] ]
                       | CROSS
                     }
                   ]
                   JOIN <object_ref2>
[ ... ]
Copy
object_ref1 和 object_ref2

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

JOIN

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

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

联接类型

语义

o1 INNER JOIN o2

对于 o1 的每一行，根据 ON condition 分子句为匹配的 o2 的每一行生成一行。（请注意，您还可以使用逗号来指定内部联接。有关示例，请参阅下面的示例部分。）如果使用不带 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 子句来筛选结果。

另请参阅：

默认：INNER JOIN

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

ON condition

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

ON object_ref2.id_number = object_ref1.id_number
Copy

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

CROSS JOIN 禁止使用 ON 子句。

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);
CREATE TABLE t2 (col1 INTEGER);
Copy
INSERT INTO t1 (col1) VALUES 
   (2),
   (3),
   (4);
INSERT INTO t2 (col1) VALUES 
   (1),
   (2),
   (2),
   (3);
Copy

内部联接：

SELECT t1.col1, t2.col1
    FROM t1 INNER JOIN t2
        ON t2.col1 = t1.col1
    ORDER BY 1,2;
+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
+------+------+
Copy

这显示了左侧外部联接。请注意，若表 t1 中的行在表 t2 中没有匹配行，则其值为 NULL。

SELECT t1.col1, t2.col1
    FROM t1 LEFT OUTER JOIN t2
        ON t2.col1 = t1.col1
    ORDER BY 1,2;
+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
|    4 | NULL |
+------+------+
Copy

这显示了右侧外部联接。请注意，若表 t1 中的行在表 t2 中没有匹配行，则其值为 NULL。

SELECT t1.col1, t2.col1
    FROM t1 RIGHT OUTER JOIN t2
        ON t2.col1 = t1.col1
    ORDER BY 1,2;
+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
| NULL |    1 |
+------+------+
Copy

这显示了完整的外部联接。请注意，由于每个表都有一行在另一个表中没有匹配的行，因此输出包含具有 NULL 值的两行：

SELECT t1.col1, t2.col1
    FROM t1 FULL OUTER JOIN t2
        ON t2.col1 = t1.col1
    ORDER BY 1,2;
+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
|    4 | NULL |
| NULL |    1 |
+------+------+
Copy

下面是一个交叉联接的示例，它产生笛卡尔积。请注意，交叉联接没有 ON 子句。

SELECT t1.col1, t2.col1
    FROM t1 CROSS JOIN t2
    ORDER BY 1, 2;
+------+------+
| 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 |
+------+------+
Copy

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

SELECT t1.col1, t2.col1
    FROM t1 CROSS JOIN t2
    WHERE t2.col1 = t1.col1
    ORDER BY 1, 2;
+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
+------+------+
Copy

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

CREATE OR REPLACE TABLE d1 (
  id number,
  name string
  );
+--------------------------------+
| status                         |
|--------------------------------|
| Table D1 successfully created. |
+--------------------------------+
INSERT INTO d1 (id, name) VALUES
  (1,'a'),
  (2,'b'),
  (4,'c');
+-------------------------+
| number of rows inserted |
|-------------------------|
|                       3 |
+-------------------------+
CREATE OR REPLACE TABLE d2 (
  id number,
  value string
  );
+--------------------------------+
| status                         |
|--------------------------------|
| Table D2 successfully created. |
+--------------------------------+
INSERT INTO d2 (id, value) VALUES
  (1,'xx'),
  (2,'yy'),
  (5,'zz');
+-------------------------+
| number of rows inserted |
|-------------------------|
|                       3 |
+-------------------------+
SELECT *
    FROM d1 NATURAL INNER JOIN d2
    ORDER BY id;
+----+------+-------+
| ID | NAME | VALUE |
|----+------+-------|
|  1 | a    | xx    |
|  2 | b    | yy    |
+----+------+-------+
Copy

自然联接可以与外部联接组合，例如：

SELECT *
  FROM d1 NATURAL FULL OUTER JOIN d2
  ORDER BY ID;
+----+------+-------+
| ID | NAME | VALUE |
|----+------+-------|
|  1 | a    | xx    |
|  2 | b    | yy    |
|  4 | c    | NULL  |
|  5 | NULL | zz    |
+----+------+-------+
Copy

联接可以组合在 FROM 子句中。下面的代码创建第三个表，然后在 FROM 子句中将两个 JOINs 链接在一起：

CREATE TABLE t3 (col1 INTEGER);
INSERT INTO t3 (col1) VALUES 
   (2),
   (6);
Copy
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;
+------+------+------+
| COL1 | COL1 | COL1 |
|------+------+------|
|    2 |    2 |    2 |
|    2 |    2 |    2 |
| NULL | NULL |    6 |
+------+------+------+
Copy

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

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;
+------+------+------+
| COL1 | COL1 | COL1 |
|------+------+------|
|    2 |    2 |    2 |
|    2 |    2 |    2 |
|    3 | NULL | NULL |
|    4 | NULL | NULL |
+------+------+------+
Copy

下面的两个示例显示了 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)
;
+--------+
| USERID |
|--------|
| a      |
+--------+
Copy

以下示例演示了非标准用法。投影列表包含“*”以外的内容。输出包含两列，第二列包含第二个表中的值或 NULL。

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)
;
+------+------+
| UI_L | UI_R |
|------+------|
| a    | NULL |
+------+------+
Copy
语言: 中文