前言

在实际开发中，MySQL数据库的表设计往往遵循“单一职责原则”，将不同维度的数据拆分存储。例如，我们会将部门信息集中存储在「部门总表」中，而员工信息、岗位信息等细分数据则存储在对应的「分表」中。这种分表设计提升了数据管理的灵活性和效率，但也带来了新的需求——如何通过部门总表的信息，精准关联并查询出各个分表的相关数据？

本文将以“部门总表+员工分表”为核心场景，讲解MySQL多表查询的分类、准备工作和实操示例。

MySQL多表查询分类

MySQL多表查询的核心是“关联”，即通过两个或多个表中的共同字段（如部门ID），将分散在不同表中的数据整合起来。根据关联方式的不同，主要分为「多表连接查询」和「子查询」两大类，下面结合“部门总表查分表”场景详细讲解。

多表连接查询

多表连接查询是最常用的方式，核心是通过「连接条件」将多个表“拼接”成一个虚拟表，再从虚拟表中查询所需数据。其中，复合条件连接查询是实际开发中最常用的形式——除了核心关联条件（如部门ID匹配），还可以添加筛选条件（如员工性别、入职时间），精准过滤数据。

核心逻辑：部门总表（部门ID） ↔ 分表（员工表/岗位表，部门ID），通过部门ID将两个表关联，一次性查询出部门信息+分表信息。

子查询

子查询（又称嵌套查询），是将一个查询语句的结果作为另一个查询语句的条件或数据源。简单来说，就是“先查一个结果，再用这个结果查另一个表”，非常适合“根据部门总表的查询结果，进一步查询分表信息”的场景。

核心逻辑：先从部门总表中查询出目标部门（如“技术部”）的ID，再以这个部门ID为条件，查询分表（员工表）中该部门的所有员工信息。

二、准备工作

在开始多表查询实操前，我们需要先准备两个核心表：「部门信息表（部门总表）」和「员工信息表（分表）」，并插入测试数据。所有SQL语句均可直接复制执行，方便大家快速上手。

准备员工信息表

mysql> create table info(
    -> name char(50),
    -> age int,
    -> dep_num int,
    -> level_num int);
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> desc info;
+-----------+----------+------+-----+---------+-------+
| Field     | Type     | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| name      | char(50) | YES  |     | NULL    |       |
| age       | int(11)  | YES  |     | NULL    |       |
| dep_num   | int(11)  | YES  |     | NULL    |       |
| level_num | int(11)  | YES  |     | NULL    |       |
+-----------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> 
mysql> insert into info values 
    -> ('zhangsan',23,101,1),
    -> ('lisi',25,102,2),
    -> ('wangwu',30,102,3),
    -> ('zhaosi',30,103,4),
    -> ('sunba',35,NULL,NULL);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from info;
+----------+------+---------+-----------+
| name     | age  | dep_num | level_num |
+----------+------+---------+-----------+
| zhangsan |   23 |     101 |         1 |
| lisi     |   25 |     102 |         2 |
| wangwu   |   30 |     102 |         3 |
| zhaosi   |   30 |     103 |         4 |
| sunba    |   35 |    NULL |      NULL |
+----------+------+---------+-----------+
5 rows in set (0.00 sec)

准备部门信息表

mysql> create table department(
    -> dep_num int,
    -> dep_name varchar(50),
    -> dep_des varchar(100));
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> 
mysql> insert into department values
    -> (101,'hr','recruit,training'),
    -> (102,'tec','system,network,service'),
    -> (103,'exp','C++,python,php'),
    -> (104,'admin','administrator');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> 
mysql> desc department;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| dep_num  | int(11)      | YES  |     | NULL    |       |
| dep_name | varchar(50)  | YES  |     | NULL    |       |
| dep_des  | varchar(100) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> 
mysql> select * from department;
+---------+----------+------------------------+
| dep_num | dep_name | dep_des                |
+---------+----------+------------------------+
|     101 | hr       | recruit,training       |
|     102 | tec      | system,network,service |
|     103 | exp      | C++,python,php         |
|     104 | admin    | administrator          |
+---------+----------+------------------------+
4 rows in set (0.00 sec)

mysql> 

表结构与关联示图

为了更清晰地理解两个表的关联关系，我们先看表结构示图和关联逻辑，再进行实操查询。

mysql> select * from info;
+----------+------+---------+-----------+
| name     | age  | dep_num | level_num |
+----------+------+---------+-----------+
| zhangsan |   23 |     101 |         1 |
| lisi     |   25 |     102 |         2 |
| wangwu   |   30 |     102 |         3 |
| zhaosi   |   30 |     103 |         4 |
| sunba    |   35 |    NULL |      NULL |
+----------+------+---------+-----------+
5 rows in set (0.00 sec)

mysql> select * from department;
+---------+----------+------------------------+
| dep_num | dep_name | dep_des                |
+---------+----------+------------------------+
|     101 | hr       | recruit,training       |
|     102 | tec      | system,network,service |
|     103 | exp      | C++,python,php         |
|     104 | admin    | administrator          |
+---------+----------+------------------------+
4 rows in set (0.00 sec)

