多表查询：

        多表关系

        多表查询概述

        内连接

        外连接

        自连接

        子查询

        多表查询案例

一、多表关系（先搞懂表怎么关联）

多表查询的前提是表与表之间存在关联关系，常见 3 种关系：

1. 一对一（1:1）

• 逻辑：A 表的 1 条记录，只能对应 B 表的 1 条记录
• 示例：用户表 user 和 用户详情表 user_detail（一个用户只有一个详情）
• 实现：在从表中添加外键，关联主表主键，并给外键加 UNIQUE 唯一约束

2. 一对多（1:N）（最常用，你的 emp/dept 就是这种）

• 逻辑：A 表的 1 条记录，对应 B 表的 N 条记录
• 示例：部门表 dept（1 个部门）→ 员工表 emp（N 个员工）
• 实现：在 ** 多的一方（emp 表）添加外键，关联一的一方（dept 表）** 的主键

  sql

• -- emp表的dept_id是外键，关联dept表的id
  CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES dept(id)

constraint fk_emp_dept foreign key(dept_id) references dept(id)

3. 多对多（M:N）

• 逻辑：A 表的 1 条记录对应 B 表的 N 条，B 表的 1 条对应 A 表的 M 条
• 示例：学生表 student 和 课程表 course（一个学生选多门课，一门课有多个学生）
• 实现：必须创建中间表（关联表），中间表包含两个表的外键，分别关联两张表的主键

二、多表查询概述

多表查询就是同时从 2 张及以上的表中查询数据，核心是通过 ** 关联条件（ON 子句）** 把表连接起来，避免「笛卡尔积」错误。

1. 笛卡尔积（必须避免的错误）

如果直接写 SELECT * FROM emp, dept; 不加关联条件，会得到两张表所有行的组合（比如 emp 有 8 行、dept 有 5 行，结果就是 8×5=40 行，大部分是无效数据）。

✅ 正确做法：必须加关联条件，过滤出符合关系的有效数据。

2. 多表查询的分类

表格

分类	核心作用	关键字
内连接	只查询两张表匹配成功的数据	INNER JOIN ... ON
外连接	包含「匹配成功 + 某张表的全部数据」	LEFT JOIN / RIGHT JOIN
自连接	一张表自己和自己连接（比如员工 - 领导）	表别名 a JOIN 表别名 b ON ...
子查询	一个查询嵌套在另一个查询中	嵌套 SELECT

三、内连接（INNER JOIN）

1. 核心逻辑

只返回两张表中「关联条件匹配成功」的数据，匹配不到的会被过滤掉。

• 等价于：取两张表的交集
• 语法（两种写法，效果完全一致）：

  sql

--隐式内连接

select 字段 from 表1，表2 where 关联条件；

--显示连接

select 字段 from 表1 inner join 表2 on 关联条件 [where 其他条件]

• -- 隐式内连接（旧写法，不推荐）
  SELECT 表1.字段，表2.字段
  FROM 表1, 表2
  WHERE 关联条件;
  
  -- 显式内连接（标准写法，推荐）
  SELECT 表1.字段 ，表2.字段
  FROM 表1 
  INNER JOIN 表2 
  ON  表1.关联字段=表2.关联字段
  [WHERE 其他条件];

2. 结合 emp/dept 实战

需求：查询所有员工的姓名、职位，以及所属部门名称（只查有部门的员工）

显示内连接

select

        e.name 员工姓名,

        e.job 职位，

        d.name 部门名称

from emp e

inner join dept d on e.dept_id=d.id;

隐式内连接

select 

        e.name 员工姓名，

        e.job 职位，

        d.name 部门名称

from emp e，dept d

where e.dept_id=d.id;

-- 显式内连接（推荐）
SELECT 
  e.name 员工姓名,
  e.job 职位,
  d.name 部门名称
FROM emp e
INNER JOIN dept d ON e.dept_id = d.id;

-- 隐式内连接（等价，不推荐）
SELECT e.name, e.job, d.name
FROM emp e, dept d
WHERE e.dept_id = d.id;

