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

SQL多表查询完全指南JOIN的7种用法详解附完整代码注释数据分析师写SQL最容易卡住的地方不是聚合不是窗口函数——是JOIN。不是不会写是写了出错不知道为什么。是知道有7种JOIN但记不清哪种该用哪种。这篇文章把JOIN的7种用法全部跑通每种配一个真实场景和完整代码注释。一、为什么要用JOIN先说一个真实场景。你有两张表orders订单表order_id, user_id, amount, created_atusers用户表user_id, user_name, city, register_date业务问了你一个问题每个城市的用户总共下了多少单、消费了多少钱订单里没有城市用户表里没有订单数据。你需要把两张表拼起来——这就是JOIN存在的原因。SQL中JOIN是把多张表通过共同字段通常是ID连接在一起的操作。理解了这个核心7种JOIN不过是拼的方式不同而已。二、建表与测试数据先建两张表后面所有示例都用这组数据。-- 建用户表 CREATE TABLE users ( user_id INT PRIMARY KEY, user_name VARCHAR(50), city VARCHAR(50), register_date DATE ); -- 插入测试数据包含一些特殊情况 INSERT INTO users VALUES (1, 张三, 北京, 2024-01-15), (2, 李四, 上海, 2024-02-20), (3, 王五, 广州, 2024-03-10), (4, 赵六, 深圳, 2024-04-05), (5, 孙七, 成都, 2024-05-18); -- 注意user_id5的孙七没有对应订单 -- 建订单表 CREATE TABLE orders ( order_id INT PRIMARY KEY, user_id INT, amount DECIMAL(10,2), created_at DATE ); INSERT INTO orders VALUES (1001, 1, 299.00, 2024-06-01), (1002, 1, 158.50, 2024-06-15), (1003, 2, 420.00, 2024-06-20), (1004, 3, 88.00, 2024-07-01), (1005, 6, 350.00, 2024-07-10); -- 注意user_id6的订单在users表里找不到对应用户脏数据这两张表故意埋了两个坑user_id5的用户孙七没有订单user_id6的订单没有对应用户接下来7种JOIN结果会因为这两个坑而产生不同——这正是理解JOIN的关键。三、7种JOIN详解1. INNER JOIN内连接——最常用取交集核心逻辑只返回两表中都能匹配上的行。-- INNER JOIN查询有订单记录的用户信息 SELECT u.user_id, u.user_name, u.city, o.order_id, o.amount FROM users AS u INNER JOIN orders AS o ON u.user_id o.user_id ORDER BY u.user_id; /* 执行结果 user_id | user_name | city | order_id | amount 1 | 张三 | 北京 | 1001 | 299.00 1 | 张三 | 北京 | 1002 | 158.50 2 | 李四 | 上海 | 1003 | 420.00 3 | 王五 | 广州 | 1004 | 88.00 注意 - user_id4赵六没有订单不出现 - user_id5孙七没有订单不出现 - user_id6的订单没有对应用户不出现 只返回两表都有匹配的4行数据 */使用场景只关心两边都有的数据时用INNER JOIN。比如分析下过单的用户行为。2. LEFT JOIN左连接——以左表为主右表补充核心逻辑返回左表所有行右表能匹配上就填数据匹配不上填NULL。-- LEFT JOIN查询所有用户有订单的展示订单没有的也展示 SELECT u.user_id, u.user_name, u.city, o.order_id, o.amount FROM users AS u LEFT JOIN orders AS o ON u.user_id o.user_id ORDER BY u.user_id; /* 执行结果 user_id | user_name | city | order_id | amount 1 | 张三 | 北京 | 1001 | 299.00 1 | 张三 | 北京 | 1002 | 158.50 2 | 李四 | 上海 | 1003 | 420.00 3 | 王五 | 广州 | 1004 | 88.00 4 | 赵六 | 深圳 | NULL | NULL ← 没有订单右边填NULL 5 | 孙七 | 成都 | NULL | NULL ← 没有订单右边填NULL 注意 - 左表users的5行全部出现 - 右表匹配不上的NULL填充 - user_id6的订单右表有但左表没有不出现 */使用场景想保留左表所有数据时用LEFT JOIN。比如查所有用户的下单情况包括从未下单的。变体技巧找左表独有的行-- 找从未下过单的用户LEFT JOIN WHERE IS NULL SELECT u.user_id, u.user_name, u.city FROM users AS u LEFT JOIN orders AS o ON u.user_id o.user_id WHERE o.order_id IS NULL; /* 结果 user_id | user_name | city 4 | 赵六 | 深圳 5 | 孙七 | 成都 */3. RIGHT JOIN右连接——以右表为主左表补充核心逻辑与LEFT JOIN镜像返回右表所有行。实际工作中很少用通常可以改写成LEFT JOIN调换表顺序。-- RIGHT JOIN查询所有订单关联用户信息即使用户信息不存在 SELECT o.order_id, o.user_id, o.amount, u.user_name, u.city FROM users AS u RIGHT JOIN orders AS o ON u.user_id o.user_id ORDER BY o.order_id; /* 执行结果 order_id | user_id | amount | user_name | city 1001 | 1 | 299.00 | 张三 | 北京 1002 | 1 | 158.50 | 张三 | 北京 1003 | 2 | 420.00 | 李四 | 上海 1004 | 3 | 88.00 | 王五 | 广州 1005 | 6 | 350.00 | NULL | NULL ← 用户不存在左边填NULL 注意订单表5行全部出现user_id6的订单没有用户信息NULL填充 */ -- 等价写法RIGHT JOIN 改写为 LEFT JOIN结果相同 SELECT o.order_id, o.user_id, o.amount, u.user_name, u.city FROM orders AS o LEFT JOIN users AS u ON o.user_id u.user_id ORDER BY o.order_id;使用场景查所有订单时关联用户信息脏数据无对应用户的订单也要展示。日常更推荐改写成LEFT JOIN可读性更好。4. FULL OUTER JOIN全外连接——两表合并NULL填充缺失部分核心逻辑返回左表和右表的所有行能匹配的配上不能匹配的两边都填NULL。注意MySQL不直接支持FULL OUTER JOIN需要用UNION模拟。-- FULL OUTER JOINMySQL用UNION模拟 -- 场景数据质量检查找出没有订单的用户和没有用户信息的订单 SELECT u.user_id AS user_id, u.user_name, o.order_id, o.amount, CASE WHEN o.order_id IS NULL THEN 用户无订单 WHEN u.user_id IS NULL THEN 订单无对应用户脏数据 ELSE 正常 END AS data_status FROM users AS u LEFT JOIN orders AS o ON u.user_id o.user_id UNION ALL SELECT u.user_id, u.user_name, o.order_id, o.amount, CASE WHEN o.order_id IS NULL THEN 用户无订单 WHEN u.user_id IS NULL THEN 订单无对应用户脏数据 ELSE 正常 END AS data_status FROM users AS u RIGHT JOIN orders AS o ON u.user_id o.user_id WHERE u.user_id IS NULL -- 只取RIGHT JOIN独有的部分避免重复 ORDER BY data_status, user_id; /* 执行结果 user_id | user_name | order_id | amount | data_status 1 | 张三 | 1001 | 299.00 | 正常 1 | 张三 | 1002 | 158.50 | 正常 2 | 李四 | 1003 | 420.00 | 正常 3 | 王五 | 1004 | 88.00 | 正常 4 | 赵六 | NULL | NULL | 用户无订单 5 | 孙七 | NULL | NULL | 用户无订单 NULL | NULL | 1005 | 350.00 | 订单无对应用户脏数据 */使用场景数据对账、脏数据排查。两张表互相补充不遗漏任何一边。5. CROSS JOIN交叉连接——笛卡尔积全部两两组合核心逻辑左表每行与右表每行两两组合结果行数 左表行数 × 右表行数。-- CROSS JOIN生成所有用户×所有月份的组合日期填充场景 -- 先建一个月份表 WITH months AS ( SELECT 2024-06 AS month UNION ALL SELECT 2024-07 UNION ALL SELECT 2024-08 ) SELECT u.user_id, u.user_name, m.month FROM users AS u CROSS JOIN months AS m WHERE u.user_id IN (1, 2, 3) -- 只取前3个用户做演示 ORDER BY u.user_id, m.month; /* 执行结果3用户 × 3月份 9行 user_id | user_name | month 1 | 张三 | 2024-06 1 | 张三 | 2024-07 1 | 张三 | 2024-08 2 | 李四 | 2024-06 2 | 李四 | 2024-07 2 | 李四 | 2024-08 3 | 王五 | 2024-06 3 | 王五 | 2024-07 3 | 王五 | 2024-08 */最常用场景生成所有用户×所有日期的全量组合再LEFT JOIN实际数据实现0值填充没有数据的位置填0而不是不显示。-- 经典用法每月用户消费统计没有消费的填0 WITH months AS ( SELECT 2024-06 AS month UNION ALL SELECT 2024-07 UNION ALL SELECT 2024-08 ), user_month AS ( SELECT u.user_id, u.user_name, m.month FROM users u CROSS JOIN months m WHERE u.user_id IN (1, 2, 3) ) SELECT um.user_id, um.user_name, um.month, COALESCE(SUM(o.amount), 0) AS total_amount -- NULL替换为0 FROM user_month um LEFT JOIN orders o ON um.user_id o.user_id AND DATE_FORMAT(o.created_at, %Y-%m) um.month GROUP BY um.user_id, um.user_name, um.month ORDER BY um.user_id, um.month;6. SELF JOIN自连接——同一张表连接自身核心逻辑把同一张表当作两张不同的表用别名区分实现行与行之间的比较。-- 场景找出注册日期晚于张三但消费金额更高的用户 -- 思路把users表自连接一个别名代表基准用户另一个代表待比较用户 SELECT u2.user_name AS compared_user, u2.city, u2.register_date, SUM(o2.amount) AS total_spent FROM users AS u1 -- u1基准用户张三 JOIN users AS u2 -- u2待比较用户 ON u2.register_date u1.register_date -- 条件1u2注册晚于u1 JOIN orders AS o1 ON o1.user_id u1.user_id -- u1的订单 JOIN orders AS o2 ON o2.user_id u2.user_id -- u2的订单 WHERE u1.user_name 张三 GROUP BY u2.user_id, u2.user_name, u2.city, u2.register_date HAVING SUM(o2.amount) SUM(o1.amount) -- 条件2u2消费 u1消费 ORDER BY total_spent DESC;更常用的SELF JOIN场景组织架构树员工-上级关系-- 假设有员工表 employees(emp_id, emp_name, manager_id) -- 查询每个员工及其直属上级 SELECT e.emp_name AS employee, m.emp_name AS manager FROM employees AS e LEFT JOIN employees AS m -- 同一张表m代表上级 ON e.manager_id m.emp_id ORDER BY e.emp_id;使用场景层级数据组织架构、分类目录、行与行比较找异常值、排名比较。7. ANTI JOIN反连接——找不在另一表中的数据SQL没有ANTI JOIN关键字用LEFT JOIN WHERE IS NULL或NOT IN / NOT EXISTS实现。-- 方法1LEFT JOIN WHERE IS NULL推荐性能好 -- 场景找出从未消费的用户 SELECT u.user_id, u.user_name, u.city FROM users AS u LEFT JOIN orders AS o ON u.user_id o.user_id WHERE o.order_id IS NULL; /* 结果 user_id | user_name | city 4 | 赵六 | 深圳 5 | 孙七 | 成都 */ -- 方法2NOT EXISTS语义更清晰 SELECT u.user_id, u.user_name, u.city FROM users AS u WHERE NOT EXISTS ( SELECT 1 FROM orders AS o WHERE o.user_id u.user_id ); -- 方法3NOT IN注意右表有NULL时会导致全表返回空慎用 SELECT user_id, user_name, city FROM users WHERE user_id NOT IN ( SELECT DISTINCT user_id FROM orders WHERE user_id IS NOT NULL );使用场景找缺席数据是数据分析中非常高频的需求。四、JOIN性能注意事项JOIN写对了还要写快。3条实用原则① 连接字段必须加索引-- JOIN的ON条件字段两边都应该有索引 CREATE INDEX idx_orders_user_id ON orders(user_id); CREATE INDEX idx_users_user_id ON users(user_id); -- 主键默认有索引 -- 用EXPLAIN查看执行计划确认是否走了索引 EXPLAIN SELECT u.user_name, o.amount FROM users u JOIN orders o ON u.user_id o.user_id; -- 观察 key 列是否显示索引名type 列是否是 ref 而不是 ALL② 先过滤再JOIN-- ❌ 不好先JOIN全表再过滤 SELECT u.user_name, o.amount FROM users u JOIN orders o ON u.user_id o.user_id WHERE u.city 北京 AND o.amount 200; -- ✅ 更好先用子查询/CTE过滤再JOIN减少JOIN的数据量 WITH beijing_users AS ( SELECT user_id, user_name FROM users WHERE city 北京 ), big_orders AS ( SELECT user_id, amount FROM orders WHERE amount 200 ) SELECT b.user_name, bo.amount FROM beijing_users b JOIN big_orders bo ON b.user_id bo.user_id;③ 避免在ON条件里做函数计算-- ❌ 不好函数会导致索引失效 JOIN orders o ON DATE_FORMAT(o.created_at, %Y) 2024 -- ✅ 更好用范围条件 JOIN orders o ON o.created_at 2024-01-01 AND o.created_at 2025-01-01五、7种JOIN一张图总结用表格对比快速记忆JOIN类型 返回数据范围 典型使用场景 ───────────────────────────────────────────────────────────────── INNER JOIN 两表都有匹配的行 主数据分析最常用 LEFT JOIN 左表全部 右表匹配部分 左表为主右表补充 RIGHT JOIN 右表全部 左表匹配部分 右表为主可改写LEFT FULL OUTER JOIN 两表全部MySQL用UNION模拟 数据对账、脏数据检查 CROSS JOIN 两表行的全部组合笛卡尔积 日期/维度填充0值补齐 SELF JOIN 同表自连接行与行比较 层级数据、排名对比 ANTI JOIN 存在于左表但不在右表 找缺席数据、差异比对六、练习题附答案用本文的 users orders 两张表完成以下查询题目1统计每个城市的总消费金额只统计有订单的城市按消费金额降序排列。-- 答案 SELECT u.city, COUNT(DISTINCT o.order_id) AS order_count, SUM(o.amount) AS total_amount FROM users AS u INNER JOIN orders AS o ON u.user_id o.user_id GROUP BY u.city ORDER BY total_amount DESC;题目2找出所有城市的消费情况包括没有产生任何订单的城市金额显示0。-- 答案 SELECT u.city, COUNT(o.order_id) AS order_count, COALESCE(SUM(o.amount), 0) AS total_amount FROM users AS u LEFT JOIN orders AS o ON u.user_id o.user_id GROUP BY u.city ORDER BY total_amount DESC;题目3找出订单表中存在、但在用户表中找不到对应用户的孤儿订单。-- 答案三种写法均可 -- 写法1LEFT JOIN反向 SELECT o.* FROM orders o LEFT JOIN users u ON o.user_id u.user_id WHERE u.user_id IS NULL; -- 写法2NOT EXISTS SELECT * FROM orders o WHERE NOT EXISTS (SELECT 1 FROM users u WHERE u.user_id o.user_id); -- 写法3NOT IN SELECT * FROM orders WHERE user_id NOT IN (SELECT user_id FROM users);总结JOIN的核心不是记住7种名称而是理解一个问题我想保留哪张表的全部数据两边都要 → INNER JOIN左边全要 → LEFT JOIN两边都全要 → FULL OUTER JOINMySQL用UNION想找差集 → LEFT JOIN WHERE IS NULL把这篇收藏写JOIN遇到困惑时翻出来查——比死记硬背7种名字有用多了。有问题评论区见船长会回复。