PostgreSQL 连接查询

连接（JOIN）是关系型数据库的核心操作，用于将多个表的数据组合在一起。本章将详细介绍 PostgreSQL 中的各种连接类型。

连接类型概述

测试数据准备

首先创建用于演示的表：

-- 创建部门表
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, '高级');

内连接（INNER JOIN）

内连接只返回两个表中匹配的行：

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

-- 使用 WHERE 实现等效查询
SELECT 
    e.emp_id,
    e.name,
    d.dept_name
FROM employees e, departments d
WHERE e.dept_id = d.dept_id;

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

结果对比

方式	说明
INNER JOIN ... ON	最常用，明确指定连接条件
INNER JOIN ... USING	列名相同时简化写法
FROM t1, t2 WHERE	传统语法

左外连接（LEFT JOIN）

左外连接返回左表所有行，即使右表中没有匹配：

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

-- 结果：
-- 张三 | 技术部
-- 李四 | 技术部
-- 王五 | 市场部
-- 赵六 | 市场部
-- 孙七 | 财务部
-- 周八 | NULL（没有部门）

右外连接（RIGHT JOIN）

右外连接返回右表所有行，即使左表中没有匹配：

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

-- 结果：
-- 张三 | 技术部
-- 李四 | 技术部
-- 王五 | 市场部
-- 赵六 | 市场部
-- 孙七 | 财务部
-- NULL | 人力资源部（没有员工）

全外连接（FULL OUTER JOIN）

全外连接返回两个表的所有行，没有匹配的显示 NULL：

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

-- 结果：
-- 张三 | 技术部
-- 李四 | 技术部
-- 王五 | 市场部
-- 赵六 | 市场部
-- 孙七 | 财务部
-- 周八 | NULL
-- NULL | 人力资源部

交叉连接（CROSS JOIN）

交叉连接返回两个表的笛卡尔积：

-- 显式交叉连接
SELECT e.name, d.dept_name
FROM employees e
CROSS JOIN departments d;

-- 隐式交叉连接（逗号分隔）
SELECT e.name, d.dept_name
FROM employees e, departments d;

-- 实际应用：生成组合数据
-- 例如：生成所有部门和所有年份的组合
SELECT d.dept_name, y.year
FROM departments d
CROSS JOIN (SELECT generate_series(2020, 2024) AS year) y;

自连接

自连接是表与自身的连接：

-- 创建员工表（包含上级）
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);

-- 查询员工及其上级
SELECT 
    e.name AS employee,
    m.name AS manager
FROM staff e
LEFT JOIN staff m ON e.manager_id = m.id;

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

-- 层级查询：查询所有下级
WITH RECURSIVE org_chart AS (
    -- 起始：CEO
    SELECT id, name, manager_id, 1 AS level
    FROM staff
    WHERE name = 'CEO'
    
    UNION ALL
    
    -- 递归：直接下属
    SELECT s.id, s.name, s.manager_id, oc.level + 1
    FROM staff s
    INNER JOIN org_chart oc ON s.manager_id = oc.id
)
SELECT * FROM org_chart;

多表连接

-- 三表连接
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. 优化连接查询，添加适当的索引