IT俱乐部 MsSql SQL 中多表查询的常见连接方式详解

SQL 中多表查询的常见连接方式详解

发布: 2025年 6月 5日 44阅读

以下是用图表结合代码解释 SQL 中多表查询的常见连接方式：

一、连接类型图表（ASCII 形式）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
1. 内连接 (INNER JOIN)
   +-------------+    +-------------+
   |   Table A   |    |   Table B   |
   +-------------+    +-------------+
          | 交集部分  |
          +-----------+
2. 左连接 (LEFT JOIN)
   +-------------+    +-------------+
   |   Table A   |    |   Table B   |
   +-------------+    +-------------+
          | 全部保留   |
          | 右表匹配   |
3. 右连接 (RIGHT JOIN)
   +-------------+    +-------------+
   |   Table A   |    |   Table B   |
   +-------------+    +-------------+
          | 左表匹配   |
          | 全部保留   |
4. 全外连接 (FULL OUTER JOIN)
   +-------------+    +-------------+
   |   Table A   |    |   Table B   |
   +-------------+    +-------------+
          | 全部保留   |
          | 全部保留   |

二、前置代码（创建示例表）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 创建示例表
CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  dept_id INT
);
CREATE TABLE departments (
  id INT PRIMARY KEY,
  dept_name VARCHAR(50)
);
-- 插入测试数据
INSERT INTO employees VALUES
(1, 'Alice', 101),
(2, 'Bob', 102),
(3, 'Charlie', 103),
(4, 'David', NULL);
INSERT INTO departments VALUES
(101, 'HR'),
(102, 'Engineering'),
(104, 'Marketing');

三、连接方式代码示例

1. 内连接（INNER JOIN）

1
2
3
4
5
6
SELECT
  e.name AS 员工姓名,   -- 从 employees 表中选择员工姓名
  d.dept_name AS 部门名称  -- 从 departments 表中选择部门名称
FROM employees e         -- 主表：employees（别名为 e）
INNER JOIN departments d -- 连接表：departments（别名为 d）
  ON e.dept_id = d.id;   -- 连接条件：员工的部门ID = 部门的ID

分步图解

假设表中数据如下（基于你之前创建的测试数据）：

表 employees 

id name dept_id
1 Alice 101
2 Bob 102
3 Charlie 103
4 David NULL

表 departments

id dept_name
101 HR
102 Engineering
104 Marketing

连接过程

1
2
3
4
5
6
7
8
9
10
employees（e）               departments（d）
+----+---------+---------+    +-----+-------------+
| id | name    | dept_id |    | id  | dept_name   |
+----+---------+---------+    +-----+-------------+
| 1  | Alice   | 101     |    | 101 | HR          |
| 2  | Bob     | 102     |    | 102 | Engineering |
| 3  | Charlie | 103     |    | 104 | Marketing   |
| 4  | David   | NULL    |    +-----+-------------+
+----+---------+---------+
通过 INNER JOIN 连接条件 e.dept_id = d.id：
  • Alice（dept_id=101）→ 匹配到 d.id=101（HR）→ 保留记录
  • Bob（dept_id=102）→ 匹配到 d.id=102（Engineering）→ 保留记录
  • Charlie（dept_id=103）→ departments 表中无 id=103 → 排除
  • David（dept_id=NULL）→ 无法匹配 → 排除
  • Marketing（id=104）→ employees 表中无 dept_id=104 → 排除

关键概念解释

1. INNER JOIN 的作用

  • 仅保留两个表中匹配的记录
  • 如果员工的 dept_id 在 departments 表中找不到对应的 id，该员工会被排除
  • 如果部门在 employees 表中无人归属，该部门也会被排除

2. 为什么用 e.dept_id = d.id？

  • 这是两个表之间的关联关系：员工的部门编号（dept_id）必须等于部门的主键（id）

3. 别名（e 和 d）的作用

  • 简化表名书写（employees e = 给表起别名 e）
  • 明确字段来源（例如 e.name 表示来自员工表，d.dept_name 表示来自部门表）

结果输出

员工姓名 部门名称
Alice HR
Bob Engineering

结果说明

  • Charlie 被排除：因为 dept_id=103 在 departments 表中不存在
  • David 被排除：因为 dept_id=NULL 无法匹配任何部门
  • Marketing 部门 被排除：因为没有员工的 dept_id=104

2. 左连接（LEFT JOIN）

1
2
3
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

结果: 包含所有左表记录 + 右表匹配结果（David 的部门为 NULL）

