类别：: 查询语法

使用联接¶

联接可合并两个表中的行以创建可在查询中使用的新组合行。

本主题内容：

简介¶

当表中的数据相关时，联接非常有用。例如，一个表可能包含有关项目的信息，另一个表可能包含有关这些项目的处理员工的信息。

SELECT * FROM projects ORDER BY project_ID;
+------------+------------------+
| PROJECT_ID | PROJECT_NAME     |
|------------+------------------|
|       1000 | COVID-19 Vaccine |
|       1001 | Malaria Vaccine  |
|       1002 | NewProject       |
+------------+------------------+

Copy

SELECT * FROM employees ORDER BY employee_ID;
+-------------+-----------------+------------+
| EMPLOYEE_ID | EMPLOYEE_NAME   | PROJECT_ID |
|-------------+-----------------+------------|
|    10000001 | Terry Smith     | 1000       |
|    10000002 | Maria Inverness | 1000       |
|    10000003 | Pat Wang        | 1001       |
|    10000004 | NewEmployee     | NULL       |
+-------------+-----------------+------------+

Copy

两个联接的表通常包含一个或多个公共列，以便一个表中的行可以与另一个表中的相应行关联。例如，项目表中的每一行可能都有一个唯一的项目 ID 编号，而员工表中的每一行可能包括当前分配给员工的项目 ID 编号。

联接操作指定（显式或隐式）如何将一个表中的行与另一个表中的相应行关联，通常通过引用公共列（例如项目 ID）。例如，以下内容会联接上面显示的项目和员工表：

SELECT p.project_ID, project_name, employee_ID, employee_name, e.project_ID
    FROM projects AS p JOIN employees AS e
        ON e.project_ID = p.project_ID
    ORDER BY p.project_ID, e.employee_ID;
+------------+------------------+-------------+-----------------+------------+
| PROJECT_ID | PROJECT_NAME     | EMPLOYEE_ID | EMPLOYEE_NAME   | PROJECT_ID |
|------------+------------------+-------------+-----------------+------------|
|       1000 | COVID-19 Vaccine |    10000001 | Terry Smith     | 1000       |
|       1000 | COVID-19 Vaccine |    10000002 | Maria Inverness | 1000       |
|       1001 | Malaria Vaccine  |    10000003 | Pat Wang        | 1001       |
+------------+------------------+-------------+-----------------+------------+

Copy

尽管单个联接操作只能联接两个表，但联接可以链接在一起。联接的结果是类似表的对象，并且该类似表的对象之后可以联接到另一个类似表的对象。从概念上讲，这个想法类似于以下内容（这不是实际的语法）：

table1 join (table2 join table 3)

Copy

在此伪代码中，首先联接 table2 和 table3。然后，从该联接生成的表与 table1 联接。

联接不仅可以应用于表，还可以应用于其他类似表的对象。您可以联接以下内容：

表。
视图（物化或非物化）。
表字面量。
计算结果等效于表（包含一列或多列以及零行或多行）的表达式。例如：
- 表函数返回的结果集。
- 由返回表的子查询返回的结果集。

当本主题提到联接表时，通常意味着联接任何类似表的对象。

备注

Snowflake 可以通过消除不必要的联接来提高性能。有关详细信息，请参阅了解 Snowflake 如何消除冗余联接。

联接类型¶

Snowflake 支持以下联接类型：

内部联接。
外部联接。
交叉联接。
自然联接。
ASOF JOIN。

内部联接¶

内部联接将一个表中的每一行与另一个表中的匹配行配对。

下面的示例使用内部联接：

SELECT p.project_ID, project_name, employee_ID, employee_name, e.project_ID
    FROM projects AS p INNER JOIN employees AS e
        ON e.project_id = p.project_id
    ORDER BY p.project_ID, e.employee_ID;
+------------+------------------+-------------+-----------------+------------+
| PROJECT_ID | PROJECT_NAME     | EMPLOYEE_ID | EMPLOYEE_NAME   | PROJECT_ID |
|------------+------------------+-------------+-----------------+------------|
|       1000 | COVID-19 Vaccine |    10000001 | Terry Smith     | 1000       |
|       1000 | COVID-19 Vaccine |    10000002 | Maria Inverness | 1000       |
|       1001 | Malaria Vaccine  |    10000003 | Pat Wang        | 1001       |
+------------+------------------+-------------+-----------------+------------+

Copy

在此示例中，输出表包含名为“Project_ID”的两列。一个 Project_ID 列来自项目表，一个列来自员工表。对于输出表中的每一行，两个 Project_ID 列中的值匹配，因为查询指定了 e.project_id = p.project_id。

输出仅包含有效对（即，与联接条件匹配的行）。在此示例中，名为“NewProject”的项目（尚未分配给任何员工）或名为“NewEmployee”的员工（尚未分配给任何项目）没有行。

外部联接¶

外部联接列出指定表中的所有行，即使这些行在另一个表中没有匹配项。例如，项目和员工之间的左侧外部联接列出了所有项目，包括尚未分配给任何员工的项目。

SELECT p.project_name, e.employee_name
    FROM projects AS p LEFT OUTER JOIN employees AS e
        ON e.project_ID = p.project_ID
    ORDER BY p.project_name, e.employee_name;
+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Maria Inverness |
| COVID-19 Vaccine | Terry Smith     |
| Malaria Vaccine  | Pat Wang        |
| NewProject       | NULL            |
+------------------+-----------------+

Copy

此输出中包含名为“NewProject”的项目，即使员工表中没有匹配的行也是如此。由于名为“NewProject”的项目没有匹配的员工姓名，因此员工姓名设置为 NULL。

右侧外部联接列出所有员工（无论项目如何）。

