从 “部门撸串” 到 “课程选课”
做数据分析时，单表查询顶多算 “入门新手村”，多表查询才是 “进阶副本”。平时刷抖音总刷到 “搭子社交”“拆分任务提效率”，其实 SQL 多表关系和查询逻辑，跟这些生活场景简直神同步。
先搞懂：多表关系就像 “抖音搭子”，分三种类型
多表查询的核心是先理清表之间的关系，就像找搭子要先明确 “是固定搭子还是灵活搭子”。常见的三种关系，用日常案例一对应就懂。
1. 一对多（多对一）：部门和员工，像 “老板和打工人”
- 关系类比：一个部门（比如 “技术部”）能有 N 个员工，但一个员工（比如小李）只能属于一个部门。就像一个抖音账号能发多条视频，一条视频只归一个账号。
- 实现关键：在 “多” 的一方（员工表employee）加外键，指向 “一” 的一方（部门表dept）的主键。
比如员工表加dept字段，关联部门表的id（主键）。
2. 多对多：学生和课程，像 “用户和关注的博主”
- 关系类比：一个学生能选多门课（比如数学 + 英语），一门课也能被多个学生选。就像一个用户能关注多个博主，一个博主也能被多个用户关注。
- 实现关键：必须加 “中间表”，不然两张表直接关联会乱套。
比如建student_course表，至少包含两个外键：student_id（关联学生表主键）和course_id（关联课程表主键），用这两个字段共同确定 “哪个学生选了哪门课”。
3. 一对一：用户和用户详情，像 “人跟身份证”
- 关系类比：一个用户只能有一份详情（比如手机号、地址），一份详情也只对应一个用户。主要用来拆分表 —— 把 “常用字段”（如用户名、密码）放主表，“不常用字段”（如生日、籍贯）放详情表，查数据更快（不用加载冗余字段）。
- 实现关键：在任意一方加外键，且外键必须设为UNIQUE（保证一个值只对应一条数据）。
比如在user_detail表加user_id字段，关联user表的id，同时给user_id加UNIQUE约束。
再学查：多表查询的 “5 种常用姿势”，附代码
理清关系后，查询就像 “按需求组队”。不同场景用不同查询方式，避免查出来的结果 “又多又乱”（比如笛卡尔积）。
1. 先避坑：笛卡尔积是 “无效组队”，必须消除
- 什么是笛卡尔积：把两张表的所有数据强行组合，比如 10 个员工 + 3 个部门，会查出 30 条结果（大部分是 “员工属于不存在的部门” 这种无效数据）。
比如直接写：
-- 笛卡尔积查询（别这么写！结果全是无效组合）
select * from employee, dept;
- 怎么消除：加WHERE条件，让外键和主键匹配。比如只查 “员工的dept等于部门的id” 的组合：
-- 消除无效笛卡尔积，只查真实的员工-部门对应关系
select * from employee, dept where employee.dept = dept.id;
2. 内连接：只查 “双方都有的交集”，像 “互关好友”
内连接只返回两张表匹配的数据，不匹配的直接过滤。比如查 “有部门的员工”（没部门的员工不显示）、“有员工的部门”（空部门不显示）。
分 “隐式” 和 “显式” 两种写法，显式性能更好，推荐用。
-- 案例：查员工姓名和对应的部门名称
-- 1. 隐式内连接（用WHERE条件关联）
select e.name as 员工姓名, d.name as 部门名称
from employee as e, dept as d -- 用as给表起别名，写起来更短
where e.dept = d.id; -- 核心：外键=主键
-- 2. 显式内连接（用JOIN...ON，更直观）
select e.name as 员工姓名, d.name as 部门名称
from employee as e
inner join dept as d -- inner可省略，但写全更清晰
on e.dept = d.id; -- ON后面跟关联条件，和WHERE分开
3. 外连接：查 “一方全部 + 双方交集”，像 “关注列表 vs 粉丝列表”
内连接漏数据？外连接能补全。分左外和右外，记住 “左表 / 右表” 是指FROM后面的第一张表 / 第二张表。
| 连接类型 | 核心逻辑 | 代码示例 |
|---|---|---|
| 左外连接 | 查左表所有数据，右表匹配的显示，不匹配的显示 NULL | 比如查 “所有员工，包括没部门的”：select e.name, d.name from employee e left join dept d on e.dept = d.id; |
| 右外连接 | 查右表所有数据，左表匹配的显示，不匹配的显示 NULL | 比如查 “所有部门，包括没员工的空部门”：select d.name, e.name from employee e right join dept d on e.dept = d.id; |
小技巧：左外和右外可以互相转换，比如 “左表 A join 右表 B” 等价于 “右表 B join 左表 A”，换下表的顺序就行。
4. 自连接：自己跟自己比，像 “找同事的领导”
把一张表当成两张表用（必须加别名），比如查 “员工和他的直属领导”—— 领导也在员工表中，只是manager字段存的是领导的id。
-- 案例1：查有领导的员工及其领导姓名（内连接，没领导的不显示）
select a.name as 员工, b.name as 领导
from employee a, employee b -- a是员工表别名，b是领导表别名
where a.manager = b.id; -- 员工的manager字段=领导的id
-- 案例2：查所有员工，包括没领导的（左外连接）
select a.name as 员工, b.name as 领导
from employee a
left join employee b
on a.manager = b.id; -- 没领导的员工，领导姓名显示NULL
5. 联合查询（UNION/UNION ALL）：合并结果，像 “合并两个抖音列表”
把两次查询的结果拼在一起，比如 “查技术部员工 + 销售部员工”，用UNION比用OR效率高，还不会让索引失效。
- UNION：自动去重（比如有重复数据会删掉）
- UNION ALL：不去重（速度更快，适合确定没重复数据的场景）
-- 案例：查技术部员工（dept=1）和销售部员工（dept=4）
select name, dept from employee where dept=1
union all -- 这里确定两个部门员工不重复，用UNION ALL更快
select name, dept from employee where dept=4;
最后进阶：子查询，像 “先查攻略再做事”
子查询就是 “查询里套查询”，先执行里面的小查询（子查询），再用结果查外面的大查询。比如 “先查销售部的 id，再查这个部门的员工”，不用分两次写。
根据子查询返回的结果，分四种类型，重点记前三种：
1. 标量子查询：返回单个值，像 “查某个固定值”
子查询结果是一个数字 / 字符串，常用=、>、<等符号。
-- 案例：查销售部的所有员工
select * from employee
where dept = (select id from dept where name='销售部'); -- 子查询返回销售部的id（单个值）
2. 列子查询：返回一列数据，像 “查多个选项”
子查询结果是一列（可能多行），常用IN、ALL、ANY。
-- 案例1：查销售部和市场部的员工（IN：在多个值里选）
select * from employee
where dept in (select id from dept where name='销售部' or name='市场部');
-- 案例2：查比财务部所有人工资都高的员工（ALL：满足所有值）
select * from employee
where salary > all(select salary from employee where dept=(select id from dept where name='财务部'));
3. 表子查询：返回多行多列，像 “查一个临时表”
子查询结果是一张临时表，常用IN或跟JOIN配合。
-- 案例：查2006年之后入职的员工，及其部门信息
select e.*, d.name
from (select * from employee where entrydate > '2006-01-01') as e -- 子查询是临时表e
left join dept d on e.dept = d.id;
总结：多表查询的 “3 步心法” 👇 👇 👇
- 先定关系：搞清楚是一对多、多对多还是一对一，尤其是多对多要加中间表；
- 再选方式：要交集用内连接，要补全用外连接，自己比自己用自连接，合并结果用联合查询；
- 复杂用子查询：先查 “小目标”（比如部门 id），再查 “大结果”（比如部门员工），逻辑更清晰。