3. 右连接（RIGHT JOIN）

1
2
3
SELECT e.name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;

结果: 包含所有右表记录 + 左表匹配结果（Marketing 部门无员工）

4. 全外连接（FULL OUTER JOIN）

1
2
3
4
5
6
7
8
-- MySQL 不支持 FULL OUTER JOIN，需用 UNION 模拟
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id
UNION
SELECT e.name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;

结果: 包含所有记录（含 NULL 匹配）

5. 交叉连接（CROSS JOIN）

1
2
3
SELECT e.name, d.dept_name
FROM employees e
CROSS JOIN departments d;

结果: 笛卡尔积（4 员工 × 3 部门 = 12 条记录）

四、结果对比表

连接类型 结果记录数 包含 NULL 情况
INNER JOIN 2 无
LEFT JOIN 4 右表可能为 NULL
RIGHT JOIN 3 左表可能为 NULL
FULL OUTER JOIN 5 两侧均可能为 NULL
CROSS JOIN 12 无关联条件，纯组合

五、关键点总结

  • INNER JOIN 仅保留匹配记录
  • LEFT/RIGHT JOIN 保留一侧全部记录
  • FULL OUTER JOIN 保留两侧所有记录
  • CROSS JOIN 生成笛卡尔积
  • 处理 NULL 值时需注意 COALESCE() 或 IFNULL() 函数的使用

可以通过实际运行这些 SQL 语句观察不同连接方式的差异。

PS、自连接示意图（ASCII 形式）

1
2
3
4
5
6
7
自连接 (SELF JOIN)
   +-------------+        +-------------+
   |   Table     |        |   Table     |
   |  (别名为A)   |        |  (别名为B)   |
   +-------------+        +-------------+
          | 关联自身的字段 |
          +----------------+

典型场景：员工表查询上下级关系、分类层级关系等

二、前置代码（创建带层级关系的表）

1
2
3
4
5
6
7
8
9
10
11
12
-- 创建带 manager_id 的员工表
CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  manager_id INT
);
-- 插入测试数据
INSERT INTO employees VALUES
(1, 'Alice', NULL),  -- 顶级管理者
(2, 'Bob', 1),       -- 向 Alice 汇报
(3, 'Charlie', 1),   -- 向 Alice 汇报
(4, 'David', 2);     -- 向 Bob 汇报

三、自连接代码示例

1. 查询员工及其上级（左连接版）

1
2
3
4
5
SELECT
  e.name AS employee,
  m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

结果：

+———-+———-+
| employee | manager  |
+———-+———-+
| Alice    | NULL     |  — 没有上级
| Bob      | Alice    |
| Charlie  | Alice    |
| David    | Bob      |
+———-+———-+

2. 仅查询有上级的员工（内连接版）

1
2
3
4
5
SELECT
  e.name AS employee,
  m.name AS manager
FROM employees e
INNER JOIN employees m ON e.manager_id = m.id;

结果：

+———-+———+
| employee | manager |
+———-+———+
| Bob      | Alice   |
| Charlie  | Alice   |
| David    | Bob     |
+———-+———+

四、结果对比表

连接方式 结果记录数 包含 NULL 情况 典型用途
自连接-左连接 4 顶级管理者的上级为 NULL 显示完整层级结构
自连接-内连接 3 无 NULL 仅显示有上下级关系的记录

五、关键点总结

  • 自连接本质：将同一张表视为两个逻辑表进行连接
  • 必须使用别名：区分不同角色的表实例（如 e 为员工，m 为上级）
  • 常用场景：

    • 组织结构查询（员工 ↔ 上级）
    • 分类层级（父分类 ↔ 子分类）
    • 路径分析（如路线 A → B → C）

4.NULL 处理：

1
2
3
4
5
SELECT
  e.name,
  COALESCE(m.name, '顶级管理者') AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

到此这篇关于SQL 中多表查询的常见连接方式详解的文章就介绍到这了,更多相关sql多表查询连接方式内容请搜索IT俱乐部以前的文章或继续浏览下面的相关文章希望大家以后多多支持IT俱乐部！

本文收集自网络，不代表IT俱乐部立场，转载请注明出处。https://www.2it.club/database/mssql/15329.html
29赞
标签:
上一篇
下一篇

相关推荐

联系我们

联系我们

在线咨询: QQ交谈

邮箱: 1120393934@qq.com

工作时间：周一至周五，9:00-17:30，节假日休息

关注微信
微信扫一扫关注我们

微信扫一扫关注我们

返回顶部