跳到主要内容

PostgreSQL 联接

在关系型数据库中，数据通常分布在多个表中。为了从这些表中提取有意义的信息，我们需要将多个表的数据组合在一起。PostgreSQL 提供了多种联接（Join）操作来实现这一目标。本文将详细介绍 PostgreSQL 中的联接类型及其使用方法。

什么是联接？​

联接（Join）是一种将两个或多个表中的数据组合在一起的操作。通过联接，我们可以根据某些条件将不同表中的行关联起来，从而生成一个包含多个表数据的结果集。

在 PostgreSQL 中，常见的联接类型包括：

  • 内联接（INNER JOIN）
  • 左外联接（LEFT OUTER JOIN）
  • 右外联接（RIGHT OUTER JOIN）
  • 全外联接（FULL OUTER JOIN）
  • 交叉联接（CROSS JOIN）

接下来，我们将逐一介绍这些联接类型。

内联接（INNER JOIN）​

内联接是最常用的联接类型。它返回两个表中满足联接条件的行。如果某一行在其中一个表中没有匹配的行，则该行不会出现在结果集中。

语法​

sql
SELECT 列名
FROM 表1
INNER JOIN 表2
ON 表1.列名 = 表2.列名;

示例​

假设我们有两个表：employees 和 departments。

sql
-- employees 表
id | name       | department_id
---|------------|--------------
1  | Alice      | 1
2  | Bob        | 2
3  | Charlie    | NULL

-- departments 表
id | name
---|---------
1  | HR
2  | Engineering
3  | Marketing

我们想要获取每个员工的姓名及其所属部门的名称：

sql
SELECT employees.name, departments.name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.id;

输出​

name    | name
--------|------------
Alice   | HR
Bob     | Engineering
备注

注意：Charlie 没有出现在结果集中，因为他的 department_id 为 NULL，没有匹配的部门。

左外联接（LEFT OUTER JOIN）​

左外联接返回左表中的所有行，即使右表中没有匹配的行。如果右表中没有匹配的行，则结果集中右表的列将包含 NULL。

语法​

sql
SELECT 列名
FROM 表1
LEFT OUTER JOIN 表2
ON 表1.列名 = 表2.列名;

示例​

继续使用 employees 和 departments 表，我们想要获取所有员工的姓名及其所属部门的名称，即使某些员工没有分配部门：

sql
SELECT employees.name, departments.name
FROM employees
LEFT OUTER JOIN departments
ON employees.department_id = departments.id;

输出​

name    | name
--------|------------
Alice   | HR
Bob     | Engineering
Charlie | NULL
提示

提示：左外联接确保左表中的所有行都会出现在结果集中，即使右表中没有匹配的行。

右外联接（RIGHT OUTER JOIN）​

右外联接与左外联接相反，它返回右表中的所有行，即使左表中没有匹配的行。如果左表中没有匹配的行，则结果集中左表的列将包含 NULL。

语法​

sql
SELECT 列名
FROM 表1
RIGHT OUTER JOIN 表2
ON 表1.列名 = 表2.列名;

示例​

我们想要获取所有部门的名称及其所属员工的姓名，即使某些部门没有员工：

sql
SELECT employees.name, departments.name
FROM employees
RIGHT OUTER JOIN departments
ON employees.department_id = departments.id;

输出​

name    | name
--------|------------
Alice   | HR
Bob     | Engineering
NULL    | Marketing
警告

注意：Marketing 部门没有员工，因此 employees.name 为 NULL。

全外联接（FULL OUTER JOIN）​

全外联接返回左表和右表中的所有行。如果某一行在其中一个表中没有匹配的行，则结果集中对应的列将包含 NULL。

语法​

sql
SELECT 列名
FROM 表1
FULL OUTER JOIN 表2
ON 表1.列名 = 表2.列名;

示例​

我们想要获取所有员工和部门的信息，无论是否有匹配的行：

sql
SELECT employees.name, departments.name
FROM employees
FULL OUTER JOIN departments
ON employees.department_id = departments.id;

输出​

name    | name
--------|------------
Alice   | HR
Bob     | Engineering
Charlie | NULL
NULL    | Marketing
注意

警告：全外联接可能会生成较大的结果集，因为它包含两个表中的所有行。

交叉联接（CROSS JOIN）​

交叉联接返回两个表的笛卡尔积，即左表中的每一行与右表中的每一行组合。结果集的行数等于左表的行数乘以右表的行数。

语法​

sql
SELECT 列名
FROM 表1
CROSS JOIN 表2;

示例​

我们想要获取所有员工和部门的组合：

sql
SELECT employees.name, departments.name
FROM employees
CROSS JOIN departments;

输出​

name    | name
--------|------------
Alice   | HR
Alice   | Engineering
Alice   | Marketing
Bob     | HR
Bob     | Engineering
Bob     | Marketing
Charlie | HR
Charlie | Engineering
Charlie | Marketing
备注

注意：交叉联接通常用于生成所有可能的组合，但在实际应用中应谨慎使用，因为它可能会生成非常大的结果集。

实际应用场景​

假设你正在开发一个员工管理系统，需要生成一份报告，列出所有员工及其所属部门。如果某些员工没有分配部门，或者某些部门没有员工，你仍然希望这些信息出现在报告中。这时，你可以使用左外联接或全外联接来确保所有数据都被包含在结果集中。

总结​

PostgreSQL 提供了多种联接类型，每种类型都有其特定的用途。通过掌握这些联接操作，你可以更灵活地从多个表中提取和组合数据。在实际应用中，选择合适的联接类型可以帮助你更高效地处理数据。

附加资源与练习​

  • 练习 1：创建一个包含 orders 和 customers 表的数据库，并使用内联接获取每个订单的客户信息。
  • 练习 2：使用左外联接获取所有客户及其订单信息，即使某些客户没有订单。
  • 练习 3：使用全外联接获取所有客户和订单的信息，无论是否有匹配的行。

通过完成这些练习，你将更深入地理解 PostgreSQL 中的联接操作。祝你学习愉快！