多表查询

多表查询

项目开发中，不同业务模块的数据通常存放在不同的表中，表与表之间通过业务字段相互关联。多表查询用于从多张存在关联的表中获取数据。

多表关系

设计数据库表结构时，需要根据业务实体之间的联系确定表之间的关系。常见的多表关系分为一对多（多对一）、多对多和一对一。

一对多（多对一）

以部门与员工为例：一个部门可以对应多名员工，一名员工只对应一个部门。

这种关系通常在“多”的一方建立外键，使其指向“一”的一方的主键。例如，在员工表中设置 dept_id，关联部门表的 id。

多对多

以学生与课程为例：一名学生可以选择多门课程，一门课程也可以被多名学生选择。

这种关系需要建立第三张中间表。中间表至少包含两个外键，分别关联两张表的主键。例如，学生课程关系表中的 student_id 和 course_id 分别关联学生表与课程表。

一对一

以用户与用户详情为例：一名用户对应一份用户详情，一份用户详情也只属于一名用户。

一对一关系常用于垂直拆分表结构，例如将经常访问的基础字段与访问频率较低的详情字段分别存放。在任意一方加入外键，关联另一方的主键，并为该外键设置唯一约束（UNIQUE），即可限制每条记录最多对应另一方的一条记录。

多表查询概述

多表查询是指在一次查询中从多张表获取数据。

如果查询多张表时没有指定表之间的关联条件，数据库会将第一张表的每一行与第二张表的每一行逐一组合，这种组合结果称为笛卡尔积。假设表 A 有 m 行、表 B 有 n 行，笛卡尔积将产生 m × n 行结果。

笛卡尔积本身是一种组合方式，但其中通常包含不符合实际业务关系的组合。进行多表查询时，需要通过关联条件筛选出有效数据。

查询分类

多表查询可以分为三类：

• 连接查询：根据表之间的关联条件组合多张表的数据。
  • 内连接
  • 外连接
    • 左外连接
    • 右外连接
  • 自连接
• 联合查询
• 子查询

内连接

返回两张表中满足连接条件的数据。从表的关系范围理解，相当于查询两张表的交集部分。可以使用隐式内连接或显式内连接实现。

隐式内连接

隐式内连接在 FROM 后列出需要查询的表，并在 WHERE 中指定连接条件。

SELECT 字段列表
FROM 表1, 表2
WHERE 连接条件;

显式内连接

显式内连接使用 JOIN 连接表，并通过 ON 指定连接条件。其中 INNER 可以省略。

SELECT 字段列表
FROM 表1
[INNER] JOIN 表2 ON 连接条件;

外连接

外连接除了返回两张表中满足连接条件的数据，还会保留指定一侧表中的全部数据(包括该侧没有匹配项的记录)。

左外连接

左外连接返回左表的全部数据，以及两张表中满足连接条件的数据。

SELECT 字段列表
FROM 表1
LEFT [OUTER] JOIN 表2 ON 连接条件;

右外连接

右外连接返回右表的全部数据，以及两张表中满足连接条件的数据。

SELECT 字段列表
FROM 表1
RIGHT [OUTER] JOIN 表2 ON 连接条件;

语法中的 OUTER 可以省略。

自连接

自连接是指一张表与自身进行连接查询，用于查询一张表内部记录之间的关系。由于同一张表在查询中承担不同角色，必须为两次出现的表分别设置别名。

SELECT 字段列表
FROM 表A AS 别名A
JOIN 表A AS 别名B ON 连接条件;

自连接描述的是同一张表参与连接的形式，具体查询既可以使用内连接，也可以使用外连接。

联合查询

联合查询使用 UNION 或 UNION ALL 将多条 SELECT 语句的结果合并为一个新的结果集。

SELECT 字段列表 FROM 表A ...
UNION [ALL]
SELECT 字段列表 FROM 表B ...;

• UNION ALL：直接合并全部查询结果，保留重复记录。
• UNION：合并查询结果后去除重复记录。只有所选全部字段的值都相同，两行数据才会被视为重复。

使用联合查询时需要注意：

• 每条查询返回的列数必须相同。
• 相同位置的列应使用兼容的数据类型，避免因隐式类型转换产生意外结果。
• 最终结果的列名由第一条 SELECT 语句决定。

