---
title: "12-多表查询与集合运算"
created: 2026-01-07
tags:
  - 项目筑基
---

# 多表查询与集合运算

### **7.12 多表查询**

#### **7.12.1 概述**

```mermaid
flowchart TB
    subgraph 多表查询分类
        subgraph 连接查询
            A1[内连接 INNER JOIN]
            A2[外连接]
            A3[交叉连接 CROSS JOIN]

            A1 --> A11[等值连接]
            A1 --> A12[非等值连接]
            A1 --> A13[自然连接]

            A2 --> A21[左外连接 LEFT JOIN]
            A2 --> A22[右外连接 RIGHT JOIN]
            A2 --> A23[全外连接]
        end

        subgraph 子查询
            B1[单行单列子查询]
            B2[多行单列子查询]
            B3[多行多列子查询]
        end
    end
```

#### **7.12.2 连接查询**

##### **连接条件**

```text
-- 公共列连接（通常是主键-外键关系）
ON 学院.学院编号 = 课程.学院编号

-- 两表的公共列形态：
-- 1. 都是主键
-- 2. 一个主键，一个外键
```

##### **内连接 INNER JOIN**

**等值连接：**

```sql
-- 隐式内连接（使用 WHERE）
SELECT * FROM stu, sc
WHERE stu.sno = sc.sno;

-- 显式内连接（使用 INNER JOIN）
SELECT * FROM stu
INNER JOIN sc ON stu.sno = sc.sno;

-- INNER 可以省略
SELECT * FROM stu
JOIN sc ON stu.sno = sc.sno;
```

**内连接示意图：**

```mermaid
flowchart LR
    subgraph stu表
        S1[S001]
        S2[S002]
        S3[S003]
    end

    subgraph sc表
        C1[S001]
        C2[S002]
        C3[S005]
    end

    S1 <-->|✓匹配| C1
    S2 <-->|✓匹配| C2
    S3 -.-|✗无匹配| C3
```

> 💡 结果：只返回两表都有的 S001, S002

**自然连接：**

```sql
-- MySQL 支持 NATURAL JOIN（自动匹配同名列，去除重复列）
SELECT * FROM stu NATURAL JOIN sc;

-- 等价于手动指定列
SELECT stu.sno, stu.sname, sc.cno, sc.grade
FROM stu
JOIN sc ON stu.sno = sc.sno;
```

**三表连接：**

```sql
-- 查询学生选课成绩，显示学生姓名、课程名、成绩
SELECT 学生.姓名, 课程.课程名称, 选课成绩.成绩
FROM 学生
JOIN 选课成绩 ON 学生.学号 = 选课成绩.学号
JOIN 课程 ON 选课成绩.课程编号 = 课程.课程编号;
```

##### **外连接**

**左外连接 LEFT JOIN：**

```sql
-- 显示所有学生，包括没有选课的
SELECT stu.*, sc.*
FROM stu
LEFT JOIN sc ON stu.sno = sc.sno;
```

| **sno** | **sname** | **cno** | **grade** |
| --- | --- | --- | --- |
| S001 | 张三 | C001 | 85 |
| S002 | 李四 | C001 | 90 |
| S003 | 王五 | NULL | NULL |

> 💡 左表（stu）的所有记录都保留

**右外连接 RIGHT JOIN：**

```sql
-- 显示所有成绩，包括没有对应学生的
SELECT stu.*, sc.*
FROM stu
RIGHT JOIN sc ON stu.sno = sc.sno;
```

**左右连接互换：**

```sql
-- 以下两个查询结果相同
SELECT * FROM A LEFT JOIN B ON A.id = B.id;
SELECT * FROM B RIGHT JOIN A ON A.id = B.id;

-- 只是把表的位置交换了
```

**全外连接：**

```sql
-- MySQL 不直接支持 FULL OUTER JOIN
-- 需要使用 UNION 模拟

SELECT * FROM stu LEFT JOIN sc ON stu.sno = sc.sno
UNION
SELECT * FROM stu RIGHT JOIN sc ON stu.sno = sc.sno;
```

##### **交叉连接 CROSS JOIN**

```sql
-- 笛卡尔积：每行与每行组合
SELECT * FROM stu CROSS JOIN sc;

-- 等价于
SELECT * FROM stu, sc;

-- 如果 stu 有12行，sc 有12行，结果有 144 行
```

##### **连接小结**

