PostgreSQL 连接查询

连接（JOIN）是关系型数据库的核心操作，用于将多个表的数据组合在一起。理解和掌握连接查询是数据库开发的基础技能，本章将详细介绍 PostgreSQL 中的各种连接类型及其应用场景。

官方文档

本教程内容基于 PostgreSQL 官方文档 - Table Expressions。

连接的本质

在关系型数据库中，数据通常被分散在多个表中，以减少冗余并保持数据一致性。连接操作让我们能够将这些分散的数据重新组合起来，形成有意义的查询结果。

连接的核心思想是：基于某种条件，将两个表中的行匹配起来，生成新的结果行。理解连接的关键在于理解"匹配"的含义——不同的连接类型定义了不同的匹配规则。

连接类型的分类

PostgreSQL 支持以下连接类型：

连接类型	关键字	特点
交叉连接	CROSS JOIN	笛卡尔积，所有组合
内连接	INNER JOIN	只返回匹配的行
左外连接	LEFT [OUTER] JOIN	左表所有行，右表匹配或 NULL
右外连接	RIGHT [OUTER] JOIN	右表所有行，左表匹配或 NULL
全外连接	FULL [OUTER] JOIN	两表所有行，不匹配为 NULL
自然连接	NATURAL JOIN	自动匹配同名列

INNER 和 OUTER 关键字是可选的，INNER 是默认值，LEFT、RIGHT、FULL 隐含了外连接。

测试数据准备

首先创建用于演示的表：

-- 创建部门表
CREATE TABLE departments (
    dept_id SERIAL PRIMARY KEY,
    dept_name VARCHAR(50),
    location VARCHAR(50)
);

INSERT INTO departments (dept_name, location) VALUES
('技术部', '北京'),
('市场部', '上海'),
('财务部', '广州'),
('人力资源部', '深圳');

-- 创建员工表
CREATE TABLE employees (
    emp_id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    dept_id INTEGER,
    salary NUMERIC(10, 2)
);

INSERT INTO employees (name, dept_id, salary) VALUES
('张三', 1, 15000),
('李四', 1, 12000),
('王五', 2, 10000),
('赵六', 2, 11000),
('孙七', 3, 13000),
('周八', NULL, 9000);

-- 创建工资等级表
CREATE TABLE salary_grades (
    grade_id SERIAL PRIMARY KEY,
    min_salary NUMERIC(10, 2),
    max_salary NUMERIC(10, 2),
    grade_name VARCHAR(20)
);

INSERT INTO salary_grades (min_salary, max_salary, grade_name) VALUES
(0, 10000, '初级'),
(10001, 15000, '中级'),
(15001, 20000, '高级');

数据说明：

• 部门表有 4 个部门，员工表有 6 个员工
• 员工"周八"没有分配部门（dept_id 为 NULL）
• 部门"人力资源部"没有员工

内连接（INNER JOIN）

内连接是最常用的连接类型，只返回两个表中满足连接条件的行。对于 T1 中的每一行，如果在 T2 中找到了满足条件的匹配行，就会生成一个结果行。

基本语法

SELECT columns
FROM table1
INNER JOIN table2 ON join_condition;

-- INNER 关键字可选
SELECT columns
FROM table1
JOIN table2 ON join_condition;

使用 ON 子句

ON 子句是最通用的连接条件指定方式，接受一个布尔表达式：

-- 基本内连接
SELECT 
    e.emp_id,
    e.name,
    d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

-- 结果：只有分配了部门的员工
-- emp_id | name  | dept_name
-- --------+-------+-----------
-- 1      | 张三  | 技术部
-- 2      | 李四  | 技术部
-- 3      | 王五  | 市场部
-- 4      | 赵六  | 市场部
-- 5      | 孙七  | 财务部
-- 注意：周八没有出现在结果中（dept_id 为 NULL）
-- 注意：人力资源部没有出现在结果中（没有员工）

ON 子句的条件可以包含任何有效的布尔表达式，不仅限于等值比较：

-- 非等值连接：查找工资对应的等级
SELECT 
    e.name,
    e.salary,
    s.grade_name
FROM employees e
INNER JOIN salary_grades s ON e.salary >= s.min_salary AND e.salary <= s.max_salary;

-- 使用 BETWEEN 简化
SELECT 
    e.name,
    e.salary,
    s.grade_name
