📊 SQL 入门 Day 6：多表查询 — JOIN 的四种姿势

前端阿凡
0 阅读3分钟

📌 一句话总结

JOIN 是 SQL 跨表查数据的核心机制，通过关联条件把多张表的行拼成结果集。掌握 INNER/LEFT/RIGHT/FULL JOIN 四种类型，覆盖 95% 的多表查询需求。

1. 四类 JOIN 速览

-- 假设有两张表：
-- employees: id, name, dept_id
-- departments: id, dept_name

-- INNER JOIN：两表都有才返回（交集）
SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

-- LEFT JOIN：左表全保留，右表没有就 NULL
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

-- RIGHT JOIN：右表全保留，左表没有就 NULL（很少用，LEFT 反过来就好）
SELECT e.name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;

-- FULL JOIN：两表都保留，没有就 NULL（PG/MySQL 8+ 支持，SQLite 不支持）
SELECT e.name, d.dept_name
FROM employees e
FULL JOIN departments d ON e.dept_id = d.id;
JOIN 类型左表有右表有结果
INNER✅✅✓ 返回
INNER❌✅✗
INNER✅❌✗
LEFT✅❌✓ 右表为 NULL
LEFT❌✅✗
RIGHT❌✅✓ 左表为 NULL
FULL✅❌✓ 右表为 NULL
FULL❌✅✓ 左表为 NULL

2. INNER JOIN — 最常用

-- 查员工及其所属部门名
SELECT
    e.employee_id,
    e.name,
    d.department_name,
    d.location
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

-- 三表 JOIN：员工 → 部门 → 项目
SELECT
    e.name,
    d.department_name,
    p.project_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN projects p ON e.employee_id = p.lead_id;

ON vs USING

如果关联的列名相同，可以用 USING 简化：

-- 列名相同（都是 department_id）
SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d USING (department_id);

-- 等价写法
SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

⚠️ USING 会把关联列合并为一个，ON 则保留两个列。

3. LEFT JOIN — 查"有/无"

-- 查所有员工及其部门（没有部门的员工也显示）
SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

-- 技巧：找"没有部门"的员工（左表有，右表没有）
SELECT e.name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
WHERE d.department_id IS NULL;

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

LEFT JOIN ... IS NULL 是非常经典的"找缺失"模式，等价于 NOT EXISTS。

4. RIGHT JOIN — 对称写法

-- RIGHT JOIN 只是 LEFT 的反向，通常直接用 LEFT 就行
-- 下面两个等价：
SELECT e.name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;

-- 等价 LEFT 写法（推荐）
SELECT e.name, d.department_name
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id;

💡 习惯： 只用 LEFT JOIN 就够了，把主表放左边，副表放右边。RIGHT JOIN 除了在特定自动生成的 SQL 中外很少主动使用。

5. FULL JOIN — 全保留

-- PostgreSQL / MySQL 8+
SELECT
    COALESCE(e.name, '无员工') AS employee,
    COALESCE(d.department_name, '无部门') AS department
FROM employees e
FULL JOIN departments d ON e.department_id = d.department_id;

-- SQLite 模拟 FULL JOIN（不支持）
SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
UNION
SELECT e.name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;

6. 自连接 — 同一张表的 JOIN

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

-- 查同部门的同事对
SELECT
    a.name AS emp1,
    b.name AS emp2,
    a.department_id
FROM employees a
INNER JOIN employees b
    ON a.department_id = b.department_id
   AND a.employee_id < b.employee_id;  -- 去重：只保留 (A,B) 不保留 (B,A)

自连接的核心要点：必须用别名，否则无法区分。

7. 多表 JOIN 的顺序与性能

-- 4 表 JOIN
SELECT
    o.order_id,
    c.customer_name,
    e.name AS sales_rep,
    p.product_name,
    oi.quantity
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN employees e ON o.sales_rep_id = e.employee_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id;

性能要点

原则说明
小表驱动大表JOIN 的顺序影响性能（优化器会重排，但建议手动优先小表）
关联列建索引JOIN 的 ON 条件列必须有索引
避免 JOIN 太多表超过 5 个 JOIN 可读性和性能都会变差，考虑反范式或视图
先过滤再 JOIN用子查询或 CTE 先缩小结果再 JOIN
-- 先过滤再 JOIN 的优化写法
SELECT e.name, d.department_name
FROM employees e
INNER JOIN (
    SELECT department_id, department_name
    FROM departments
    WHERE status = 'active'
) d ON e.department_id = d.department_id
WHERE e.status = '在职';

8. 实战：多表查询完整场景

-- 场景：生成 2024 年销售报表
SELECT
    c.customer_name,
    COUNT(o.order_id) AS order_count,
    SUM(o.total_amount) AS total_spent,
    MAX(o.order_date) AS last_order
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2024-01-01'
  AND o.status = '已完成'
GROUP BY c.customer_id, c.customer_name
HAVING COUNT(o.order_id) >= 3
ORDER BY total_spent DESC
LIMIT 10;

📝 要点总结

JOIN 类型场景记忆口诀
INNER JOIN两表都有才要交集
LEFT JOIN主表全保留左表为王
RIGHT JOIN副表全保留尽量换 LEFT
FULL JOIN两表都保留全集
自连接同一张表内关联必须用别名
ON关联条件列名不同时用
USING关联列同名列名相同时用
LEFT JOIN + IS NULL找缺失数据经典"不存在"查询
avatar
文章
阅读
粉丝