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

目录

• 一、连接类型图表（ASCII 形式）
• 二、前置代码（创建示例表）
• 三、连接方式代码示例
• 1. 内连接（INNER JOIN）
• 2. 左连接（LEFT JOIN）
• 3. 右连接（RIGHT JOIN）
• 4. 全外连接（FULL OUTER JOIN）
• 5. 交叉连接（CROSS JOIN）
• 四、结果对比表
• 五、关键点总结
• PS、自连接示意图（ASCII 形式）
• 二、前置代码（创建带层级关系的表）
• 三、自连接代码示例
• 四、结果对比表
• 五、关键点总结

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

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

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   |
   +-------------+    +-------------+
          | 全部保留   |
          | 全部保留   |

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

-- 创建示例表
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）

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 

表 departments

连接过程

employees（e）               departments（d）
+----+---------+---------+    +-----+-------------+
| id | name    | dept_id |    | id  | dept_name   |
+----+---------+---------+    +-----+-------------+
| 1  | Alice   | 101     |    | 101 | HR          |
| 2  | Bob     | 102     |    |php 102 | Engineering |
| 3  | Charlie | python103     |    | 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 表示来自部门表）

结果输出

结果说明

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

2. 左连接（LEFT JOIN）

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

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

3. 右连接（RIGHT JOIN）

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

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

4. 全外连接（FULL OUTER JOIN）

-- 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）

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

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

四、结果对比表

五、关键点总结

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

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

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

自连接 (SELF JOIN)
   +-------------+        +-------------+
   |   Table     |        |   Table     |
   |  (别名为A)   |        |  (别名为B)   |
   +-------------+        +-------------+
          | 关联自身的字段 |
          +----------------+

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

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

-- 创建带 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, 'Daviwww.devze.comd', 2);     -- 向 Bob 汇报

三、自连接代码示例

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

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. 仅查询有上级的员工（内连接版）

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     |

+----------+---------+

四、结果对比表

五、关键点总结

• 自连接本质：将同一张表视为两个逻辑表进行连接
• 必须使用别名：区分不同角色的表实例（如 e 为员工，m 为上级）
• 常用场景：
  • 组织结构查询（员工 ↔ 上级）
  • 分类层级（父分类 ↔ 子分类）
  • 路径分析（如路线 A → B → C）

4.NULL 处理：

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

到此这篇关于SQL 中多表查询的常见连接方式详解的文章就介绍到这了,更多相关sql多表查询连接方式内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！