子查询返回一个表

最后，第三种选择是当子查询返回整个表时。这是最常见的选项。

很多时候我们想要稍微调整某个表的情况。然后才将更正后的表与另一个表连接（使用 JOIN ON 运算符）。

让我们从最简单的情况开始，我们用 JOIN 连接两个表：

SELECT * FROM employee e JOIN task t ON e.id = t.emploee_id

您可能还记得，任务表中有一些任务没有分配给任何人：employee_id是NULL。

让我们生成一个更正后的表，我们将所有挂起的任务分配给主管（他的 ID = 4）。

为此，我们使用IFNULL()函数：

SELECT id, IFNULL(employee_id, 4) AS employee_id, name, deadline FROM task

这个查询的结果是：

ID 员工ID 姓名 最后期限
1个 1个 修复一个前端bug 2022-06-01
2个 2个 修复后端的一个bug 2022-06-15
3个 5个 买咖啡 2022-07-01
4个 5个 买咖啡 2022-08-01
5个 5个 买咖啡 2022-09-01
6个 4个 打扫办公室 （无效的）
7 4个 享受生活 （无效的）
8个 6个 享受生活 （无效的）
Java webinar

更正后的单元格标记为红色。

现在让我们将更正后的表替换到查询中：

SELECT * FROM employee e JOIN task t ON e.id = t.emploee_id

而不是任务表。

这样的请求看起来像这样：

SELECT * FROM employee e JOIN ( 
 	SELECT id, IFNULL(employee_id, 4) AS employee_id, name, deadline
 	FROM task 
) t ON e.id = t.emploee_id

我们没有使用单词task ，而是写了括号并将请求正文放在其中。

顺便说一句，嵌套查询的别名 t（别名）非常有用。嵌套查询与表不同，它没有自己的名称，因此别名非常不合适。

这是这样一个查询的结果：

ID 姓名 职业 薪水 年龄 加入日期 ID 员工ID 姓名
1个 伊万诺夫伊万 程序员 100000 25 2012-06-30 1个 1个 修复一个前端bug
2个 彼得罗夫彼得 程序员 80000 23 2013-08-12 2个 2个 修复后端的一个bug
4个 拉比诺维奇·莫伊沙 导演 200000 35 2015-05-12 6个 4个 打扫办公室
4个 拉比诺维奇·莫伊沙 导演 200000 35 2015-05-12 7 4个 享受生活
5个 基连科阿纳斯塔西娅 办公室主管 40000 25 2015-10-10 4个 5个 买咖啡
5个 基连科阿纳斯塔西娅 办公室主管 40000 25 2015-10-10 5个 5个 买咖啡
5个 基连科阿纳斯塔西娅 办公室主管 40000 25 2015-10-10 3个 5个 买咖啡
6个 瓦斯卡 猫 1000 3个 2018-11-11 8个 6个 享受生活

我们的主管有“打扫办公室”的任务，我想他会很快找人委托给它的：）使用WITH语句

顺便说一下，从 MySQL 版本 8 开始，您不再需要将所有子查询都放在最终查询中。它们可以单独执行。为此，使用WITH 语句。

它允许您创建一个虚拟表（命名查询），它的外观由一个模板给出：

WITH Name AS (request)

很多时候，您的子查询具有未命名的列，例如 COUNT(*)，您没有为其分配唯一的名称。在这种情况下，WITH 语句可以选择为子查询指定新的列名。

它的第二种形式由模板给出：

WITH Name(column1, column2, …) AS (request)

您可以根据需要使用任意数量的虚拟表（命名查询）并在其中相互引用。您的请求的一般形式将是这样的：

WITH name1 AS (request1),  
       	name2 AS (request2), 
       	name3 AS (request3) 
SELECT * FROM name1 JOIN name2 ON …

现在让我们进行可怕的查询：

SELECT * FROM employee e JOIN ( 
 	SELECT id, IFNULL(employee_id, 4) AS employee_id, name, deadline 
 	FROM task 
) t ON e.id = t.emploee_id

并使用 WITH 语句重写它：

WITH task2(id, employee_id, name, deadline) 
   AS (SELECT id, IFNULL(employee_id, 4), name, deadline FROM task) 
SELECT * FROM employee e JOIN task2 t ON e.id = t.emploee_id

或者您可以不使用列名，但是您必须为 IFNULL() 函数指定一个别名：

WITH task2 AS ( 
 	SELECT id, IFNULL(employee_id, 4) AS employee_id, name, deadline FROM task 
) 
SELECT * FROM employee e JOIN task2 t ON e.id = t.emploee_id