PostgreSQL 连接 (Joins)

PostgreSQL - 现代 JOIN 指南

JOIN 子句是 SQL 的基本组成部分，用于根据表之间的关联列来组合两个或多个表中的行。掌握 JOIN 对于有效查询关系型数据至关重要。

设置示例模式

让我们使用一个现代、定义良好的模式。我们将有一个 employees 表和一个 departments 表，它们通过外键关联。这有助于强制执行数据完整性。

CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    salary NUMERIC(10, 2),
    department_id INT,
    CONSTRAINT fk_department
        FOREIGN KEY(department_id)
        REFERENCES departments(id)
);

-- 填充表
INSERT INTO departments (id, name) VALUES
(1, 'Engineering'),
(2, 'Sales'),
(3, 'HR');

INSERT INTO employees (id, name, salary, department_id) VALUES
(1, 'Alice', 95000, 1),
(2, 'Bob', 80000, 2),
(3, 'Charlie', 110000, 1),
(4, 'Diana', 72000, NULL); -- Diana is not assigned to a department

INNER JOIN

INNER JOIN 只返回满足连接条件的行。它选择两个表中具有匹配值的所有行。这是最常见的连接类型。

可视化表示（维恩图）：

      Table A         Table B
    (*********)     (*********)
   (***(XXXXX)*******)***)
  (****(XXXXX)*********)
 (*****(XXXXX)********)
  (****(XXXXX)*********)
   (***(XXXXX)*******)***)
    (*********)_..._/***/)
             ^--- INNER JOIN (Intersection)

SELECT e.name, d.name AS department_name
FROM employees AS e
INNER JOIN departments AS d ON e.department_id = d.id;

结果（注意：Diana 被排除在外，因为她的 department_id 为 NULL）：

  name   | department_name
---------+-----------------
 Alice   | Engineering
 Bob     | Sales
 Charlie | Engineering
(3 rows)

LEFT JOIN (或 LEFT OUTER JOIN)

LEFT JOIN 返回左表（employees）中的所有行，以及右表（departments）中匹配的行。如果没有匹配项，则右表中的列将为 NULL。

可视化表示：

      Table A         Table B
    (XXXXXXXXX)     (*********)
   (XXXXXXXXX(*******)***)
  (XXXXXXXXX(*********)
 (XXXXXXXXX(********)
  (XXXXXXXXX(*********)
   (XXXXXXXXX(*******)***)
    (XXXXXXXXX)_____/***/)
      ^--- LEFT JOIN (All of A, plus matching B)

SELECT e.name, d.name AS department_name
FROM employees AS e
LEFT JOIN departments AS d ON e.department_id = d.id;

结果（Diana 现在被包含在内）：

  name   | department_name
---------+-----------------
 Alice   | Engineering
 Bob     | Sales
 Charlie | Engineering
 Diana   | NULL
(4 rows)

RIGHT JOIN (或 RIGHT OUTER JOIN)

RIGHT JOIN 与 LEFT JOIN 相反。它返回右表（departments）中的所有行，以及左表（employees）中匹配的行。如果没有匹配项，则左表中的列将为 NULL。

SELECT e.name, d.name AS department_name
FROM employees AS e
RIGHT JOIN departments AS d ON e.department_id = d.id;

结果（‘HR’ 部门被包含在内，即使没有员工）：

  name   | department_name
---------+-----------------
 Alice   | Engineering
 Charlie | Engineering
 Bob     | Sales
 NULL    | HR
(4 rows)

FULL OUTER JOIN

FULL OUTER JOIN 结合了 LEFT 和 RIGHT 连接的结果。它返回两个表中的所有行，对于在另一个表中没有匹配项的列，其值为 NULL。

SELECT e.name, d.name AS department_name
FROM employees AS e
FULL OUTER JOIN departments AS d ON e.department_id = d.id;

结果（包含 employees 表中的 Diana 和 departments 表中的 ‘HR’）：

  name   | department_name
---------+-----------------
 Alice   | Engineering
 Bob     | Sales
 Charlie | Engineering
 Diana   | NULL
 NULL    | HR
(5 rows)

其他连接类型

CROSS JOIN

CROSS JOIN 创建两个表的笛卡尔积——第一个表中的每一行都与第二个表中的每一行组合。请谨慎使用，因为它可能产生非常大的结果集。

SELECT e.name, d.name FROM employees AS e CROSS JOIN departments AS d;

SELF JOIN

自连接 (SELF JOIN) 是一种常规连接，但表是与自身连接。这对于查询分层数据或比较同一表中的行非常有用。

-- 查找薪资高于同事 'Bob' 的员工
SELECT e1.name, e1.salary
FROM employees AS e1, employees AS e2
WHERE e2.name = 'Bob' AND e1.salary > e2.salary;

最佳实践：避免使用 NATURAL JOIN

行业最佳实践：避免使用 NATURAL JOIN。它会隐式地根据所有同名列进行连接。如果模式发生变化，这可能导致意想不到的错误结果，使查询变得脆弱且难以调试。请始终明确指定您的连接条件。

当连接列具有相同名称时，一个更安全、更简洁的替代方案是使用 USING：

-- 这不是一个好主意：
-- SELECT * FROM employees NATURAL JOIN departments;

-- 当列名相同时，ON 的更好替代方案：
-- (假设外键列名为 'id' 而不是 'department_id')
-- SELECT e.name, d.name FROM employees e JOIN departments d USING(id);

性能与最终建议

• 使用别名：始终使用简短、清晰的表别名（例如，employees 用 e）以提高可读性。
• 索引外键：为了获得良好的连接性能，请确保 ON 子句中使用的列已建立索引。外键约束通常会自动创建这些索引，但进行验证至关重要。
• 分析查询：使用 EXPLAIN ANALYZE 查看 PostgreSQL 如何执行您的连接。这有助于您识别性能瓶颈并了解索引是否被正确使用。