MySQL教程 - 12 多表查询

继续讲解多表查询...

12.8 子查询

子查询 也叫 嵌套查询，它指的是一个 SQL 查询语句嵌套在另一个查询语句中，用于为外层查询提供数据。通常用来处理复杂查询，特别是涉及多个表或需要多步筛选的场景。

语法：

SELECT 字段列表
FROM 表名1
WHERE column1 [操作符] (
    SELECT column1
    FROM 表名2
    WHERE condition
);

• 上面的语句分为外部查询和子查询，上面外部的查询是 SELECT，但其实可以是 INSERT、DELETE 或 SELECT 。

• 上面的语法中，子查询在 WHERE 子句中，其实子查询可以出现在 SELECT、FROM 或 WHERE 子句中。

• 子查询可以单独执行，并为外层查询提供中间结果。

根据使用场景和嵌套位置，子查询分为以下几种类型：

• 标量子查询（子查询结果为单行单列，就是单个值）
• 列子查询（子查询结果为单列，可为多行）
• 行子查询（子查询结果为单行，可为多列）
• 表子查询（子查询结果为多行多列）

下面分别介绍一下。

1 标量子查询

标量子查询，子查询结果为单行单列的单个值。常用的操作符有： = 、> 、< 、>= 、<= 、!= 。

举个栗子：

查询技术部的所有员工信息，在前面已经使用内连接查询实现过，下面使用子查询实现。

查询可以分为两个步骤，先根据名称查询出部门ID，然后根据部门ID查询员工，如下：

-- 步骤1：首先查询出技术部的部门ID
SELECT id FROM tb_department WHERE name = '技术部';

-- 步骤2：根据部门ID查询员工
SELECT * FROM tb_employee WHERE dept_id = '2';

合并起来，使用一条子查询语句实现：

SELECT * FROM tb_employee WHERE dept_id = (
  SELECT id FROM tb_department WHERE name = '技术部'
);

执行结果：

再举个例子：

查询所有大于平均工资的员工信息：

-- 步骤1：查询出平均工资
SELECT AVG(salary) FROM tb_employee;

-- 步骤2：查询大于平均工资的员工信息
SELECT * FROM tb_employee WHERE salary > 13943.150000;

将两条查询语句合并成一条子查询：

SELECT * FROM tb_employee WHERE salary > (SELECT AVG(salary) FROM tb_employee);

2 列子查询

列子查询，子查询结果为一列，可以为多行。常用的操作符有：IN 、NOT IN 、ANY 、SOME 、ALL 。

操作符使用场景对比：

操作符	含义	示例	子查询结果	条件
IN	等于子查询返回的任意一个值	column_name IN (...)	多行单列	任意匹配
NOT IN	不等于子查询返回的任意一个值	column_name NOT IN (...)	多行单列	全部不匹配
ANY	与子查询返回的任意一个值比较	column_name > ANY (...)	多行单列	至少一个值满足
SOME	等价于 ANY	column_name > SOME (...)	多行单列	至少一个值满足
ALL	与子查询返回的所有值逐一比较	column_name > ALL (...)	多行单列	所有值都需满足

举个栗子：

查询 技术部 和 市场部 的所有员工信息。

查询可以分为两步实现：

-- 步骤1：查询技术部和市场部的部门ID
SELECT id FROM tb_department WHERE name = '技术部' OR name = '市场部';

查询出的结果，部门ID是一列，多行的。

-- 步骤2：根据部门ID是技术部或市场部的员工信息
SELECT * FROM tb_employee WHERE dept_id in (1, 2);

将上面的两个步骤合并为一个子查询实现，使用 IN 操作符：

SELECT * FROM tb_employee WHERE dept_id in (
  SELECT id FROM tb_department WHERE name = '技术部' OR name = '市场部'
);

再举个栗子：

查询比 行政部 所有员工工资都高的员工信息，可以分为一下几个步骤：

-- 步骤1：查询出行政部部门ID
SELECT id FROM tb_department WHERE name = '行政部';

-- 步骤2：查询出行政部所有的员工工资
SELECT salary FROM tb_employee WHERE dept_id = (SELECT id FROM tb_department WHERE name = '行政部');

-- 步骤3：查询比行政部所有工资都高的员工信息
SELECT * FROM tb_employee WHERE salary > ALL (这里需要子查询);

将上面几个步骤使用子查询实现：

SELECT * FROM tb_employee WHERE salary > ALL (
  SELECT salary FROM tb_employee WHERE dept_id = (SELECT id FROM tb_department WHERE name = '行政部')
);

• 上面使用了 salary > ALL(...) 表示 salary 要大于 子查询的所有结果。如果使用的是 ANY 表示满足子查询结果中的任意一个结果，那么就是大于任意一个结果。

实现上面的查询有很多方式，例如可以先查询出技术部员工的最高工资，这里只是演示一种。

3 行子查询

列子查询，子查询结果为一行，可以为多列。常用的操作符有：= 、!= 、 IN 、NOT IN 。

举个栗子：

查询性别和工资与员工 叶文杰 完全相同的员工。

查询可以分为两个步骤：

-- 步骤1：查询出叶文杰的性别和工资
SELECT gender, salary FROM tb_employee WHERE name = '叶文杰';

查询出的结果是一行多列，包括性别和工资的：

-- 步骤2：根据性别和年龄查询员工
SELECT * FROM tb_employee WHERE gender = '女' AND salary = 15000;

-- 上面的写法无法使用子查询，可以使用如下形式
SELECT * FROM tb_employee WHERE (gender, salary) = ('女', 15000);

将上面实现的两个步骤使用行子查询来实现：

SELECT * FROM tb_employee WHERE (gender, salary) = (SELECT gender, salary FROM tb_employee WHERE name = '叶文杰');

• 上面使用的操作符是 = ，那么两个条件都要等于，如果是 > ，那么两个条件都要大于对应的值，!= 或 <>：表示任意一个条件不相等，则符合条件。

查询结果如下：

4 表子查询

列子查询，子查询结果为多行多列，其实就是一张表的数据。常用的操作符是 ：IN 。

举个栗子：

查询性别和工资与员工 叶文杰 或 罗辑 完全相同的员工。

查询可以分为两个步骤：

-- 步骤1：查询出叶文杰和罗辑的性别和工资
SELECT gender, salary FROM tb_employee WHERE name = '叶文杰' OR name = '罗辑';

查询出的结果是多行多列的，包括性别和工资的：

-- 步骤2：根据性别和年龄查询员工
SELECT * FROM tb_employee WHERE (gender, salary) IN (SELECT gender, salary FROM tb_employee WHERE name = '叶文杰' OR name = '罗辑');

• 上面使用了 IN 查询，相当于满足 (gender, salary) = (男, 12000.5) OR (gender, salary) = (女, 15000) 即可。

执行结果：

再举个例子：

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

-- 步骤1：查询出入职日期在2023-01-01之后的员工
SELECT * FROM tb_employee WHERE entry_time > '2023-01-01';

查询出的结果是多行多列的表，可以将这个结果作为一张表和其他的表进行连接查询。

SELECT e.*, d.* FROM (SELECT * FROM tb_employee WHERE entry_time > '2023-01-01') as e 
LEFT JOIN tb_department as d
on e.dept_id = d.id;

• 可以看到可以将子查询放在 FROM 后面，作为一张表进行使用。

当然，查询方式有很多，例如可以直接使用连接查询，通过查询条件实现：

SELECT * FROM tb_employee as e LEFT JOIN tb_department as d ON e.dept_id = d.id 
WHERE e.entry_time > '2023-01-01';