子查询三种类型
1.1 七种 JOIN 速览（前必过一遍）
-- 数据准备
-- employees: id=1(张三),2(李四),3(王五) departments: id=1(技术部),2(产品部),4(人事部)
-- ① INNER JOIN — 只返回两表都匹配的行
SELECT e.name, d.dept_name -- 要显示的列
FROM employees e -- 左表（驱动表）
INNER JOIN departments d ON e.dept_id = d.id; -- 只保留两边都匹配的行
-- 结果：张三-技术部, 李四-产品部 （王五 dept_id=3 没匹配 → 不出现；人事部没人 → 不出现）
-- ② LEFT JOIN — 左表全保留，右表没匹配的填 NULL
SELECT e.name, d.dept_name -- 要显示的列
FROM employees e -- 左表（全部保留）
LEFT JOIN departments d ON e.dept_id = d.id; -- 右表匹配不上就填 NULL
-- 结果：张三-技术部, 李四-产品部, 王五-NULL （王五保留了）
-- ③ RIGHT JOIN — 右表全保留（生产中尽量用 LEFT JOIN 替代，可读性更好）
SELECT e.name, d.dept_name -- 要显示的列
FROM employees e -- 左表（匹配不上就填 NULL）
RIGHT JOIN departments d ON e.dept_id = d.id; -- 右表全部保留
-- 结果：张三-技术部, 李四-产品部, NULL-人事部
-- ④ FULL OUTER JOIN — 两表全部保留（MySQL 不支持，用 LEFT JOIN UNION RIGHT JOIN 模拟）
SELECT e.name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id = d.id
UNION -- UNION 自动去重，合并两个查询结果
SELECT e.name, d.dept_name FROM employees e RIGHT JOIN departments d ON e.dept_id = d.id;
-- ⑤ CROSS JOIN — 笛卡尔积（慎用！m 行 × n 行 = m*n 行）
SELECT e.name, d.dept_name FROM employees e CROSS JOIN departments d;
-- 3 × 3 = 9 行，每一行 employee 和每一行 department 都组合一次
-- ⑥ LEFT JOIN + IS NULL — 找"左表有但右表没有"的行（没下过单的客户）
SELECT e.name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id -- 先 LEFT JOIN，没匹配的 d.id 为 NULL
WHERE d.id IS NULL; -- 再过滤：只要右表为 NULL 的行 = 左表独有
-- 结果：王五（dept_id=3 在 departments 里不存在）
-- ⑦ RIGHT JOIN + IS NULL — 找"右表有但左表没有"的行
-- 同理反过来：WHERE e.id IS NULL → 人事部（没有员工被分配到该部门）
graph TD subgraph A["A"] A1["1"] A2["2"] end subgraph B["B"] B1["1"] B3["3"] end A1 --- B1 style A1 fill:#4a90d9,stroke:#333 style B1 fill:#4a90d9,stroke:#333 style A2 fill:#e67e22,stroke:#333 style B3 fill:#27ae60,stroke:#333 INNER["← INNER JOIN = 交集（1）"] LEFT["← LEFT JOIN = A 全集（1,2）"] RIGHT["← RIGHT JOIN = B 全集（1,3）"] LEFTNULL["← LEFT JOIN + IS NULL = A 独有（2）"] RIGHTNULL["← RIGHT JOIN + IS NULL = B 独有（3）"] FULL["← FULL OUTER JOIN = 并集（1,2,3）"]
1.2 🔴 LEFT JOIN + WHERE 退化陷阱（必考题）
这是 SQL 最常见的陷阱——90% 的候选人会踩。
-- 场景：查询所有客户及其订单（包括没下过单的客户）
-- customers: 张三(1), 李四(2), 王五(3)
-- orders: order_1(user_id=1), order_2(user_id=1)
-- ❌ 错误写法 — LEFT JOIN 偷偷退化成 INNER JOIN
SELECT c.name, o.order_id -- 想要所有客户+订单
FROM customers c -- 左表：客户表
LEFT JOIN orders o ON c.user_id = o.user_id -- 左连接：保留没订单的客户
WHERE o.amount > 100; -- ⚠️ WHERE 在后：把 amount=NULL 的行全滤掉了！
-- 结果：只有张三（因为王五和李四的订单 amount 是 NULL，NULL > 100 = UNKNOWN → 被过滤）
-- 王五（没下过单）被漏掉了！LEFT JOIN 白写了！
-- ✅ 正确写法1 — 过滤条件写进 ON 子句
SELECT c.name, o.order_id -- 所有客户+符合条件的订单
FROM customers c -- 左表
LEFT JOIN orders o ON c.user_id = o.user_id -- ① 连接条件
AND o.amount > 100; -- ② 过滤条件也写 ON 里——在 JOIN 时就判断
-- ON 里的条件在 JOIN 时生效——右表不满足的不匹配，但左表行保留
-- ✅ 正确写法2 — 用子查询先过滤右表
SELECT c.name, o.order_id
FROM customers c
LEFT JOIN (SELECT * FROM orders WHERE amount > 100) o -- 先过滤 orders，再 LEFT JOIN
ON c.user_id = o.user_id; -- 子查询里已过滤，外面只需连接
graph TD subgraph Wrong["LEFT JOIN + WHERE（❌ 退化为 INNER JOIN）"] W1["① LEFT JOIN → 结果含王五（orders 字段为 NULL）"] W2["② WHERE amount>100 → NULL>100 = UNKNOWN → 王五被过滤掉！"] W1 --> W2 end subgraph Correct["LEFT JOIN + ON（✅ 正确）"] C1["① ON 条件在 JOIN 时判断"] C2["王五的 orders 行不满足 amount>100，不匹配<br/>但保留王五，orders 字段为 NULL"] C1 --> C2 end
一句话讲清：“WHERE 过滤的是 JOIN 之后的结果集，ON 过滤的是 JOIN 过程中的匹配条件。对 LEFT JOIN 的右表做 WHERE 过滤，会把该表为 NULL 的行也过滤掉——LEFT JOIN 退化为 INNER JOIN。“
1.3 驱动表选择 — 小表驱动大表
-- 问："多表 JOIN 怎么优化？"
-- 核心原则：小表驱动大表
-- MySQL 的 Nested-Loop Join：外层循环（驱动表）每行 → 去内层循环（被驱动表）找匹配
-- 驱动表行数越少 → 内层循环总次数越少
-- 用小表 customers (3行) 驱动大表 orders (10万行)
SELECT c.name, o.order_id
FROM customers c -- ← 驱动表（3行，小表）
JOIN orders o ON c.user_id = o.user_id; -- ← 被驱动表，JOIN 字段必须有索引
-- 循环 3 次，每次在 orders.user_id 索引上快速查找 → 快
-- ❌ 大表驱动小表
SELECT c.name, o.order_id
FROM orders o -- ← 驱动表（10万行，大表）
JOIN customers c ON o.user_id = c.user_id; -- 循环 10 万次 → 慢（虽然优化器通常会自己选对）
| 场景 | 驱动表选谁 | 原因 |
|---|---|---|
| JOIN 字段都有索引 | 行数少的表 | 减少外层循环次数 |
| 小表无索引，大表有索引 | 小表 | 小表全表扫描代价小，大表走索引快 |
| 两表都没索引 | 任意（都慢） | 先建索引再 JOIN |
一句话讲清：“能用
EXPLAIN看rows列——rows 最小的表通常是驱动表。如果优化器选错了，可以用STRAIGHT_JOIN强制指定。“
1.4 多表 JOIN 实战 — 四表联查
-- 场景：查询"张三老师教过的所有课程中，每个学生的成绩"
-- 涉及 4 张表：teacher → course → sc → student
SELECT
st.sname AS 学生姓名, -- 从 student 表取学生名
c.cname AS 课程名称, -- 从 course 表取课程名
sc.score AS 成绩, -- 从 sc（成绩表）取分数
t.tname AS 教师 -- 从 teacher 表取教师名
FROM teacher t -- ① 先从 teacher 入手（后面 WHERE 要过滤教师名）
JOIN course c ON t.tid = c.tid -- ② 老师 → 课程（通过 tid 关联）
JOIN sc ON c.cid = sc.cid -- ③ 课程 → 成绩（通过 cid 关联）
JOIN student st ON sc.sid = st.sid -- ④ 成绩 → 学生（通过 sid 关联）
WHERE t.tname = '张三' -- ⑤ 只查张三老师
ORDER BY sc.score DESC; -- ⑥ 按分数降序排列
-- 执行顺序分析（EXPLAIN 看到的）：
-- teacher（先过滤 tname='张三'，1 行）→ course（JOIN 走索引，2 行）
-- → sc（JOIN 走索引，50 行）→ student（JOIN 走索引，50 行）
速记卡（面试闪卡）
Q1：一句话讲清「子查询三种类型」到底是什么？
A：子查询与多表连接，本质都是把一次查询的结果喂给下一次；最易踩的坑是左连接右表的过滤写错，会让左连接悄悄退化成内连接。
Q2：七种 JOIN 与多表连接 —— 怎么理解？
A：把两表连接想成”拼图”——INNER 只留两边都对得上的交集，LEFT 保住左表全貌、右表对不上就填空。就像相亲：只介绍互有好感的（内连接），或先把所有男生列出来、有缘再配对（左连接）。
Q3：LEFT JOIN + WHERE 退化陷阱 —— 怎么理解？
A：左连接后若在 WHERE 里过滤右表，NULL 行会被当成”不成立”整行删掉，左连接瞬间变内连接。好比先请所有人入座（左连接），再按”有伴侣”筛人（WHERE），单身的人全被请走——白请了。
Q4：驱动表选择——小表驱动大表 —— 怎么理解？
A：多表 JOIN 像套娃循环：外层每走一行，内层就要全跑一遍。所以让小表当外层（驱动表），大表走索引，循环次数最少。好比请客先叫人少的一桌进场，再逐个去大桌找座位，最省腿。
Q5：子查询实战——四表联查 —— 怎么理解？
A：子查询就是把一个 SELECT 塞进另一个 SELECT 里当临时表或过滤条件，比如”先过滤订单再左连接”避免退化。像做菜先备好料（子查询），再下锅翻炒（主查询），料备错了整道菜就废。
Q6：核心速记主线有哪些？
-
七种 JOIN：INNER/LEFT/RIGHT/FULL/CROSS + 两种 NULL 找差集
-
LEFT JOIN 右表过滤要写进 ON，别写 WHERE（否则退化）
-
驱动表选小表，JOIN 字段必须有索引
-
子查询可替代 JOIN 解决退化，先过滤再连接
口诀
A：子查询里套查询，
左连退化要警惕；
过滤写进 ON 子句，
小表驱动最省力。