| **连接类型** | **结果说明** |
| --- | --- |
| `INNER JOIN` | 只返回匹配的行（结果最少） |
| `LEFT JOIN` | 返回左表所有行 + 匹配的右表行 |
| `RIGHT JOIN` | 返回右表所有行 + 匹配的左表行 |
| `FULL JOIN` | 返回两表所有行（MySQL需用UNION模拟） |
| `CROSS JOIN` | 返回笛卡尔积（结果最多） |

##### **连接 + 其他子句**

```sql
-- 查询教师欧阳淑芳所上的所有课堂
SELECT 课堂.课堂名称, 课堂.开课年份, 课堂.开课学期
FROM 教师
INNER JOIN 课堂 ON 教师.教师编号 = 课堂.教师编号
WHERE 教师.姓名 = '欧阳淑芳'
ORDER BY 课堂.开课年份, 课堂.开课学期;

-- 统计计算机学院每位教师的教学工作量
SELECT 教师.教师编号, SUM(课程.学时数) AS 学时数
FROM 教师
INNER JOIN 课堂 ON 教师.教师编号 = 课堂.教师编号
INNER JOIN 课程 ON 课堂.课程编号 = 课程.课程编号
INNER JOIN 学院 ON 教师.学院编号 = 学院.学院编号
WHERE 课堂.开课年份 = '2017-2018'
  AND 课堂.开课学期 = '一'
  AND 学院名称 = '计算机学院'
GROUP BY 教师.教师编号
ORDER BY 学时数 DESC;
```

#### **7.12.3 子查询**

##### **概述**

![[image-2802d1c5.png]]

##### **单行单列子查询**

```sql
-- 查询成绩高于叶淑华的学生
SELECT * FROM stus
WHERE score > (
    SELECT score FROM stus WHERE name = '叶淑华'
);
```

##### **多行单列子查询**

```sql
-- 查询财务部和销售部的所有员工
SELECT * FROM emp
WHERE dep_id IN (
    SELECT id FROM dept
    WHERE dept_name = '财务部' OR dept_name = '销售部'
);

-- 使用 ANY（满足任意一个）
SELECT * FROM 选课成绩
WHERE 成绩 > ANY (
    SELECT 成绩 FROM 选课成绩 WHERE 学号 = 'S001'
);

-- 使用 ALL（满足所有）
SELECT * FROM 选课成绩
WHERE 成绩 > ALL (
    SELECT 成绩 FROM 选课成绩 WHERE 学号 = 'S001'
);
```

##### **多行多列子查询（派生表）**

```sql
-- 查询年龄大于20的员工信息和部门信息
SELECT * FROM
    (SELECT * FROM emp WHERE age > 20) AS t1
JOIN dept ON t1.dep_id = dept.id;
```

> ⚠️ **注意**：MySQL 要求派生表必须有别名！

##### **不相关子查询**

```sql
-- 子查询可以独立执行
SELECT * FROM 学生
WHERE 学院编号 = (
    SELECT 学院编号 FROM 学院 WHERE 学院名称 = '计算机学院'
);

-- 判断用 = 还是 IN：
-- 子查询返回单值 → 用 =
-- 子查询返回多值 → 用 IN
```

##### **相关子查询**

```sql
-- 子查询引用了外部表，无法独立执行
-- 查询每门课成绩高于该课平均分的记录
SELECT * FROM sc AS t1
WHERE grade > (
    SELECT AVG(grade) FROM sc AS t2
    WHERE t1.cno = t2.cno  -- 引用了外部的 t1
);
```

> 💡 **相关子查询执行过程**
>
> 外部查询每处理一行，子查询就执行一次：
>
> ```
> 第1行：t1.cno = 'C001' → 计算C001的平均分 → 比较
> 第2行：t1.cno = 'C001' → 计算C001的平均分 → 比较
> 第3行：t1.cno = 'C002' → 计算C002的平均分 → 比较
> ...
> ```

##### **EXISTS 子查询**

```sql
-- 查询有选课记录的学生
SELECT * FROM 学生
WHERE EXISTS (
    SELECT 1 FROM 选课成绩
    WHERE 选课成绩.学号 = 学生.学号
);

-- 查询没有选课记录的学生
SELECT * FROM 学生
WHERE NOT EXISTS (
    SELECT 1 FROM 选课成绩
    WHERE 选课成绩.学号 = 学生.学号
);
```

##### **别名的重要性**

```sql
-- 自关联查询：同一张表比较
-- 查询成绩高于自己平均分的记录

-- ✗ 错误写法：无法区分两个 sc
SELECT sno, cno
FROM sc
WHERE grade > (SELECT AVG(grade) FROM sc WHERE sc.sno = sc.sno);

-- ✓ 正确写法：使用别名区分
SELECT t1.sno, t1.cno
FROM sc AS t1
WHERE t1.grade > (
    SELECT AVG(t2.grade)
    FROM sc AS t2
    WHERE t1.sno = t2.sno
);
```