FROM employees e
INNER JOIN salary_grades s ON e.salary BETWEEN s.min_salary AND s.max_salary;

-- 结果：
-- name | salary  | grade_name
-- ------+---------+------------
-- 张三 | 15000.00| 中级
-- 李四 | 12000.00| 中级
-- 王五 | 10000.00| 初级
-- 赵六 | 11000.00| 中级
-- 孙七 | 13000.00| 中级
-- 周八 | 9000.00 | 初级

使用 USING 子句

当连接的两个表有相同的列名时，可以使用 USING 子句简化：

-- 使用 USING（列名相同时）
SELECT 
    emp_id,
    name,
    dept_name
FROM employees
INNER JOIN departments USING (dept_id);

-- 等价于
SELECT 
    employees.emp_id,
    employees.name,
    departments.dept_name
FROM employees
INNER JOIN departments ON employees.dept_id = departments.dept_id;

ON 与 USING 的重要区别：

USING 子句有一个重要特性：它会合并匹配的列，只输出一次。而 ON 子句会保留两个表的列：

-- 使用 ON：dept_id 列出现两次
SELECT * FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

-- 结果包含：emp_id, name, dept_id (from employees), salary, dept_id (from departments), dept_name, location

-- 使用 USING：dept_id 列只出现一次
SELECT * FROM employees
INNER JOIN departments USING (dept_id);

-- 结果包含：dept_id, emp_id, name, salary, dept_name, location

传统语法（逗号连接）

使用逗号分隔表并在 WHERE 子句中指定连接条件是传统的写法：

-- 传统语法
SELECT 
    e.emp_id,
    e.name,
    d.dept_name
FROM employees e, departments d
WHERE e.dept_id = d.dept_id;

-- 等价于
SELECT 
    e.emp_id,
    e.name,
    d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

注意：虽然两种写法结果相同，但推荐使用显式的 JOIN 语法，原因如下：

• 可读性更好，连接条件与过滤条件分离
• 不容易遗漏连接条件（逗号语法忘记 WHERE 会产生笛卡尔积）
• 支持外连接等更复杂的操作

连接条件的最佳实践

1. 明确指定表别名：当多个表有相同列名时，使用别名避免歧义

-- 推荐：使用别名
SELECT e.name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;

-- 不推荐：不使用别名（当列名唯一时可以，但不够清晰）
SELECT name, dept_name
FROM employees
JOIN departments ON employees.dept_id = departments.dept_id;

外连接（OUTER JOIN）

外连接与内连接的区别在于：外连接会保留至少一个表的所有行，即使在另一个表中没有匹配。外连接分为三种：左外连接、右外连接和全外连接。

左外连接（LEFT OUTER JOIN）

左外连接返回左表（FROM 子句中的表）的所有行，对于右表中没有匹配的行，右表的列显示为 NULL。

执行过程：

1. 首先执行内连接
2. 对于左表中未匹配的行，添加一个结果行，右表的列填充 NULL

-- 左外连接
SELECT 
    e.name,
    e.dept_id,
    d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;

-- 结果：
-- name | dept_id | dept_name
-- ------+---------+-----------
-- 张三 | 1       | 技术部
-- 李四 | 1       | 技术部
-- 王五 | 2       | 市场部
-- 赵六 | 2       | 市场部
-- 孙七 | 3       | 财务部
-- 周八 | NULL    | NULL      ← 左表所有行都在，即使没有匹配

常见应用场景：

-- 场景1：查找没有分配部门的员工
SELECT e.name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_id IS NULL;

-- 结果：周八

-- 场景2：统计每个部门的员工数量（包括没有员工的部门）
SELECT 
    d.dept_name,
    COUNT(e.emp_id) AS employee_count
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dept_name;

-- 结果：
-- dept_name    | employee_count
-- -------------+---------------
-- 技术部       | 2
-- 市场部       | 2
-- 财务部       | 1
-- 人力资源部   | 0             ← 没有员工的部门也会出现

重要提示：在左连接中查找未匹配的行时，必须使用 IS NULL 而不是 = NULL，因为 NULL = NULL 的结果是 NULL（未知），不是 true。

右外连接（RIGHT OUTER JOIN）

右外连接与左外连接对称，返回右表（JOIN 子句后的表）的所有行。