多表的连接查询

连接查询分类

多表连接查询主要分为以下三类，核心区别在于关联条件的使用和结果集的展示范围：

• 交叉连接：生成笛卡尔积，不使用任何匹配条件，返回两个表所有数据的组合。

  
  

• 内连接：只连接两个表中匹配的行，不匹配的行不会出现在结果集中。

  
  

• 外连接：分为左连接和右连接，会保留其中一个表的所有数据，另一个表不匹配的部分填充为NULL：

  • 左连接：显示左边表内所有的值，不论在右边表内匹不匹配。

    
    

  • 右连接：显示右边表内所有的值，不论在左边表内匹不匹配。

    
    

交叉连接

特点

返回两个表所有数据的全部组合（笛卡尔积），组合数量 = 左表行数 × 右表行数（例如A表5行，B表7行，最终会返回35行结果），无需设置匹配条件，实操中需谨慎使用（大数据量易造成结果集冗余）。

语法

无需添加任何匹配条件，直接关联两个表即可，基本语法如下：

select 表1.字段名, 表2.字段名 from 表1, 表2;

示范

结合前面准备的info（员工表）和department（部门表），执行交叉连接查询，获取所有员工与所有部门的组合：

mysql> select info.name,info.age,info.dep_num,department.dep_name from info,department;
+----------+------+---------+----------+
| name     | age  | dep_num | dep_name |
+----------+------+---------+----------+
| zhangsan |   23 |     101 | hr       |
| zhangsan |   23 |     101 | tec      |
| zhangsan |   23 |     101 | exp      |
| zhangsan |   23 |     101 | admin    |
| lisi     |   25 |     102 | hr       |
| lisi     |   25 |     102 | tec      |
| lisi     |   25 |     102 | exp      |
| lisi     |   25 |     102 | admin    |
| wangwu   |   30 |     102 | hr       |
| wangwu   |   30 |     102 | tec      |
| wangwu   |   30 |     102 | exp      |
| wangwu   |   30 |     102 | admin    |
| zhaosi   |   30 |     103 | hr       |
| zhaosi   |   30 |     103 | tec      |
| zhaosi   |   30 |     103 | exp      |
| zhaosi   |   30 |     103 | admin    |
| sunba    |   35 |    NULL | hr       |
| sunba    |   35 |    NULL | tec      |
| sunba    |   35 |    NULL | exp      |
| sunba    |   35 |    NULL | admin    |
+----------+------+---------+----------+
20 rows in set (0.00 sec)

内连接

特点

核心是“匹配才显示”，只有当两个表中通过关联字段（如dep_num）匹配成功的行，才会出现在结果集中，不匹配的行会被过滤掉。这是实际开发中最常用的连接方式之一，能精准获取关联数据。

需求

显示所有员工对应的部门信息，即关联info表和department表，只展示有对应部门的员工数据。

示例

以dep_num（部门编号）作为关联条件，执行内连接查询，SQL语句及执行效果如下：

mysql> select info.name,info.age,info.dep_num,department.dep_name from info,department where info.dep_num = department.dep_num;
+----------+------+---------+----------+
| name     | age  | dep_num | dep_name |
+----------+------+---------+----------+
| zhangsan |   23 |     101 | hr       |
| lisi     |   25 |     102 | tec      |
| wangwu   |   30 |     102 | tec      |
| zhaosi   |   30 |     103 | exp      |
+----------+------+---------+----------+
4 rows in set (0.00 sec)

思考问题

观察上述内连接查询结果，会发现员工sunba没有出现在结果中，可以思考一下：孙八去哪了？（提示：结合sunba的dep_num值和内连接“匹配才显示”的特点分析）

外连接

特点

核心是“匹配则显示，不匹配则补NULL”，以左表或右表为基准（主表），保留主表的所有数据，另一个表（从表）中不匹配的字段填充为NULL，区别于内连接“只显示匹配行”的逻辑。

语法

外连接核心语法分左连接和右连接，格式统一为：

-- 左连接：A表为主表，保留A表所有数据
SELECT 字段名 FROM A表 LEFT JOIN B表 ON 关联条件;

-- 右连接：B表为主表，保留B表所有数据
SELECT 字段名 FROM A表 RIGHT JOIN B表 ON 关联条件;

左连接（LEFT JOIN ON）

需求

找出所有员工及所属的部门，包括没有部门的员工（即保留info表所有员工，即使其dep_num为NULL），查看所有员工的部门信息。

示例

以info表（员工表）为主表，关联department表（部门表），SQL语句及执行结果如下：

mysql> select info.name,info.age,info.dep_num,department.dep_name from info left join department on info.dep_num = department.dep_num;
+----------+------+---------+----------+
| name     | age  | dep_num | dep_name |
+----------+------+---------+----------+
| zhangsan |   23 |     101 | hr       |
| lisi     |   25 |     102 | tec      |
| wangwu   |   30 |     102 | tec      |
| zhaosi   |   30 |     103 | exp      |
| sunba    |   35 |    NULL | NULL     |
+----------+------+---------+----------+
5 rows in set (0.00 sec)