### **7.13 集合运算**

#### **7.13.1 概述**

```sql
-- 基本格式
查询1
<集合运算>
查询2
[ORDER BY ...]
```

#### **7.13.2 UNION 并集**

```sql
-- 合并两个查询结果，去除重复行
SELECT 姓名, 性别 FROM 教师
UNION
SELECT 姓名, 性别 FROM 学生;

-- 保留重复行
SELECT 姓名, 性别 FROM 教师
UNION ALL
SELECT 姓名, 性别 FROM 学生;
```

| **运算** | **说明** |
| --- | --- |
| `UNION` | 去除重复行（较慢，需要排序去重） |
| `UNION ALL` | 保留所有行（较快，直接合并） |

> 💡 如果确定没有重复，或需要保留重复，用 `UNION ALL` 效率更高

#### **7.13.3 INTERSECT 交集（MySQL 8.0.31+）**

```sql
-- 返回两个查询都有的记录
SELECT 姓名 FROM 教师
INTERSECT
SELECT 姓名 FROM 学生;

-- MySQL 8.0.31 之前的替代方案
SELECT DISTINCT 教师.姓名
FROM 教师
INNER JOIN 学生 ON 教师.姓名 = 学生.姓名;
```

#### **7.13.4 EXCEPT 差集（MySQL 8.0.31+）**

```sql
-- 返回在第一个查询中但不在第二个查询中的记录
SELECT 姓名 FROM 教师
EXCEPT
SELECT 姓名 FROM 学生;

-- MySQL 8.0.31 之前的替代方案
SELECT 姓名 FROM 教师
WHERE 姓名 NOT IN (SELECT 姓名 FROM 学生);

-- 或使用 LEFT JOIN
SELECT 教师.姓名
FROM 教师
LEFT JOIN 学生 ON 教师.姓名 = 学生.姓名
WHERE 学生.姓名 IS NULL;
```

#### **7.13.5 集合运算示意图**

![[image-49d1c2b8.png]]

#### **7.13.6 集合运算规则**

| **规则** | **说明** |
| --- | --- |
| 列数相同 | 两个查询的列数必须相同 |
| 类型兼容 | 对应列的数据类型必须兼容 |
| ORDER BY 位置 | 只能出现在最后，对整个结果排序 |
| 列名 | 以第一个查询为准 |
| 优先级 | INTERSECT > UNION = EXCEPT |

### **7.14 快速参考**

#### **7.14.1 DML 语句汇总**

```sql
-- 插入
INSERT INTO 表名(列1, 列2) VALUES (值1, 值2);
INSERT INTO 表名(列1, 列2) VALUES (值1, 值2), (值3, 值4);

-- 删除
DELETE FROM 表名 WHERE 条件;
TRUNCATE TABLE 表名;

-- 更新
UPDATE 表名 SET 列1 = 值1, 列2 = 值2 WHERE 条件;

-- 查询
SELECT 列 FROM 表
WHERE 条件
GROUP BY 分组列
HAVING 分组条件
ORDER BY 排序列
LIMIT 偏移, 数量;
```

#### **7.14.2 常用函数速查**

| **SQL Server** | **MySQL** | **说明** |
| --- | --- | --- |
| `LEN()` | `CHAR_LENGTH()` | 字符长度 |
| `GETDATE()` | `NOW()` | 当前时间 |
| `+` | `CONCAT()` | 字符串连接 |
| `TOP(N)` | `LIMIT N` | 前N条 |
| `ISNULL()` | `IFNULL()` | 空值替换 |
| `CONVERT()` | `CAST() / CONVERT()` | 类型转换 |

#### **7.14.3 SQL 执行顺序**

![[image-f3e1bb34.png]]

| **顺序** | **子句** | **说明** |
| --- | --- | --- |
| 1 | FROM | 确定数据来源 |
| 2 | JOIN | 表连接 |
| 3 | WHERE | 行过滤 |
| 4 | GROUP BY | 分组 |
| 5 | HAVING | 组过滤 |
| 6 | SELECT | 选择列 |
| 7 | DISTINCT | 去重 |
| 8 | ORDER BY | 排序 |
| 9 | LIMIT | 分页 |

---
⬅️ [[11-聚合排序与分页|聚合排序与分页]] 🏠 [[00-数据库|00-数据库]] ➡️ [[13-索引|索引]]