子查询

也叫嵌套查询，在一条 SQL 语句中嵌套 SELECT 查询，把查询结果作为SQL的数据源。包含子查询的语句称为外部查询，外部语句可以是 SELECT、INSERT、UPDATE 或 DELETE。

SELECT *
FROM 表1
WHERE 字段 = (SELECT 字段 FROM 表2 WHERE 条件);

子查询可以按照返回结果的形态分为标量子查询、列子查询、行子查询和表子查询，也可以按照出现位置分为 WHERE 子查询、FROM 子查询和 SELECT 子查询。

标量子查询

标量子查询返回一行一列，即一个值，例如数字、字符串或日期。需要单个值的表达式可以使用标量子查询；如果子查询返回多行，MySQL 会报错。

列子查询

列子查询返回一列数据，该列可以包含多行。

行子查询

行子查询返回一行数据，该行可以包含多列。

行值比较

使用 = 比较单个行值

WHERE (a, b, c, ...) = (1, 2, 3, ...) 使用的是行值比较语法。括号两侧分别构成一个行值，数据库按照位置逐项比较：a 与 1、b 与 2、c 与 3 对应。

WHERE (字段1, 字段2, 字段3) = (值1, 值2, 值3)

在参与比较的值都不为 NULL 时，上述写法可以理解为：

WHERE 字段1 = 值1
  AND 字段2 = 值2
  AND 字段3 = 值3

右侧也可以使用返回一行多列的子查询：

WHERE (字段1, 字段2) = (
    SELECT 字段A, 字段B
    FROM 表名
    WHERE 条件
)

比较时两侧的值数量必须相同，右侧子查询还必须至多返回一行；返回多行会导致错误。比较中出现 NULL 时仍遵循 SQL 的空值逻辑，结果可能为未知，从而无法通过 WHERE 筛选。

使用 IN 匹配多个行值

行值 IN 用于判断左侧行值是否与右侧多组行值中的任意一组完全匹配：

WHERE (字段1, 字段2) IN (
    (值1, 值2),
    (值3, 值4)
)

这里的“任意一组”是指右侧任意一个完整行值，每组内部仍然按照位置一一对应。以上条件可以理解为：

WHERE (字段1 = 值1 AND 字段2 = 值2)
   OR (字段1 = 值3 AND 字段2 = 值4)

不同元组中的值不能交叉组合。例如，右侧是 (1, 2) 和 (3, 4) 时，左侧 (1, 4) 不会匹配，虽然 1 和 4 分别出现在右侧的不同元组中。

右侧也可以使用返回多行多列的子查询：

WHERE (字段1, 字段2) IN (
    SELECT 字段A, 字段B
    FROM 表名
    WHERE 条件
)

子查询可以返回多行，左侧行值只要与其中任意一行按位置完全匹配，IN 条件就成立。左右两侧的列数仍须相同，对应位置的数据类型应能够比较。

表子查询

表子查询返回多行多列，即一张表，可以作为临时结果集参与外部查询。表子查询放在 FROM 后时也称为派生表，并且必须设置别名；返回的多列结果也可以配合行值 IN 在 WHERE 中进行匹配。

示例

数据准备

下面创建 dept 部门表、emp 员工表和 emp_archive 员工归档表。员工表中的 dept_id 用于关联部门表的 id；员工归档表用于演示如何联合不同数据源。

DROP TABLE IF EXISTS emp_archive;
DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;

CREATE TABLE dept (
    id INT PRIMARY KEY COMMENT '编号',
    name VARCHAR(50) NOT NULL COMMENT '部门名称'
) COMMENT = '部门表';

CREATE TABLE emp (
    id INT PRIMARY KEY COMMENT '编号',
    name VARCHAR(50) NOT NULL COMMENT '姓名',
    age TINYINT UNSIGNED COMMENT '年龄',
    job VARCHAR(20) COMMENT '职位',
    salary INT COMMENT '薪资',
    entrydate DATE COMMENT '入职时间',
    managerid INT COMMENT '直属领导编号',
    dept_id INT COMMENT '部门编号'
) COMMENT = '员工表';