执行过程：

1. 首先执行内连接
2. 对于右表中未匹配的行，添加一个结果行，左表的列填充 NULL

-- 右外连接
SELECT 
    e.name,
    d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;

-- 结果：
-- name | dept_name
-- ------+-----------
-- 张三 | 技术部
-- 李四 | 技术部
-- 王五 | 市场部
-- 赵六 | 市场部
-- 孙七 | 财务部
-- NULL | 人力资源部  ← 右表所有行都在，即使没有匹配

转换技巧：任何右连接都可以改写为左连接，只需交换表的顺序：

-- 这两个查询等价
SELECT e.name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;

SELECT e.name, d.dept_name
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id;

实践中，建议统一使用左连接，因为从左到右的阅读顺序更符合直觉。

全外连接（FULL OUTER JOIN）

全外连接返回两个表的所有行，对于没有匹配的行，另一表的列显示 NULL。

执行过程：

1. 首先执行内连接
2. 对于左表中未匹配的行，添加结果行，右表列填充 NULL
3. 对于右表中未匹配的行，添加结果行，左表列填充 NULL

-- 全外连接
SELECT 
    e.name,
    d.dept_name
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.dept_id;

-- 结果：
-- name | dept_name
-- ------+-----------
-- 张三 | 技术部
-- 李四 | 技术部
-- 王五 | 市场部
-- 赵六 | 市场部
-- 孙七 | 财务部
-- 周八 | NULL        ← 左表未匹配的行
-- NULL | 人力资源部   ← 右表未匹配的行

常见应用场景：

-- 查找所有未匹配的行（双向差异）
SELECT 
    e.name AS employee_without_dept,
    d.dept_name AS dept_without_employee
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.dept_id
WHERE e.emp_id IS NULL OR d.dept_id IS NULL;

-- 结果：
-- employee_without_dept | dept_without_employee
-- ----------------------+----------------------
-- 周八                 | NULL
-- NULL                 | 人力资源部

外连接的图示理解

可以用集合来理解外连接：

左连接：返回 [左表] ∪ [交集]
右连接：返回 [交集] ∪ [右表]
全连接：返回 [左表] ∪ [交集] ∪ [右表]
内连接：只返回 [交集]

交叉连接（CROSS JOIN）

交叉连接返回两个表的笛卡尔积——左表的每一行与右表的每一行组合。如果左表有 M 行，右表有 N 行，结果将有 M × N 行。

基本语法

-- 显式语法
SELECT columns
FROM table1
CROSS JOIN table2;

-- 隐式语法（逗号分隔，不推荐）
SELECT columns
FROM table1, table2;

示例

-- 员工（6行）与部门（4行）的笛卡尔积
SELECT e.name, d.dept_name
FROM employees e
CROSS JOIN departments d;

-- 结果：24 行（6 × 4）
-- name | dept_name
-- ------+-----------
-- 张三 | 技术部
-- 张三 | 市场部
-- 张三 | 财务部
-- 张三 | 人力资源部
-- 李四 | 技术部
-- 李四 | 市场部
-- ... 以此类推

实际应用场景

交叉连接通常不是我们想要的结果，但在某些场景下很有用：

-- 场景1：生成测试数据
-- 为每个部门生成每个月的测试记录
SELECT 
    d.dept_name,
    m.month_name,
    0 AS placeholder_value
FROM departments d
CROSS JOIN (
    SELECT '一月' AS month_name UNION ALL
    SELECT '二月' UNION ALL
    SELECT '三月' UNION ALL
    SELECT '四月' UNION ALL
    SELECT '五月' UNION ALL
    SELECT '六月'
) m;

-- 场景2：生成所有组合用于报表
-- 所有产品与所有销售区域的组合
SELECT p.product_name, r.region_name
FROM products p
CROSS JOIN sales_regions r;

-- 场景3：使用 generate_series 生成序列
SELECT 
    d.dept_name,
    y.year
FROM departments d
CROSS JOIN (SELECT generate_series(2020, 2024) AS year) y;

-- 结果：
-- dept_name    | year
-- -------------+-----
-- 技术部       | 2020
-- 技术部       | 2021
-- 技术部       | 2022
-- 技术部       | 2023
-- 技术部       | 2024
-- 市场部       | 2020
-- ... 以此类推

注意事项

