MySQL DQL 子查询

恋喵大鲤鱼

发布于 2024-05-24 14:40:53

830

发布于 2024-05-24 14:40:53

文章被收录于专栏：C/C++基础

1.简介

子查询是另一个语句中的 SELECT 语句。

子查询也称为内查询（Inner Query），必须位于括号之中。包含子查询的查询称为外查询（Outer Query）。子查询支持多层嵌套，也就是子查询可以包含其他子查询。

子查询的外部语句可以是以下任一语句：SELECT、INSERT、UPDATE、DELETE、SET 或 DO。

下面是一个示例。

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

在这个例子中，SELECT * FROM t1 ...是外查询，位于括号中的SELECT column1 FROM t2是子查询。

2.优势

使用子查询的主要优势有：

它们允许结构化查询，以便可以隔离语句的每个部分。
它们提供了需要复杂连接和并集的操作的替代方法。
许多人发现子查询比复杂的连接或联合更具可读性。事实上，正是子查询的创新让人们产生了将早期 SQL 称为“结构化查询语言”的最初想法。

3.分类

根据子查询的结果可以将其分为多种类型。

标量子查询（Scalar Subquery）：返回单个值（一行一列）的子查询。
行子查询（Row Subquery）：返回单行结果（一行多列）的子查询。
列子查询（Column Subquery）：返回单列结果（一列多行）的子查询。
表子查询（Table Subquery）：返回一个虚拟表（多行多列）的子查询。

3.1 标量子查询

标量子查询的结果就像一个常量一样，可以用于 SELECT、WHERE、GROUP BY、HAVING 以及 ORDER BY 等子句中。

例如以下语句返回了月薪大于平均月薪的员工：

SELECT name, salary
FROM employee
WHERE salary > (
	SELECT AVG(salary)
	FROM employee
);

+--------+---------+
| name   | salary  |
+--------+---------+
| 刘备   | 1000000 |
| 曹操   | 2000000 |
| 孙权   | 1500000 |
+--------+---------+

其中，括号内部的子查询用于获得员工的平均月薪，外查询用于返回月薪大于平均月薪的员工信息。

3.2 行子查询

行子查询可以当作一个一行多列的临时表使用。

以下语句查找所有与“关羽”在同一个部门并且职级相同的员工：

SELECT name, dept_id, job_level
FROM employee
WHERE (dept_id, job_level) = (SELECT dept_id, job_level
                           FROM employee
                           WHERE name = '关羽')
AND name != '关羽';

+--------+---------+-----------+
| name   | dept_id | job_level |
+--------+---------+-----------+
| 张飞   |       1 |         2 |
+--------+---------+-----------+

3.3 列子查询

列子查询可以当作一个一列多行的临时表使用。

当 WHERE 条件中的子查询返回多行数据时，不能再使用普通的比较运算符，因为它们不支持单个值和多个值的比较；如果想要判断某个字段是否在子查询返回的数据列表中，可以将子查询与 IN、ALL、ANY/SOME 操作符配合使用。

operand IN (subquery)
operand comparison_operator ALL (subquery)
operand comparison_operator ANY (subquery)
operand comparison_operator SOME (subquery)

其中 comparison_operator 是下面运算符之一：

=  >  <  >=  <=  <>  !=

IN 操作符

IN 操作符表示表达式是否在子查询的结果列中，如果在返回 TRUE。

SELECT s1 FROM t1 WHERE s1 IN (SELECT s1 FROM t2);

比如查找“刘备”和“孙权”所在部门的员工：

SELECT name, dept_id
FROM employee
WHERE dept_id IN (
	SELECT dept_id
	FROM employee
	WHERE name IN ('刘备','孙权')
)
AND name NOT IN ('刘备','孙权');

+-----------+---------+
| name      | dept_id |
+-----------+---------+
| 关羽      |       1 |
| 张飞      |       1 |
| 黄月英    |       1 |
| 吕蒙      |       3 |
| 黄盖      |       3 |
+-----------+---------+

NOT IN 操作符执行和 IN 相反的操作，也就是当表达式在列子查询结果中时为 TRUE。

ALL 操作符

除了 IN 运算符之外，ALL、ANY/SOME 运算符与比较运算符的结合也可以用于判断子查询的返回结果。

ALL 必须跟在比较运算符之后，如果表达式与子查询返回列中的所有值的比较结果为 TRUE，则返回 TRUE。

SELECT s1 FROM t1 WHERE s1 > ALL (SELECT s1 FROM t2);

