类别：: 查询语法

使用联接¶

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

简介¶

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

CREATE TABLE projects (
  project_id INT,
  project_name VARCHAR);

INSERT INTO projects VALUES
  (1000, 'COVID-19 Vaccine'),
  (1001, 'Malaria Vaccine'),
  (1002, 'NewProject');

CREATE TABLE employees (
  employee_id INT,
  employee_name VARCHAR,
  project_id INT);

INSERT INTO employees VALUES
  (10000001, 'Terry Smith', 1000),
  (10000002, 'Maria Inverness', 1000),
  (10000003, 'Pat Wang', 1001),
  (10000004, 'NewEmployee', NULL);

Copy

查询表以查看数据：

SELECT * FROM projects ORDER BY project_ID;

Copy

+------------+------------------+
| PROJECT_ID | PROJECT_NAME     |
|------------+------------------|
|       1000 | COVID-19 Vaccine |
|       1001 | Malaria Vaccine  |
|       1002 | NewProject       |
+------------+------------------+

SELECT * FROM employees ORDER BY employee_ID;

Copy

+-------------+-----------------+------------+
| EMPLOYEE_ID | EMPLOYEE_NAME   | PROJECT_ID |
|-------------+-----------------+------------|
|    10000001 | Terry Smith     |       1000 |
|    10000002 | Maria Inverness |       1000 |
|    10000003 | Pat Wang        |       1001 |
|    10000004 | NewEmployee     |       NULL |
+-------------+-----------------+------------+

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

联接操作指定（显式或隐式）如何将一个表中的行与另一个表中的相应行关联，通常通过引用一个或多个公共列（例如 project_id）。例如，以下内容会联接之前创建的 projects 和 employees 表：

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;

Copy

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

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

table1 JOIN (table2 JOIN table3)

Copy

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

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

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

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

备注

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

联接类型¶

Snowflake 支持以下联接类型：

内部联接
外部联接
交叉联接
自然联接

备注

Snowflake 还支持 ASOF JOIN，用于分析时间序列数据。有关更多信息，请参阅 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;

Copy

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

在此示例中，输出表包含名为 PROJECT_ID 的两列。一个 PROJECT_ID 列来自 projects 表，一列来自 employees 表。对于输出表中的每一行，两个 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;

Copy

+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Maria Inverness |
| COVID-19 Vaccine | Terry Smith     |
| Malaria Vaccine  | Pat Wang        |
| NewProject       | NULL            |
+------------------+-----------------+

此输出中包含名为 NewProject 的项目，即使 employees 表中没有匹配的行也是如此。由于名为 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;

Copy

+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Maria Inverness |
| COVID-19 Vaccine | Terry Smith     |
| Malaria Vaccine  | Pat Wang        |
| NULL             | NewEmployee     |
+------------------+-----------------+

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

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;

Copy

+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Maria Inverness |
| COVID-19 Vaccine | Terry Smith     |
| Malaria Vaccine  | Pat Wang        |
| NewProject       | NULL            |
| NULL             | NewEmployee     |
+------------------+-----------------+

交叉联接¶

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

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

以下查询显示交叉联接：

备注

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

SELECT p.project_name, e.employee_name
  FROM projects AS p CROSS JOIN employees AS e
  ORDER BY p.project_ID, e.employee_ID;

Copy

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

您可以通过在 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;

Copy

+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Terry Smith     |
| COVID-19 Vaccine | Maria Inverness |
| Malaria Vaccine  | Pat Wang        |
+------------------+-----------------+

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

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;

Copy

+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Terry Smith     |
| COVID-19 Vaccine | Maria Inverness |
| Malaria Vaccine  | Pat Wang        |
+------------------+-----------------+

重要

尽管此示例中的两个查询在不同的子句（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 值的行。换言之，带筛选器的外部联接的工作方式可能不像外部联接。

自然联接¶

自然联接会在具有相同名称且数据类型兼容的列上，将两个表进行联接。employees 和先前创建的 projects 表都有一个名为 project_ID 的列。自然联接隐式构建 ON 子句：ON projects.project_ID = employees.project_ID。

如果两个表有多个共同的列，则自然连接使用构造的 ON 子句中的所有公用列。例如，如果两个表各有名为 city 和 province 的列，则自然联接将构造以下 ON 子句：

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

Copy

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

SELECT *
  FROM projects NATURAL JOIN employees
  ORDER BY employee_id;

Copy

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

您可以将自然联接与外部联接进行组合。

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

实施联接¶

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

在 FROM 子句的 ON 分子句中使用 JOIN 分子句。
结合使用 WHERE 子句与 FROM 子句。

Snowflake 建议在 FROM 子句中使用 ON 分子句，因为其语法更加灵活。此外，在 ON 分子句中指定谓词可以避免在使用 WHERE 子句指定外部联接的联接条件时意外筛选带 NULL 值的行。

此外，您可以使用 DIRECTED 关键字强制执行表的联接顺序。当您指定此关键字时，首先扫描第一个或左边的表，然后再扫描第二个或右边的表。例如，o1 INNER DIRECTED JOIN o2 先扫描 o1 表，然后再扫描 o2 表。如果添加了 DIRECTED 关键字，则联接类型（例如，INNER 或 OUTER）为必填项。有关更多信息，请参阅 JOIN。

备注

定向联接是一项预览版功能，适用于所有账户。