性能警告：交叉连接可能产生大量结果行。对于大型表，结果可能非常庞大：

-- 危险：两个大表的交叉连接
-- 如果两个表各有 10,000 行，结果将有 100,000,000 行！
SELECT * FROM large_table1
CROSS JOIN large_table2;  -- 可能导致内存耗尽

逗号语法的隐患：使用逗号分隔表时，如果忘记 WHERE 子句，会产生交叉连接：

-- 本意是内连接，但忘记 WHERE 子句
SELECT e.name, d.dept_name
FROM employees e, departments d;  -- 这是交叉连接，不是内连接！

-- 正确的内连接写法
SELECT e.name, d.dept_name
FROM employees e, departments d
WHERE e.dept_id = d.dept_id;

这也是为什么推荐使用显式 JOIN 语法的原因之一。

自连接（Self Join）

自连接是一个表与自身的连接。这在处理层级数据、查找相关记录等场景中非常有用。自连接本身不是一种特殊的连接类型，而是一种连接模式——可以使用内连接、外连接等任何类型。

层级数据查询

最常见的自连接场景是处理组织结构、分类层级等数据：

-- 创建员工表（包含上级信息）
CREATE TABLE staff (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    manager_id INTEGER REFERENCES staff(id)
);

INSERT INTO staff (name, manager_id) VALUES
('CEO', NULL),
('CTO', 1),
('CFO', 1),
('开发经理', 2),
('测试经理', 2),
('会计', 3),
('开发工程师', 4),
('测试工程师', 5);

查询员工及其直接上级

-- 使用左外连接，确保没有上级的员工（CEO）也出现在结果中
SELECT 
    e.name AS employee,
    m.name AS manager
FROM staff e
LEFT JOIN staff m ON e.manager_id = m.id;

-- 结果：
-- employee    | manager
-- ------------+-----------
-- CEO         | NULL
-- CTO         | CEO
-- CFO         | CEO
-- 开发经理    | CTO
-- 测试经理    | CTO
-- 会计        | CFO
-- 开发工程师  | 开发经理
-- 测试工程师  | 测试经理

查找同级别的员工

-- 查找同一个经理下的员工对
SELECT 
    e1.name AS employee1,
    e2.name AS employee2,
    m.name AS common_manager
FROM staff e1
INNER JOIN staff e2 ON e1.manager_id = e2.manager_id AND e1.id < e2.id
INNER JOIN staff m ON e1.manager_id = m.id;

-- 结果：
-- employee1   | employee2   | common_manager
-- ------------+-------------+----------------
-- CTO         | CFO         | CEO
-- 开发经理    | 测试经理    | CTO
-- 开发工程师  | 测试工程师  | 开发经理

使用 CTE 进行递归查询

对于多层级的数据，PostgreSQL 提供了 WITH RECURSIVE 语法：

-- 查询完整的组织架构树
WITH RECURSIVE org_tree AS (
    -- 基础查询：顶级员工（没有上级）
    SELECT 
        id, 
        name, 
        manager_id, 
        1 AS level,
        name::TEXT AS path
    FROM staff
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 递归查询：下级员工
    SELECT 
        s.id, 
        s.name, 
        s.manager_id, 
        ot.level + 1,
        ot.path || ' > ' || s.name
    FROM staff s
    INNER JOIN org_tree ot ON s.manager_id = ot.id
)
SELECT 
    REPEAT('  ', level - 1) || name AS org_chart,
    level,
    path
FROM org_tree
ORDER BY path;

-- 结果：
-- org_chart      | level | path
-- ---------------+-------+-----------------------------
-- CEO            | 1     | CEO
--   CTO          | 2     | CEO > CTO
--     开发经理    | 3     | CEO > CTO > 开发经理
--       开发工程师| 4     | CEO > CTO > 开发经理 > 开发工程师
--     测试经理    | 3     | CEO > CTO > 测试经理
--       测试工程师| 4     | CEO > CTO > 测试经理 > 测试工程师
--   CFO          | 2     | CEO > CFO
--     会计       | 3     | CEO > CFO > 会计

自连接的其他应用场景

-- 场景1：查找重复记录
SELECT a.*
FROM records a
INNER JOIN records b ON a.email = b.email AND a.id < b.id;