diagram 图表，示意图，图解(diagram的复数）

四、外连接（LEFT/RIGHT JOIN）

1. 核心逻辑

不仅返回匹配成功的数据，还会返回某一张表的全部数据，即使另一张表没有匹配项（匹配不到的字段会显示 NULL）。

（1）左外连接（LEFT JOIN，最常用）

• 逻辑：以左表（JOIN 左边的表）为基准，返回左表的全部数据，右表只返回匹配成功的数据，匹配不到的字段为 NULL

• 语法：select 左表.字段，右表.字段 from 左表 left   join 右表 on 左表.关联字段=右表.关联字段。 

• SELECT 字段 FROM 左表 LEFT [OUTER] JOIN 右表 ON 关联条件;
  

• 实战：查询所有员工，包括没有部门的员工

  sql

  SELECT 
    e.name 员工姓名,
    e.job 职位,
    d.name 部门名称
  FROM emp e
  LEFT JOIN dept d ON e.dept_id = d.id;
  

  • 结果：所有员工都会显示，没有部门的员工，部门名称 列显示 NULL

2. 查询所有员工（包括没部门的）

sql

select emp.name, dept.name
from emp
left join dept
on emp.dept_id = dept.id;

实例：. 查询所有员工 + 部门 + 入职日期

sql

select emp.name, emp.entrydate, dept.name
from emp
left join dept
on emp.dept_id = dept.id;

五、自连接（Self Join）

1. 核心逻辑

一张表自己和自己连接，本质是内连接 / 外连接的特殊形式，必须给表起两个不同的别名，区分两个角色。

• 适用场景：表中存在「上下级」「父子」关系，比如 emp 表的 managerid（直属领导 ID）关联 emp 表的 id

2. 实战：查询员工姓名，以及其直属领导的姓名

sql

-- 给emp起两个别名：e（员工）、m（领导）
SELECT 
  e.name 员工姓名,
  m.name 直属领导姓名
FROM emp e
LEFT JOIN emp m ON e.managerid = m.id;

• 用 LEFT JOIN 保证没有领导的顶级员工（比如总经理）也会显示，领导姓名为 NULL
• 用 INNER JOIN 会过滤掉没有领导的员工

六、子查询（Nested Query）

1. 核心逻辑

一个 SELECT 查询嵌套在另一个 SELECT 查询中，内层查询的结果作为外层查询的条件 / 数据源。

• 子查询可以出现在 WHERE、FROM、SELECT 等位置
• 按结果集分类：
  • 标量子查询：结果是单个值（1 行 1 列）
  • 列子查询：结果是一列（N 行 1 列）
  • 行子查询：结果是一行（1 行 N 列）
  • 表子查询：结果是一张表（N 行 N 列）

七、多表查询综合案例（直接运行，巩固所有知识点）

案例 1：查询所有员工的姓名、职位、部门名称、直属领导姓名

sql

SELECT 
  e.name 员工姓名,
  e.job 职位,
  d.name 部门名称,
  m.name 直属领导
FROM emp e
LEFT JOIN dept d ON e.dept_id = d.id
LEFT JOIN emp m ON e.managerid = m.id;

• 用两次左连接：一次关联部门表，一次关联领导表，保证所有员工都显示

案例 2：查询每个部门的员工人数，没有员工的部门显示 0

sql

SELECT 
  d.name 部门名称,
  COUNT(e.id) 员工人数  -- COUNT(主键)，NULL会被统计为0
FROM dept d
LEFT JOIN emp e ON d.id = e.dept_id
GROUP BY d.id, d.name;

案例 3：查询薪资高于部门平均薪资的员工

sql

SELECT 
  e.name 员工姓名,
  e.salary 员工薪资,
  avg_sal.avg_sal 部门平均薪资
FROM emp e
INNER JOIN (
  -- 子查询：统计每个部门的平均薪资
  SELECT dept_id, AVG(salary) avg_sal FROM emp GROUP BY dept_id
) avg_sal ON e.dept_id = avg_sal.dept_id
WHERE e.salary > avg_sal.avg_sal;

八、核心避坑指南

• 必须加关联条件：否则会产生笛卡尔积，出现大量无效数据
• 表别名规范：多表查询必须给表起别名，避免字段名冲突（比如两张表都有 name 字段）
• 左 / 右连接选择：
  • 要保留左表全部数据 → 用 LEFT JOIN
  • 要保留右表全部数据 → 用 RIGHT JOIN
  • 只保留匹配数据 → 用 INNER JOIN
• 子查询性能：复杂子查询可以用多表连接替代，性能更优
• NULL 值处理：外连接会产生 NULL 值，统计时用 COUNT(主键) 而不是 COUNT(*)

九、多表查询速查表

表格

连接类型	关键字	核心逻辑	适用场景
内连接	INNER JOIN	只返回匹配数据	只查两张表都存在的关联数据
左外连接	LEFT JOIN	保留左表全部数据	查左表所有数据，右表匹配不上显示 NULL
右外连接	RIGHT JOIN	保留右表全部数据	查右表所有数据，左表匹配不上显示 NULL
自连接	表别名 JOIN 表别名	表自己连自己	查上下级、父子关系
子查询	嵌套 SELECT	内层结果作为外层条件	分步查询、复杂条件过滤