PostgreSQL 多表查询 #

┌─────────────────────────────────────────────────────────────┐
│                    JOIN 类型                                 │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  INNER JOIN    只返回匹配的行                               │
│  LEFT JOIN     返回左表所有行，右表匹配行                   │
│  RIGHT JOIN    返回右表所有行，左表匹配行                   │
│  FULL JOIN     返回两表所有行                               │
│  CROSS JOIN    笛卡尔积                                     │
│  SELF JOIN     自连接（同一表连接）                         │
│                                                             │
│  ┌───────────┐         ┌───────────┐                       │
│  │   表 A    │         │   表 B    │                       │
│  │  ┌─────┐  │         │  ┌─────┐  │                       │
│  │  │     │  │         │  │     │  │                       │
│  │  │  A  ├──┼─────────┼──┤  B  │  │  INNER JOIN           │
│  │  │     │  │         │  │     │  │                       │
│  │  └─────┘  │         │  └─────┘  │                       │
│  │  ┌─────┐  │         │           │                       │
│  │  │     │  │         │           │  LEFT JOIN            │
│  │  └─────┘  │         │           │                       │
│  │           │         │  ┌─────┐  │                       │
│  │           │         │  │     │  │  RIGHT JOIN           │
│  │           │         │  └─────┘  │                       │
│  └───────────┘         └───────────┘                       │
│                                                             │
└─────────────────────────────────────────────────────────────┘

-- 创建示例表
CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    location VARCHAR(100)
);

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department_id INTEGER REFERENCES departments(id),
    salary DECIMAL(10, 2),
    manager_id INTEGER REFERENCES employees(id)
);

CREATE TABLE projects (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department_id INTEGER REFERENCES departments(id)
);

INSERT INTO departments (name, location) VALUES
    ('Engineering', 'Building A'),
    ('Sales', 'Building B'),
    ('Marketing', 'Building C');
    -- id: 1, 2, 3

INSERT INTO employees (name, department_id, salary, manager_id) VALUES
    ('Alice', 1, 90000.00, NULL),      -- CEO
    ('Bob', 1, 80000.00, 1),           -- Engineering
    ('Charlie', 1, 85000.00, 1),       -- Engineering
    ('Diana', 2, 70000.00, 1),         -- Sales
    ('Eve', 2, 65000.00, 4),           -- Sales
    ('Frank', NULL, 60000.00, 1);      -- No department

INSERT INTO projects (name, department_id) VALUES
    ('Project Alpha', 1),
    ('Project Beta', 1),
    ('Project Gamma', 2);

-- INNER JOIN：只返回两表都匹配的行
SELECT 
    e.name AS employee_name,
    e.salary,
    d.name AS department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

-- 输出：
--  employee_name | salary  | department_name
-- ---------------+---------+----------------
--  Alice         | 90000.00| Engineering
--  Bob           | 80000.00| Engineering
--  Charlie       | 85000.00| Engineering
--  Diana         | 70000.00| Sales
--  Eve           | 65000.00| Sales
-- Frank 没有部门，不显示

-- 使用 USING（列名相同时）
SELECT 
    e.name,
    d.name AS department
FROM employees e
INNER JOIN departments d USING (id);  -- 不推荐，这里 id 含义不同

-- 多条件连接
SELECT 
    e.name,
    d.name AS department,
    d.location
FROM employees e
INNER JOIN departments d 
    ON e.department_id = d.id 
    AND d.location = 'Building A';

-- 连接三个表
SELECT 
    e.name AS employee,
    d.name AS department,
    p.name AS project
FROM employees e
INNER JOIN departments d ON e.department_id = d.id
INNER JOIN projects p ON d.id = p.department_id;