-- 场景2：比较同一表中不同时期的记录
SELECT 
    curr.product_name,
    curr.sales AS current_sales,
    prev.sales AS previous_sales,
    curr.sales - prev.sales AS growth
FROM sales_data curr
INNER JOIN sales_data prev ON curr.product_id = prev.product_id
    AND curr.period = '2024-Q1'
    AND prev.period = '2023-Q1';

-- 场景3：查找相关记录（如同一作者的文章）
SELECT a1.title AS article1, a2.title AS article2
FROM articles a1
INNER JOIN articles a2 ON a1.author_id = a2.author_id AND a1.id < a2.id;

多表连接

-- 三表连接
SELECT 
    e.name,
    d.dept_name,
    s.grade_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
INNER JOIN salary_grades s ON e.salary >= s.min_salary AND e.salary <= s.max_salary;

-- 连接多个表的简化写法
SELECT 
    e.name,
    d.dept_name,
    s.grade_name
FROM employees e
JOIN departments d USING (dept_id)
JOIN salary_grades s ON e.salary BETWEEN s.min_salary AND s.max_salary;

自然连接（NATURAL JOIN）

自然连接基于相同列名自动连接：

-- 自然连接
SELECT *
FROM employees
NATURAL JOIN departments;

-- 注意：如果列名相同但类型不同会报错
-- 建议：明确指定连接条件而非使用 NATURAL JOIN

连接条件与过滤条件

-- 区别：ON 条件 vs WHERE 条件
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id AND d.dept_name = '技术部';

-- 结果：所有员工，技术部只显示技术部，其他显示 NULL
-- 张三 | 技术部
-- 李四 | 技术部
-- 王五 | NULL
-- 赵六 | NULL
-- 孙七 | NULL
-- 周八 | NULL

-- 如果用 WHERE 过滤：
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_name = '技术部';

-- 结果：只显示技术部员工
-- 张三 | 技术部
-- 李四 | 技术部

Lateral 连接

PostgreSQL 支持 LATERAL 子查询，可以引用前面的表：

-- 查找每个部门工资最高的员工
SELECT d.dept_name, e.name, e.salary
FROM departments d
CROSS JOIN LATERAL (
    SELECT name, salary
    FROM employees
    WHERE dept_id = d.dept_id
    ORDER BY salary DESC
    LIMIT 1
) e;

-- 使用 LATERAL 获取前 N 条记录
SELECT d.dept_name, e.name, e.salary
FROM departments d
CROSS JOIN LATERAL (
    SELECT name, salary
    FROM employees
    WHERE dept_id = d.dept_id
    ORDER BY salary DESC
    LIMIT 2
) e;

连接的执行顺序

-- PostgreSQL 连接顺序优化
-- 注意：ON 条件的顺序可能影响性能

-- 效率低：大表先连接
SELECT *
FROM large_table l
JOIN small_table s ON l.id = s.id;

-- 效率高：小表先连接（PostgreSQL 会自动优化）
SELECT *
FROM small_table s
JOIN large_table l ON l.id = s.id;

-- 强制连接顺序
SELECT *
FROM large_table l
JOIN small_table s ON l.id = s.id
JOIN medium_table m ON m.id = s.id;

连接的性能优化

-- 创建索引优化连接
CREATE INDEX idx_employees_dept ON employees(dept_id);
CREATE INDEX idx_departments_id ON departments(dept_id);

-- 复合索引
CREATE INDEX idx_employees_salary_dept ON employees(salary, dept_id);

-- 查看查询计划
EXPLAIN SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

-- 详细分析
EXPLAIN ANALYZE SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

小结

本章我们学习了 PostgreSQL 的各种连接类型：

连接类型	说明	特点
INNER JOIN	内连接	只返回匹配行
LEFT JOIN	左外连接	返回左表所有行
RIGHT JOIN	右外连接	返回右表所有行
FULL OUTER JOIN	全外连接	返回所有行
CROSS JOIN	交叉连接	笛卡尔积
NATURAL JOIN	自然连接	自动基于相同列名
LATERAL JOIN	横向连接	允许子查询引用外层表

练习

1. 查询所有员工及其部门名称（包括没有部门的员工）
2. 查询所有部门及其员工（包括没有员工的部门）
3. 查询每个部门的员工数量和平均工资
4. 使用自连接查询员工的上级的上级
5. 优化连接查询，添加适当的索引