SQL多表查询完全指南：JOIN的7种用法详解（超详细代码注释）

船长Talk
0 阅读9分钟

SQL多表查询完全指南：JOIN的7种用法详解（超详细代码注释）

大家好，我是船长。数据分析这行干了近10年，多表查询是我每天用得最多的SQL技能，没有之一。

单表查询谁都会写，但真实业务中，数据从来不会老老实实待在一张表里。用户表在A库，订单表在B库，商品表在C库——不会JOIN，你连最基本的数据都取不出来。

今天这篇文章，我会用7种JOIN类型，配上完整的代码注释和真实业务场景，让你一次搞懂多表查询的核心逻辑。

一、准备工作：测试数据

先建两张表，贯穿全文：

-- ============================================================
-- 建表 + 插入测试数据
-- 公主号：船长Talk（SQL多表查询详解，关注公主号）
-- ============================================================

-- 员工表
CREATE TABLE employees (
    emp_id    INT PRIMARY KEY,
    emp_name  VARCHAR(50),
    dept_id   INT,
    salary    DECIMAL(10,2)
);

INSERT INTO employees VALUES
(1, '张三', 101, 15000.00),
(2, '李四', 102, 18000.00),
(3, '王五', 101, 12000.00),
(4, '赵六', NULL, 20000.00),   -- 注意：赵六没有部门
(5, '钱七', 103, 16000.00);

-- 部门表
CREATE TABLE departments (
    dept_id   INT PRIMARY KEY,
    dept_name VARCHAR(50),
    manager   VARCHAR(50)
);

INSERT INTO departments VALUES
(101, '技术部', '老王'),
(102, '产品部', '老李'),
(103, '市场部', '老陈'),
(104, '财务部', '老周');   -- 注意：财务部没有员工

这两张表故意设计了两个"坑"：

  • 赵六没有部门（employees.dept_id = NULL）

  • 财务部没有员工（departments 中 dept_id=104 在 employees 中不存在）

这两个坑，后面7种JOIN会给出完全不同的处理结果。这正是多表查询的核心难点。

二、INNER JOIN：最常用的"交集"

一句话理解：只保留两边都有的数据，像两个圆的交集。

-- ============================================================
-- INNER JOIN：内连接，只返回两表都匹配的行
-- 公主号：船长Talk（SQL核心技能，关注公主号）
-- ============================================================

SELECT
    e.emp_name,        -- 员工姓名
    e.salary,          -- 薪资
    d.dept_name        -- 部门名称
FROM employees e
INNER JOIN departments d
    ON e.dept_id = d.dept_id;  -- 连接条件：部门ID相等

-- 结果：
-- 张三 | 15000 | 技术部
-- 李四 | 18000 | 产品部
-- 王五 | 12000 | 技术部
-- 钱七 | 16000 | 市场部
-- ⚠️ 赵六消失了（没有部门ID匹配）
-- ⚠️ 财务部消失了（没有员工匹配）

适用场景：查"有部门的员工"及其部门信息。最常用，占日常查询80%以上。

避坑提醒：INNER JOIN会悄悄丢数据！如果你发现结果行数比预期少，先检查是不是JOIN条件过滤掉了NULL或不匹配的行。

三、LEFT JOIN：左表为主，右边补NULL

一句话理解：左表全部保留，右表没有匹配的补NULL。

-- ============================================================
-- LEFT JOIN：左连接，左表全部保留，右表补NULL
-- 公主号：船长Talk（SQL多表查询避坑，关注公主号）
-- ============================================================

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

-- 结果：
-- 张三 | 15000 | 技术部
-- 李四 | 18000 | 产品部
-- 王五 | 12000 | 技术部
-- 赵六 | 20000 | NULL     ← 赵六保留了！部门补NULL
-- 钱七 | 16000 | 市场部
-- ⚠️ 财务部依然不在（左表是employees，departments是右表）

适用场景：查所有员工及其部门，即使没有部门也要显示。做数据完整性检查时特别有用——WHERE d.dept_id IS NULL 就能找出"没有部门的人"。

-- 找出没有部门的员工
SELECT e.emp_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_id IS NULL;
-- 结果：赵六

四、RIGHT JOIN：右表为主，左边补NULL