-- 输出：
--  employee | department  |    project
-- ----------+-------------+---------------
--  Alice    | Engineering | Project Alpha
--  Alice    | Engineering | Project Beta
--  Bob      | Engineering | Project Alpha
--  Bob      | Engineering | Project Beta
--  Charlie  | Engineering | Project Alpha
--  Charlie  | Engineering | Project Beta
--  Diana    | Sales       | Project Gamma
--  Eve      | Sales       | Project Gamma

-- LEFT JOIN：返回左表所有行，右表匹配行（无匹配则为 NULL）
SELECT 
    e.name AS employee_name,
    e.salary,
    d.name AS department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

-- 输出：
--  employee_name | salary  | department_name
-- ---------------+---------+----------------
--  Alice         | 90000.00| Engineering
--  Bob           | 80000.00| Engineering
--  Charlie       | 85000.00| Engineering
--  Diana         | 70000.00| Sales
--  Eve           | 65000.00| Sales
--  Frank         | 60000.00| NULL           -- Frank 没有部门

-- LEFT OUTER JOIN 是 LEFT JOIN 的完整写法
SELECT e.name, d.name AS department
FROM employees e
LEFT OUTER JOIN departments d ON e.department_id = d.id;

-- 查找没有部门的员工
SELECT e.name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
WHERE d.id IS NULL;

-- 输出：
--   name
-- --------
--  Frank

-- 查找没有员工的项目
SELECT p.name AS project
FROM projects p
LEFT JOIN departments d ON p.department_id = d.id
LEFT JOIN employees e ON d.id = e.department_id
WHERE e.id IS NULL;

-- 查找没有项目的部门
SELECT d.name AS department
FROM departments d
LEFT JOIN projects p ON d.id = p.department_id
WHERE p.id IS NULL;

-- RIGHT JOIN：返回右表所有行，左表匹配行
SELECT 
    e.name AS employee_name,
    d.name AS department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

-- 输出：
--  employee_name | department_name
-- ---------------+----------------
--  Alice         | Engineering
--  Bob           | Engineering
--  Charlie       | Engineering
--  Diana         | Sales
--  Eve           | Sales
--  NULL          | Marketing      -- Marketing 没有员工

-- RIGHT JOIN 可以改写为 LEFT JOIN（推荐）
SELECT 
    e.name AS employee_name,
    d.name AS department_name
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id;

-- FULL JOIN：返回两表所有行
SELECT 
    e.name AS employee_name,
    d.name AS department_name
FROM employees e
FULL JOIN departments d ON e.department_id = d.id;

-- 输出：
--  employee_name | department_name
-- ---------------+----------------
--  Alice         | Engineering
--  Bob           | Engineering
--  Charlie       | Engineering
--  Diana         | Sales
--  Eve           | Sales
--  Frank         | NULL           -- Frank 没有部门
--  NULL          | Marketing      -- Marketing 没有员工

-- FULL OUTER JOIN 是 FULL JOIN 的完整写法

-- CROSS JOIN：笛卡尔积（每行与每行组合）
SELECT 
    e.name AS employee,
    d.name AS department
FROM employees e
CROSS JOIN departments d;

-- 输出：6 员工 × 3 部门 = 18 行
--  employee | department
-- ----------+------------
--  Alice    | Engineering
--  Alice    | Sales
--  Alice    | Marketing
--  Bob      | Engineering
--  ...（共 18 行）

-- 使用场景：生成所有组合
-- 例如：每个员工对每个项目的权限矩阵

-- 自连接：查找员工及其经理
SELECT 
    e.name AS employee,
    e.salary,
    m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- 输出：
--  employee | salary  | manager
-- ----------+---------+---------
--  Alice    | 90000.00| NULL
--  Bob      | 80000.00| Alice
--  Charlie  | 85000.00| Alice
--  Diana    | 70000.00| Alice
--  Eve      | 65000.00| Diana
--  Frank    | 60000.00| Alice

-- 查找薪资高于经理的员工
SELECT 
    e.name AS employee,
    e.salary AS emp_salary,
    m.name AS manager,
    m.salary AS mgr_salary