对于 ALL 操作符，有两个需要注意的情况，就是子查询结果为空或者存在 NULL 值。

SELECT name, salary
FROM employee
WHERE salary > ALL (SELECT 999999 FROM anonymity WHERE 1=0);

以上查询会返回所有员工，因为子查询返回结果为空集，外查询相当于没有 WHERE 条件。

SELECT name, salary
FROM employee
WHERE salary > ALL (SELECT MAX(999999) FROM anonymity WHERE 1=0);

以上查询会返回返回空集，因为子查询返回 NULL，任何数值和 NULL 比较的结果都是未知（unknown）。

NOT IN 是 <> ALL 的别名。因此，这两个语句是相同的：

SELECT s1 FROM t1 WHERE s1 <> ALL (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 NOT IN (SELECT s1 FROM t2);

ANY/SOME 操作符

SOME 是 ANY 的别名，所以 SOME 等同于 ANY。

ANY 关键字必须跟在比较运算符之后，如果表达式与子查询返回列中的任何值的比较结果为 TRUE，则返回 TRUE。

SELECT s1 FROM t1 WHERE s1 > ANY (SELECT s1 FROM t2);

= ANY 和 IN 操作符等价。

3.4 表子查询

当子查询返回的结果包含多行多列数据时，称为表子查询。

表子查询通常用于 FROM 子句或者查询条件中。

当子查询出现在 FROM 子句中时，相当于创建了一个语句级别的派生表（Derived Table）。

SELECT ... FROM (subquery) [AS] tbl_name ...

[AS] tbl_name 子句是强制性的，因为 FROM 子句中的每个表都必须有一个名称。派生表中的任何列都必须具有唯一名称。tbl_name 后面可以跟一个带括号的派生表列名称列表。

SELECT ... FROM (subquery) [AS] tbl_name (col_list) ...

列名数量必须与列数量相同。

此外，JSON_TABLE() 函数会生成一个表，提供了另一种创建派生表的方法：

SELECT * FROM JSON_TABLE(arg_list) [AS] tbl_name ...

4.关联子查询

在上面的示例中，子查询和外查询之间没有联系，可以单独运行。这种子查询也称为非关联子查询（Non-correlated Subquery）。

另一类子查询会引用外查询中的字段，从而与外部查询产生关联，也称为关联子查询（Correlated Subquery）。

例如以下示例通过使用关联子查询获得各个部门的员工数量：

SELECT d.name AS "部门名称",
       (SELECT count(*)
        FROM employee
        WHERE dept_id = d.id) as "员工数量"
FROM department d;

+--------------+--------------+
| 部门名称     | 员工数量     |
+--------------+--------------+
| 蜀汉部       |            4 |
| 曹魏部       |            3 |
| 孙吴部       |            3 |
+--------------+--------------+

其中，子查询的 WHERE 条件中使用了外查询的部门编号（d.id），从而与外查询产生关联。该语句执行时，外查询先检索出所有的部门数据，针对每条记录再将 d.id 传递给子查询，子查询返回每个部门的员工数量。

5.EXISTS 和 NOT EXISTS

如果子查询返回任何行，则 EXISTS 子查询为 TRUE，NOT EXISTS 子查询为 FALSE。

SELECT column1 FROM t1 WHERE EXISTS (SELECT * FROM t2);

传统上，EXISTS 子查询以 SELECT * 开头，但它也可以以 SELECT 5 或 SELECT column1 或任何其他内容开头。 MySQL 会忽略此类子查询中的 SELECT 列表，因此没有区别。

对于前面的示例，如果 t2 包含任何行，甚至只包含 NULL 值的行，则 EXISTS 条件为 TRUE。这实际上是一个不太可能的例子，因为 [NOT] EXISTS 子查询几乎总是包含相关性。

下面看一个更加具体的例子。比如返回了存在女性员工的部门：

SELECT d.name
FROM department d
WHERE EXISTS (SELECT *
               FROM employee e
               WHERE e.gender = '女'
               AND e.dept_id = d.id
             );

+-----------+
| name      |
+-----------+
| 蜀汉部    |
+-----------+

其中，EXISTS 之后是一个关联子查询，先执行外查询找到 d.dept_id；然后依次将 d.dept_id 传递给子查询，判断该部门是否存在女性员工，如果存在则返回部门信息。

NOT EXISTS 执行相反的操作。如果想要查找不存在女性员工的部门，可以将上例中的 EXISTS 替换成 NOT EXISTS。

6.横向派生表

对于派生表而言，它必须能够单独运行，而不能依赖其他表。

例如，以下语句想要返回每个部门内月薪最高的员工：

SELECT d.name, t.name, t.salary
FROM department d
LEFT JOIN (SELECT e.dept_id, e.name, e.salary
           FROM employee e
           WHERE e.dept_id = d.id
           ORDER BY e.salary DESC
           LIMIT 1
          ) t ON d.id = t.dept_id;
ERROR 1054 (42S22): Unknown column 'd.id' in 'where clause'

该语句失败的原因在于子查询 t 不能引用外查询中的 department 表。

从 MySQL 8.0.14 开始，派生表支持 LATERAL 关键字前缀，表示允许派生表引用它所在的 FROM 子句中的其他表。这种派生表被称为横向派生表（Lateral Derived Table）。

对于上面的问题，可以使用 LATERAL 派生表实现：

SELECT d.name, t.name, t.salary
FROM department d
LEFT JOIN LATERAL (SELECT e.dept_id, e.name, e.salary
           FROM employee e
           WHERE e.dept_id = d.id
           ORDER BY e.salary DESC
           LIMIT 1
          ) t on d.id = t.dept_id;

该语句在 LEFT JOIN 之后加上了一个 LATERAL 关键字，使得子查询 t 能够引用前面的 department 表中的字段。

如果你使用的是 MySQL 5.7 以及之前的版本，可以利用 MySQL 中的自定义变量实现相同的效果：

SELECT d.name dept_name, w.name emp_name, w.salary
FROM department d
LEFT JOIN (
	SELECT *
  	FROM (
    	SELECT a.*, IF(@did = a.dept_id, @rn := @rn+1, @rn := 1) AS rn, @did := a.dept_id
    	FROM
    	(SELECT * FROM employee e ORDER BY dept_id, salary DESC) a
  	) AS t
  	WHERE t.rn <= 1
) AS w ON d.id = w.dept_id;

+-----------+----------+---------+
| dept_name | emp_name | salary  |
+-----------+----------+---------+
| 蜀汉部    | 刘备     | 1000000 |
| 曹魏部    | 曹操     | 2000000 |
| 孙吴部    | 孙权     | 1500000 |
+-----------+----------+---------+

上面的查询语句使用了自定义变量，有几处需要特别解释一下。

语句IF(@did = a.dept_id, @rn := @rn+1, @rn := 1) AS rn这是一个 IF 语句，用于计算排名。它检查当前行的部门 ID (a.dept_id) 是否与前一行的部门 ID (@did) 相同。如果相同，则排名 (@rn) 自增 1，表示同一个部门内的下一个员工。如果部门 ID 不同（即进入了新的部门），则排名 (@rn) 被重置为 1，表示这是新部门的第一个员工。AS rn 表示将计算出的排名别名为 rn，它将作为结果集的一部分返回。

@did := a.dept_id将当前行的部门 ID (a.dept_id) 赋值给用户变量 @did。该变量用在前面的 IF 语句中，用于给部门内的员工计算排名。

再给每个部门员工按照工资排序并编上部门内部排名 rn 后，将结果作为派生表 t，通过 SELECT 查询出所有部门内薪资排名第一的员工。

最后和部门表连表查询出每个部门内月薪最高的员工。

7.附录

本文示例用到的员工表（employee）和部门表（deparment）建表与数据如下。

员工表（employee）：

CREATE TABLE employee(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name varchar(64) NOT NULL,
  dept_id INT UNSIGNED,
  job_level INT UNSIGNED,
  salary INT UNSIGNED,
  gender CHAR(1) DEFAULT '男',
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

INSERT INTO employee(name,dept_id,job_level,salary,gender)
VALUES
('刘备', 1,1, 1000000, '男'),
('关羽', 1,2, 100000, '男'),
('张飞', 1,2, 100000, '男'),
('黄月英',1,3, 80000, '女'),
('曹操', 2,1, 2000000, '男'),
('典韦', 2,2, 200000, '男'),
('张辽', 2,2, 200000, '男'),
('孙权', 3,1, 1500000, '男'),
('吕蒙', 3,2, 150000, '男'),
('黄盖', 3,2, 150000, '男')

部门表（deparment）：

CREATE TABLE department(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  name varchar(64) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

INSERT INTO department(name)
VALUES
('蜀汉部'),
('曹魏部'),
('孙吴部')