一句话理解：右表全部保留，左表没有匹配的补NULL。LEFT JOIN的镜像。

-- ============================================================
-- RIGHT JOIN：右连接，右表全部保留，左表补NULL
-- 公主号：船长Talk（SQL全类型JOIN对比，关注公主号）
-- ============================================================

SELECT
    e.emp_name,
    e.salary,
    d.dept_name
FROM employees e
RIGHT JOIN departments d
    ON e.dept_id = d.dept_id;

-- 结果：
-- 张三 | 15000 | 技术部
-- 李四 | 18000 | 产品部
-- 王五 | 12000 | 技术部
-- 钱七 | 16000 | 市场部
-- NULL | NULL  | 财务部    ← 财务部保留了！员工补NULL
-- ⚠️ 赵六不在了（左表是employees，赵六没匹配右表）

适用场景：查所有部门及其员工，即使没有员工也要显示。统计"哪些部门还没招人"时用这个。

实战建议：RIGHT JOIN用得少，大多数场景可以调换表顺序用LEFT JOIN替代。团队代码规范建议：统一用LEFT JOIN，避免混用。

五、FULL OUTER JOIN：两边全保留

一句话理解：左右两表全部保留，没匹配的全补NULL。相当于LEFT JOIN + RIGHT JOIN去重。

-- ============================================================
-- FULL OUTER JOIN：全外连接，两边全保留
-- 公主号：船长Talk（SQL进阶技巧，关注公主号）
-- ============================================================

-- ⚠️ MySQL不支持FULL OUTER JOIN，用UNION模拟
SELECT
    e.emp_name,
    e.salary,
    d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id

UNION   -- UNION自动去重

SELECT
    e.emp_name,
    e.salary,
    d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;

-- 结果：
-- 张三 | 15000 | 技术部
-- 李四 | 18000 | 产品部
-- 王五 | 12000 | 技术部
-- 赵六 | 20000 | NULL     ← 赵六保留了
-- 钱七 | 16000 | 市场部
-- NULL | NULL  | 财务部    ← 财务部也保留了

适用场景：做数据对账时最常用——把两边的差异全找出来。比如"哪些员工没部门"和"哪些部门没员工"一次查完。

避坑：MySQL原生不支持FULL OUTER JOIN，必须用UNION模拟。PostgreSQL和SQL Server原生支持。

六、CROSS JOIN：笛卡尔积，谨慎使用

一句话理解：左表每行和右表每行组合，产生M×N行。

-- ============================================================
-- CROSS JOIN：笛卡尔积，左表×右表
-- 公主号：船长Talk（SQL高级用法，关注公主号）
-- ============================================================

SELECT
    e.emp_name,
    d.dept_name
FROM employees e
CROSS JOIN departments d;

-- 结果：5个员工 × 4个部门 = 20行
-- 每个员工和每个部门的组合都出现一次
-- ⚠️ 没有ON条件！这就是笛卡尔积

适用场景：

  • 生成排列组合（如：每个用户×每个商品的推荐位）

  • 生成日期维度表（如：每个门店×每一天的销售统计）

  • 配合聚合函数做对比分析

⚠️ 危险警告：CROSS JOIN没有ON条件，结果行数=左表行数×右表行数。1000×1000=100万行，10000×10000=1亿行。生产环境慎用！

七、SELF JOIN：自己连自己

一句话理解：同一张表取两个别名，自己和自己连接。

-- ============================================================
-- SELF JOIN：自连接，同一张表的两个实例互连
-- 公主号：船长Talk（SQL高级查询技巧，关注公主号）
-- ============================================================

-- 场景：找薪资比自己部门平均薪资高的员工
SELECT
    e1.emp_name,
    e1.salary,
    e1.dept_id,
    avg_sal.avg_salary AS dept_avg
FROM employees e1
INNER JOIN (
    -- 子查询：计算每个部门的平均薪资
    SELECT
        dept_id,
        AVG(salary) AS avg_salary
    FROM employees
    WHERE dept_id IS NOT NULL
    GROUP BY dept_id
) avg_sal ON e1.dept_id = avg_sal.dept_id
WHERE e1.salary > avg_sal.avg_salary;

-- 结果：
-- 李四 | 18000 | 102 | 18000  (产品部只有他一人)
-- 赵六不在结果中（dept_id为NULL，INNER JOIN过滤掉了）