FROM employees e
INNER JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;

-- 多级层级查询
WITH RECURSIVE employee_hierarchy AS (
    SELECT 
        id,
        name,
        manager_id,
        1 AS level,
        name::TEXT AS path
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    SELECT 
        e.id,
        e.name,
        e.manager_id,
        eh.level + 1,
        eh.path || ' -> ' || e.name
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT 
    REPEAT('  ', level - 1) || name AS hierarchy,
    level
FROM employee_hierarchy
ORDER BY path;

-- NATURAL JOIN：自动使用同名列连接
-- 不推荐使用，因为不可控
SELECT e.name, d.name
FROM employees e
NATURAL JOIN departments d;

-- 等价于（如果两表都有 department_id 列）
SELECT e.name, d.name
FROM employees e
JOIN departments d USING (department_id);

-- ON vs WHERE 的区别
-- ON：连接条件
-- WHERE：过滤结果

-- LEFT JOIN + WHERE（过滤结果）
SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
WHERE d.location = 'Building A';
-- 结果：只返回在 Building A 的员工
-- Frank 不会出现（因为 d.location 是 NULL）

-- LEFT JOIN + ON（连接条件）
SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d 
    ON e.department_id = d.id 
    AND d.location = 'Building A';
-- 结果：返回所有员工
-- Frank 会显示（department 为 NULL）
-- 其他部门的员工也会显示（department 为 NULL）

-- 每个部门的员工数量和平均薪资
SELECT 
    d.name AS department,
    COUNT(e.id) AS employee_count,
    COALESCE(ROUND(AVG(e.salary), 2), 0) AS avg_salary,
    COALESCE(SUM(e.salary), 0) AS total_salary
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
GROUP BY d.id, d.name
ORDER BY employee_count DESC;

-- 输出：
--  department  | employee_count | avg_salary | total_salary
-- -------------+----------------+------------+--------------
--  Engineering |              3 |   85000.00 |    255000.00
--  Sales       |              2 |   67500.00 |    135000.00
--  Marketing   |              0 |       0.00 |         0.00

-- 复杂条件连接
SELECT 
    e.name AS employee,
    d.name AS department,
    p.name AS project
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
LEFT JOIN projects p ON d.id = p.department_id
WHERE e.salary > 70000
ORDER BY e.name, p.name;

-- JOIN 子查询结果
SELECT 
    e.name,
    dept_stats.avg_salary,
    e.salary - dept_stats.avg_salary AS diff
FROM employees e
JOIN (
    SELECT 
        department_id,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) dept_stats ON e.department_id = dept_stats.department_id;

-- 使用 CTE 更清晰
WITH dept_stats AS (
    SELECT 
        department_id,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT 
    e.name,
    dept_stats.avg_salary,
    e.salary - dept_stats.avg_salary AS diff
FROM employees e
JOIN dept_stats ON e.department_id = dept_stats.department_id;

-- 确保连接列有索引
CREATE INDEX idx_employees_department_id ON employees(department_id);
CREATE INDEX idx_projects_department_id ON projects(department_id);

-- 查看执行计划
EXPLAIN ANALYZE
SELECT e.name, d.name
FROM employees e
JOIN departments d ON e.department_id = d.id;

-- 小表驱动大表
-- 如果 employees 表大，departments 表小
-- 让小表在 JOIN 中先处理

-- 使用 INNER JOIN 而非 LEFT JOIN（如果不需要 NULL 值）
-- INNER JOIN 通常更快

-- 错误：忘记连接条件
SELECT e.name, d.name
FROM employees e, departments d;  -- 笛卡尔积！

-- 正确：添加连接条件
SELECT e.name, d.name
FROM employees e
JOIN departments d ON e.department_id = d.id;

进阶阶段
├── 高级查询
├── 多表查询（本文）
├── 索引优化
└── 事务处理