思考问题

1. 上述左连接结果是否显示了所有部门？
   答：没有。左连接以info表（员工表）为主表，仅保留员工表所有数据，部门表中无对应员工的部门（如admin部门，dep_num=104）不会显示。
2. 如果希望显示所有部门中的员工，该如何查询？（提示：换用右连接，以部门表为主表）

右连接（RIGHT JOIN ON）

右连接以右表（此处为department部门表）为主表，保留部门表所有数据，员工表中无对应部门的字段填充为NULL，以下结合3个需求演示实操。

需求1：显示所有的部门的员工信息

示范

mysql> select info.name,info.age,info.dep_num,department.dep_name from info right join department on info.dep_num = department.dep_num;
+----------+------+---------+----------+
| name     | age  | dep_num | dep_name |
+----------+------+---------+----------+
| zhangsan |   23 |     101 | hr       |
| lisi     |   25 |     102 | tec      |
| wangwu   |   30 |     102 | tec      |
| zhaosi   |   30 |     103 | exp      |
| NULL     | NULL |    NULL | admin    |
+----------+------+---------+----------+
5 rows in set (0.00 sec)

需求2：找出公司所有部门中年龄大于25岁的员工

示例

mysql> select info.name,info.age,info.dep_num,department.dep_name 
    -> from
    ->  info right join department 
    -> on
    ->  info.dep_num = department.dep_num 
    -> AND  age > 25;
+--------+------+---------+----------+
| name   | age  | dep_num | dep_name |
+--------+------+---------+----------+
| wangwu |   30 |     102 | tec      |
| zhaosi |   30 |     103 | exp      |
| NULL   | NULL |    NULL | hr       |
| NULL   | NULL |    NULL | admin    |
+--------+------+---------+----------+
4 rows in set (0.00 sec)

找出公司所有部门中的员工，对他们的年龄排序

示例

mysql> select info.name,info.age,info.dep_num,department.dep_name 
    -> from 
    -> info right join department 
    -> on 
    -> info.dep_num = department.dep_num 
    -> ORDER BY 
    -> age 
    -> ASC;
+----------+------+---------+----------+
| name     | age  | dep_num | dep_name |
+----------+------+---------+----------+
| NULL     | NULL |    NULL | admin    |
| zhangsan |   23 |     101 | hr       |
| lisi     |   25 |     102 | tec      |
| zhaosi   |   30 |     103 | exp      |
| wangwu   |   30 |     102 | tec      |
+----------+------+---------+----------+
5 rows in set (0.00 sec)

子查询

子查询简介

子查询（又称嵌套查询），核心特点是：父查询的执行依赖于子查询的结果。 子查询中可包含的关键字：IN、NOT IN、ANY、ALL、EXISTS、NOT EXISTS； 子查询中可包含的比较运算符：=、!=、>、<、>=、<= 等。

带IN关键字的子查询（范围匹配）

需求

查询员工年龄大于等于25岁的部门（即先找出年龄≥25的员工所属部门编号，再查询对应部门信息）。

演示

mysql>  select dep_num,dep_name 
    -> from 
    -> department 
    -> where 
    -> dep_num 
    -> in 
    -> (select distinct dep_num from info where age >=25);
+---------+----------+
| dep_num | dep_name |
+---------+----------+
|     102 | tec      |
|     103 | exp      |
+---------+----------+
2 rows in set (0.01 sec)

说明：子查询 select distinct dep_num from info where age >=25 会返回年龄≥25的员工所属部门编号（102、103），父查询通过IN关键字匹配这些编号，查询对应部门名称。

带EXISTS关键字的子查询（存在性判断）

简介

EXISTS关键字表示“存在”，使用时内层查询不返回具体记录，仅返回布尔值（True/False）：

• 若内层查询返回True（存在满足条件的记录），外层查询执行；
• 若内层查询返回False（不存在满足条件的记录），外层查询不执行，无结果返回。

示例

需求：判断指定部门是否存在，若存在则查询所有员工信息，若不存在则无结果。

情况1：内层查询返回True（部门存在）

mysql>  select * from info 
    -> where 
    -> exists 
    -> (select * from department where dep_num=102);
+----------+------+---------+-----------+
| name     | age  | dep_num | level_num |
+----------+------+---------+-----------+
| zhangsan |   23 |     101 |         1 |
| lisi     |   25 |     102 |         2 |
| wangwu   |   30 |     102 |         3 |
| zhaosi   |   30 |     103 |         4 |
| sunba    |   35 |    NULL |      NULL |
+----------+------+---------+-----------+
5 rows in set (0.00 sec)

说明：部门表中存在dep_num=102（tec部门），内层查询返回True，外层查询执行，返回所有员工信息。

情况2：内层查询返回False（部门不存在）

mysql> select * from info where exists (select * from department where dep_num=105);
Empty set (0.00 sec)

说明：部门表中不存在dep_num=105的部门，内层查询返回False，外层查询不执行，无结果返回。