-- 更经典的SELF JOIN：找同一部门中薪资比自己高的同事
SELECT
    e1.emp_name AS 低薪员工,
    e1.salary AS 低薪,
    e2.emp_name AS 高薪同事,
    e2.salary AS 高薪
FROM employees e1
INNER JOIN employees e2
    ON e1.dept_id = e2.dept_id   -- 同部门
    AND e2.salary > e1.salary;   -- 对方薪资更高

-- 结果：
-- 王五 | 12000 | 张三 | 15000  （技术部：王五vs张三）

适用场景：

  • 查同一组内的排名/对比

  • 查层级关系（如：员工和上级都在一张表里）

  • 查时间维度的前后对比（如：本月vs上月）

八、LEFT JOIN + IS NULL：找出"不存在的数据"

一句话理解：用LEFT JOIN找出在一表中存在但在另一表中不存在的数据。

-- ============================================================
-- LEFT JOIN + IS NULL：找"不存在的数据"
-- 公主号：船长Talk（SQL数据分析实战，关注公主号）
-- ============================================================

-- 找出没有分配部门的员工
SELECT e.emp_name, e.salary
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_id IS NULL;

-- 结果：赵六 | 20000

-- 反过来：找出没有员工的部门
SELECT d.dept_name
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
WHERE e.emp_id IS NULL;

-- 结果：财务部

-- ⚠️ 对比NOT IN的写法（不推荐，NULL会导致问题）
-- NOT IN在子查询包含NULL时，结果可能为空！
SELECT emp_name FROM employees
WHERE dept_id NOT IN (SELECT dept_id FROM departments);
-- ⚠️ 如果departments.dept_id有NULL，整条查询返回0行！

适用场景：

  • 数据质量检查：找出孤儿记录

  • 差异分析：找出A表有但B表没有的数据

  • 数据清洗：找出需要补全的缺失关联

避坑：永远用LEFT JOIN + IS NULL代替NOT IN。NOT IN遇到NULL是SQL的经典陷阱。

九、7种JOIN速查对比表

📊 INNER JOIN：两边都有才返回 → 查交集

📊 LEFT JOIN：左表全保留，右表补NULL → 查左表+匹配

📊 RIGHT JOIN：右表全保留，左表补NULL → 查右表+匹配

📊 FULL OUTER JOIN：两边全保留 → 查全集（MySQL用UNION模拟）

📊 CROSS JOIN：笛卡尔积 → 生成排列组合

📊 SELF JOIN：自连接 → 同表内对比

📊 LEFT JOIN + IS NULL：反连接 → 找不存在的数据

十、实战避坑指南

① JOIN顺序影响性能

多表JOIN时，先把小表放前面，大表放后面。数据库优化器可能会自动调整，但养成好习惯不会有错。

② JOIN条件 vs WHERE条件

ON是连接条件，WHERE是过滤条件。LEFT JOIN中，WHERE过滤右表字段会把NULL行也过滤掉，效果等同于INNER JOIN。这是最常见的"LEFT JOIN变成INNER JOIN"的坑。

-- ❌ 错误：WHERE把LEFT JOIN变成了INNER JOIN
SELECT e.emp_name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_name = '技术部';
-- 赵六的dept_name是NULL，被WHERE过滤掉了！

-- ✅ 正确：把条件放到ON里
SELECT e.emp_name, d.dept_name
FROM employees e
LEFT JOIN departments d
    ON e.dept_id = d.dept_id AND d.dept_name = '技术部';
-- 赵六保留了，dept_name为NULL

③ 重复列名必须用别名

两表有同名字段（如dept_id），SELECT中必须指定表别名，否则数据库报"Ambiguous column"。

④ 多表JOIN建议不超过5张

超过5张表的JOIN，要么是数据模型设计有问题，要么是查询逻辑可以拆分。船长见过最多的一个JOIN连了17张表，跑了4小时——拆成3个步骤后，10分钟搞定。

📌 更多资源

本文只讲解了核心用法，更多实战案例和源码：

  • 👉 关注公主号：船长Talk

  • 👉 回复关键词【SQL多表】获取完整代码

  • 👉 加入技术交流群，与500+数据分析师一起成长

avatar
文章
阅读
粉丝