SELECT p.project_name, e.employee_name
    FROM projects AS p RIGHT OUTER JOIN employees AS e
        ON e.project_ID = p.project_ID
    ORDER BY p.project_name, e.employee_name;
+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Maria Inverness |
| COVID-19 Vaccine | Terry Smith     |
| Malaria Vaccine  | Pat Wang        |
| NULL             | NewEmployee     |
+------------------+-----------------+

Copy

完整的外部联接列出所有项目和所有员工。

SELECT p.project_name, e.employee_name
    FROM projects AS p FULL OUTER JOIN employees AS e
        ON e.project_ID = p.project_ID
    ORDER BY p.project_name, e.employee_name;
+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Maria Inverness |
| COVID-19 Vaccine | Terry Smith     |
| Malaria Vaccine  | Pat Wang        |
| NewProject       | NULL            |
| NULL             | NewEmployee     |
+------------------+-----------------+

Copy

交叉联接¶

交叉联接将第一个表中的每一行与第二个表中的每一行合并，从而创建所有可能的行组合（称为“笛卡尔积”）。由于大多数结果行包含实际上不相关的部分行，因此交叉联接本身很少有用。实际上，交叉联接通常是意外省略联接条件的结果。

交叉联接的结果可能非常大（且成本高昂）。如果第一个表有 N 行，第二个表有 M 行，则结果是 N x M 行。例如，如果第一个表有 100 行，第二个表有 1000 行，则结果集包含 100,000 行。

以下查询显示交叉联接：

SELECT p.project_name, e.employee_name
    FROM projects AS p CROSS JOIN employees AS e
    ORDER BY p.project_ID, e.employee_ID;
+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Terry Smith     |
| COVID-19 Vaccine | Maria Inverness |
| COVID-19 Vaccine | Pat Wang        |
| COVID-19 Vaccine | NewEmployee     |
| Malaria Vaccine  | Terry Smith     |
| Malaria Vaccine  | Maria Inverness |
| Malaria Vaccine  | Pat Wang        |
| Malaria Vaccine  | NewEmployee     |
| NewProject       | Terry Smith     |
| NewProject       | Maria Inverness |
| NewProject       | Pat Wang        |
| NewProject       | NewEmployee     |
+------------------+-----------------+

Copy

请注意，此查询不包含 ON 子句，也不包含筛选器。

通过在 WHERE 子句中应用筛选器，可以使交叉联接的输出更有用：

SELECT p.project_name, e.employee_name
    FROM projects AS p CROSS JOIN employees AS e
    WHERE e.project_ID = p.project_ID
    ORDER BY p.project_ID, e.employee_ID;
+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Terry Smith     |
| COVID-19 Vaccine | Maria Inverness |
| Malaria Vaccine  | Pat Wang        |
+------------------+-----------------+

Copy

此交叉联接和筛选器的结果与以下内部联接的结果相同：

SELECT p.project_name, e.employee_name
    FROM projects AS p INNER JOIN employees AS e
        ON e.project_ID = p.project_ID
    ORDER BY p.project_ID, e.employee_ID;
+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Terry Smith     |
| COVID-19 Vaccine | Maria Inverness |
| Malaria Vaccine  | Pat Wang        |
+------------------+-----------------+

Copy

重要

尽管此示例中的两个查询在不同的子句（WHERE 与 FROM ... ON ...）中使用相同的条件 (e.project_id = p.project_id) 时产生相同的输出，但也可以构造出使用相同条件但不产生相同输出的查询对。

比较常见的示例涉及外部联接。如果您执行 table1 LEFT OUTER JOIN table2，则对于 table1 中没有匹配项的行，来自 table2 的列包含 NULL。类似 WHERE table2.ID = table1.ID 的筛选器会筛选出 table2.id 或 table1.id 包含 NULL 的行，而 FROM ... ON ... 子句中的显式外部联接不会筛选出带 NULL 值的行。换言之，带筛选器的外部联接的实际工作方式可能不像外部联接。

自然联接¶

当两个表包含具有相同名称的列并且这些列中的数据对应时，使用自然联接。在上面显示的员工表和项目表中，这两个表都有名为“project_ID”的列。自然联接隐式构建 ON 子句： ON projects.project_ID = employees.project_ID。

如果两个表具有多个公共列，则在 ON 子句中使用所有公共列。例如，如果您有两个表，每个表都有名为“city”和“province”的列，则自然联接将构建以下 ON 子句：

ON table2.city = table1.city AND table2.province = table1.province。

自然联接的输出仅包含每个共享列的一个副本。例如，以下查询生成包含两个表中所有列的自然联接，只不过它仅保留了冗余 project_ID 列的一个副本：

SELECT *
    FROM projects NATURAL JOIN employees
    ORDER BY employee_ID;
+------------+------------------+-------------+-----------------+
| PROJECT_ID | PROJECT_NAME     | EMPLOYEE_ID | EMPLOYEE_NAME   |
|------------+------------------+-------------+-----------------|
|       1000 | COVID-19 Vaccine |    10000001 | Terry Smith     |
|       1000 | COVID-19 Vaccine |    10000002 | Maria Inverness |
|       1001 | Malaria Vaccine  |    10000003 | Pat Wang        |
+------------+------------------+-------------+-----------------+

Copy

自然联接可以与外部联接组合。

自然联接不能与 ON 子句组合，因为联接条件已隐含。但是，您可以使用 WHERE 子句来筛选自然联接的结果。

实施联接¶

从语法上讲，有两种方法可以联接表：

在 FROM 子句的 ON 子句中使用 JOIN 运算符。
结合使用 WHERE 与 FROM 子句。

Snowflake 建议在 FROM 子句中使用 ON 子句。语法更灵活。在 ON 子句中指定谓词可以避免在使用 WHERE 子句指定外部联接的联接条件时意外筛选带 NULLs 的行。