CREATE TABLE emp_archive (
    id INT PRIMARY KEY COMMENT '编号',
    name VARCHAR(50) NOT NULL COMMENT '姓名',
    age TINYINT UNSIGNED COMMENT '年龄',
    salary INT COMMENT '薪资'
) COMMENT = '员工归档表';

INSERT INTO dept (id, name)
VALUES
    (1, '研发部'),
    (2, '市场部'),
    (3, '财务部'),
    (4, '销售部'),
    (5, '总经办'),
    (6, '人事部');

INSERT INTO emp (id, name, age, job, salary, entrydate, managerid, dept_id)
VALUES
    (1, '金庸', 66, '总裁', 20000, '2000-01-01', NULL, 5),
    (2, '张无忌', 20, '项目经理', 12500, '2005-12-05', 1, 1),
    (3, '杨逍', 33, '开发', 8400, '2000-11-03', 2, 1),
    (4, '韦一笑', 48, '开发', 11000, '2002-02-05', 2, 1),
    (5, '常遇春', 43, '开发', 10500, '2004-09-07', 3, 1),
    (6, '小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2, 1),
    (7, '灭绝', 60, '财务总监', 8500, '2002-09-12', 1, 3),
    (8, '周芷若', 19, '会计', 48000, '2006-06-02', 7, 3),
    (9, '丁敏君', 23, '出纳', 5250, '2009-05-13', 7, 3),
    (10, '赵敏', 20, '市场部总监', 12500, '2004-10-12', 1, 2),
    (11, '鹿杖客', 56, '职员', 3750, '2006-10-03', 10, 2),
    (12, '鹤笔翁', 19, '职员', 3750, '2007-05-09', 10, 2),
    (13, '方东白', 19, '职员', 5500, '2009-02-12', 10, 2),
    (14, '张三丰', 88, '销售总监', 14000, '2004-10-12', 1, 4),
    (15, '俞莲舟', 38, '销售', 4600, '2004-10-12', 14, 4),
    (16, '宋远桥', 40, '销售', 4600, '2004-10-12', 14, 4),
    (17, '陈友谅', 42, NULL, 2000, '2011-10-12', 1, NULL);

INSERT INTO emp_archive (id, name, age, salary)
VALUES
    (2, '张无忌', 20, 12500),
    (3, '杨逍', 33, 8400),
    (18, '谢逊', 52, 9000);

内连接

查询每一名员工的姓名及其关联的部门名称。由于内连接只返回满足连接条件的数据，因此没有关联部门的员工不会出现在结果中。

隐式内连接

SELECT e.name AS employee_name,
       d.name AS department_name
FROM emp AS e, dept AS d
WHERE e.dept_id = d.id;

显式内连接

SELECT e.name AS employee_name,
       d.name AS department_name
FROM emp AS e
INNER JOIN dept AS d ON e.dept_id = d.id;

两种写法的查询结果相同，都会返回员工与部门之间满足 e.dept_id = d.id 的数据。

外连接

左外连接

查询 emp 表的全部员工数据，以及每名员工对应的部门名称：

SELECT e.*,
       d.name AS department_name
FROM emp AS e
LEFT JOIN dept AS d ON e.dept_id = d.id;

emp 是左表，因此结果会保留全部员工，包括没有关联部门的陈友谅。

右外连接

查询 dept 表的全部部门数据，以及每个部门对应的员工信息：

SELECT d.id AS department_id,
       d.name AS department_name,
       e.*
FROM emp AS e
RIGHT JOIN dept AS d ON e.dept_id = d.id;

dept 是右表，因此结果会保留全部部门，包括没有关联员工的人事部。

自连接

emp.managerid 保存直属领导的员工编号，关联同一张 emp 表中的 id。下面分别使用别名 e 表示员工，使用别名 m 表示直属领导。

查询员工及其直属领导

使用内连接查询员工姓名及其直属领导姓名：

SELECT e.name AS employee_name,
       m.name AS manager_name
FROM emp AS e
INNER JOIN emp AS m ON e.managerid = m.id;

该查询只返回能够匹配到直属领导的员工。

查询全部员工及其直属领导

如果没有直属领导的员工也需要出现在结果中，可以将员工放在左表并使用左外连接：

SELECT e.name AS employee_name,
       m.name AS manager_name
FROM emp AS e
LEFT JOIN emp AS m ON e.managerid = m.id;

左外连接会保留全部员工，因此没有直属领导的金庸也会出现在结果中。

联合查询

下面合并 emp 当前员工表与 emp_archive 员工归档表。两个数据源返回相同的四列，并且对应列的数据类型兼容。

UNION ALL

查询当前和归档数据中的全部员工，并保留所有记录：

SELECT id, name, age, salary
FROM emp
UNION ALL
SELECT id, name, age, salary
FROM emp_archive;

emp 有 17 行，emp_archive 有 3 行，因此结果共有 20 行。张无忌和杨逍同时存在于当前表与归档表中，各自会出现两次。

UNION

合并当前和归档员工，并去除字段值完全相同的记录：

SELECT id, name, age, salary
FROM emp
UNION
SELECT id, name, age, salary
FROM emp_archive;

张无忌和杨逍在两个数据源中的四个查询字段完全相同，因此会被去重，最终结果共有 18 行。

子查询

标量子查询

查询“销售部”的所有员工信息：

SELECT *
FROM emp
WHERE dept_id = (
    SELECT id
    FROM dept
    WHERE name = '销售部'
);

子查询返回销售部的 id，外部查询再根据这个部门编号筛选员工。当前示例数据中的“销售部”只有一条记录，因此子查询返回一个值。

查询在“方东白”之后入职的员工信息：

SELECT *
FROM emp
WHERE entrydate > (
    SELECT entrydate
    FROM emp
    WHERE name = '方东白'
);

子查询返回方东白的入职日期 2009-02-12，外部查询返回入职日期晚于该日期的员工。

列子查询

查询“销售部”和“市场部”的所有员工信息：

SELECT *
FROM emp
WHERE dept_id IN (
    SELECT id
    FROM dept
    WHERE name IN ('销售部', '市场部')
);

子查询返回两个部门编号，IN 判断员工的 dept_id 是否位于这组结果中。

查询工资高于财务部所有员工的员工信息：

SELECT *
FROM emp
WHERE salary > ALL (
    SELECT e.salary
    FROM emp AS e
    INNER JOIN dept AS d ON e.dept_id = d.id
    WHERE d.name = '财务部'
);

> ALL 要求员工工资高于子查询返回的每一个工资。当前数据中财务部的最高工资为 48000，没有员工的工资高于该值，因此查询结果为空。

查询工资高于研发部任意一名员工的员工信息：

SELECT *
FROM emp
WHERE salary > ANY (
    SELECT e.salary
    FROM emp AS e
    INNER JOIN dept AS d ON e.dept_id = d.id
    WHERE d.name = '研发部'
);

> ANY 表示只要高于子查询结果中的任意一个工资即可。当前数据中研发部的最低工资为 6600，因此该条件等价于工资高于 6600。

行子查询

查询与“张无忌”的工资及直属领导都相同的员工信息：

SELECT *
FROM emp
WHERE (salary, managerid) = (
    SELECT salary, managerid
    FROM emp
    WHERE name = '张无忌'
);

左侧 (salary, managerid) 和子查询返回的一行两列按照位置比较，即工资对应工资、直属领导编号对应直属领导编号。当前数据中张无忌和赵敏的工资都是 12500，直属领导编号都是 1，因此两人都会被返回。

表子查询

查询与“鹿杖客”或“宋远桥”的职位及工资相同的员工信息：

SELECT *
FROM emp
WHERE (job, salary) IN (
    SELECT job, salary
    FROM emp
    WHERE name IN ('鹿杖客', '宋远桥')
);

子查询返回多行两列，外部查询使用 (job, salary) IN (...) 按整组值匹配。鹿杖客对应“职员、3750”，宋远桥对应“销售、4600”，因此具有这两组职位和工资组合的员工都会被返回。

查询入职日期在 2006-01-01 之后的员工信息及其部门信息：

SELECT e.*,
       d.name AS department_name
FROM (
    SELECT *
    FROM emp
    WHERE entrydate > '2006-01-01'
) AS e
LEFT JOIN dept AS d ON e.dept_id = d.id;

FROM 中的子查询先生成满足入职日期条件的临时结果集，再由外部查询关联部门表。派生表使用别名 e；左外连接可以保留其中没有关联部门的员工。