MySQL 相关子查询以及横向派生表

1. 相关子查询

1.1. 什么是相关子查询

子查询是嵌套在外部查询中的查询。相关子查询使用外部查询的值。每当外部查询处理一行数据时，就针对该行数据执行一次相关子查询，因此其结果依赖外部查询中正在处理的行。

1.2. 为什么使用相关子查询

相关子查询可以解决 GROUP BY 的一些限制 - 只能查询分组字段和聚合字段。但是有时这些字段无法满足需求，所以需要用相关子查询解决这些限制。

1.3. 示例

员工表如下：

mysql> show create table Employees\G
*************************** 1. row ***************************
       Table: Employees
Create Table: CREATE TABLE `Employees` (
  `EmployeeID` int DEFAULT NULL,
  `Department` varchar(50) DEFAULT NULL,
  `Salary` decimal(10,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> select * from Employees;
+------------+-------------+-----------+
| EmployeeID | Department  | Salary    |
+------------+-------------+-----------+
|          1 | Sales       |  60000.00 |
|          2 | Engineering |  70000.00 |
|          3 | IT          |  80000.00 |
|          4 | IT          |  90000.00 |
|          5 | IT          |  40000.00 |
|          6 | Engineering | 100000.00 |
|          7 | Sales       |  20000.00 |
+------------+-------------+-----------+
7 rows in set (0.00 sec)

求：每个部门工资最高的员工及其工资。

如果只求 Department 和 MaxSalary，那么只需按部门进行分组，计算 MAX(Salary) 即可。但是现在除这两个字段外还需要 EmployeeID 字段，该字段不是分组字段，所以无法使用 GROUP BY 直接查询，此时需要使用相关子查询。

SELECT Department, EmployeeID, Salary
FROM Employees e
WHERE Salary IN (SELECT MAX(Salary)
                 FROM Employees
                 WHERE Department = e.Department);

对于该需求而言，除使用相关子查询外，还可以使用如下两个 SQL 实现：

SELECT Department, EmployeeID, MAX(Salary)
FROM (SELECT e1.Department, e1.EmployeeID, e1.Salary, e2.EmployeeID as e2_employee_id
      FROM Employees e1
               LEFT JOIN Employees e2 ON e1.Department = e2.Department AND e1.Salary < e2.Salary) t
GROUP BY Department, EmployeeID
HAVING COUNT(e2_employee_id) < 1;

SELECT Department, EmployeeID, Salary
FROM (SELECT Department, EmployeeID, Salary, RANK() OVER ( PARTITION BY Department ORDER BY Salary DESC ) AS nth
      FROM Employees) t
WHERE nth <= 1;

1.4. 总结

对于外层查询的每一行结果，相关子查询都将执行一次。因此相关子查询可能导致性能问题。
子查询可以使用外部查询中定义的任何字段，但是这些字段必须在被子查询引用时在外部查询中必须可见或者有效。

2. 横向派生表

2.1. 介绍

在 MySQL 中，派生表通常不能在同一个 FROM 子句中引用（依赖）前面的表中的列。在 MySQL 8.0.14 中，派生表可被定义为横向（LATERAL）派生表，以允许这种引用。其实就是通过关键字 LATERAL 在子查询的结果集中引用外层表达式的列。这种使用方式有助于简化查询语句，提高查询效率。

横向派生表受以下限制：

横向派生表只能出现在 FROM 子句中，可以出现在逗号分隔的表列表中，也可以出现在联接规范（JOIN、INNER JOIN、CROSS JOIN、LEFT [OUTER] JOIN 或 RIGHT [OUTER] JOIN）中。
如果横向派生表位于联接子句的右操作数中，并且包含对左操作数的引用，则联接操作必须是 INNER JOIN、CROSS JOIN 或 LEFT [OUTER] JOIN。
如果表在左操作数中并且包含对右操作数的引用，则联接操作必须是 INNER JOIN、CROSS JOIN 或 RIGHT [OUTER] JOIN。
横向派生表内部不能使用聚合函数直接引用外部查询结果。
根据 SQL 标准，MySQL 总是将带有表函数（如 JSON_TABLE()）的联接视为使用 LATERAL。

2.2. 示例：使用横向派生表实现 1.3

SELECT Department, EmployeeID, Salary, e2.MaxSalary
FROM Employees e1
         INNER JOIN LATERAL (
    SELECT MAX(e3.Salary) AS MaxSalary FROM Employees e3 WHERE e3.Department = e1.Department
    ) e2 ON e1.Salary = e2.MaxSalary;

2.3. 并非所有场景都适合使用横向派生表

因为是子查询，所以必然生成中间表，因此如非必要不要使用横向派生表。