# 语法-子查询

子查询（Subquery）是指嵌套在另一个SQL查询中的查询。它可以出现在SELECT、FROM、WHERE、HAVING或INSERT、UPDATE、DELETE等语句中。子查询的结果会被外层查询使用，通常用于解决复杂的查询需求。

# 子查询语法

可以使用括号()
WITH AS短语。WITH AS是查询的一种写法, 旧版MySQL 子查询不支持WITH AS写法，只支持括号写法。WITH AS写法可读性更高，更方便注释。MySQL从8.0开始支持，而SQL Server和PostgreSQL较早支持。

# 子查询的特点

嵌套结构：子查询是嵌套在另一个查询内部的查询。
独立执行：子查询可以独立执行，其结果会被外层查询使用。
返回结果：子查询可以返回单个值、单行或多行数据，具体取决于使用场景。
作用域：子查询可以访问外层查询的字段（称为相关子查询），也可以完全独立（称为非相关子查询）。

# 子查询的分类

按返回结果分类：

标量子查询：返回单个值（一行一列），通常用于SELECT、WHERE或HAVING中。
行子查询：返回单行数据（一行多列），通常用于WHERE或HAVING中。
表子查询：返回多行数据（多行多列），通常用于FROM或JOIN中。

按相关性分类：

非相关子查询：子查询可以独立执行，不依赖外层查询。
相关子查询：子查询依赖外层查询的字段，需要与外层查询结合执行。

# 在SELECT中使用

子查询返回一个标量值，作为外层查询的一个字段。

SELECT 
    student_name,
    (SELECT AVG(score) FROM score) AS avg_score
FROM student;

# 在WHERE中使用

子查询返回一个布尔值，作为外层查询的过滤条件。

SELECT * FROM student 
WHERE score > (SELECT AVG(score) FROM score);

# 在HAVING中使用

子查询返回一个标量值，作为外层查询的过滤条件。

SELECT student_name, AVG(score) 
FROM score 
GROUP BY student_name 
HAVING AVG(score) > (SELECT AVG(score) FROM score);

# 在FROM中使用

子查询返回一个表，作为外层查询的表。

SELECT * 
FROM (SELECT * FROM student) AS student_table;

# 在INSERT中使用

子查询返回一个表，作为INSERT语句的表。

INSERT INTO student (student_name, score) 
SELECT student_name, score FROM score;

# 在UPDATE中使用

子查询返回一个表，作为UPDATE语句的表。

UPDATE student SET score = (SELECT AVG(score) FROM score) 
WHERE student_name = '张三';

# 在DELETE中使用

子查询返回一个表，作为DELETE语句的表。

DELETE FROM student WHERE student_name = '张三';

# 非相关子查询

非相关子查询是指子查询可以独立执行，不依赖外层查询。

SELECT * FROM student 
WHERE score > (SELECT AVG(score) FROM score);

# 相关子查询

相关子查询是指子查询依赖外层查询的字段，需要与外层查询结合执行。

SELECT * FROM student 
WHERE score > (SELECT AVG(score) FROM score WHERE student.id = score.student_id);

# 子查询的执行顺序

子查询会独立执行，其结果会被外层查询使用。
子查询会先执行，然后外层查询才会执行。
子查询会先执行，然后外层查询才会执行。
子查询会先执行，然后外层查询才会执行。

# 子查询的优化

尽量使用非相关子查询，避免相关子查询。
尽量使用标量子查询，避免行子查询。
尽量使用表子查询，避免行子查询。
尽量使用IN，避免EXISTS。

# WITH AS

WITH AS（也称为公用表表达式，Common Table Expression，简称CTE）是SQL中用于定义临时结果集的一种语法。它可以将复杂的查询分解为多个逻辑部分，提高代码的可读性和可维护性。

# 基础语法

ITH cte_name AS (
    SELECT ...
    FROM ...
    WHERE ...
)
SELECT ...
FROM cte_name;

# 简化复杂查询

WITH high_scores AS (
    SELECT student_no, score
    FROM score
    WHERE score > 90
),
top_students AS (
    SELECT student_no
    FROM high_scores
    GROUP BY student_no
    HAVING COUNT(*) > 2
)
SELECT st.student_name
FROM student st
JOIN top_students ts ON st.student_no = ts.student_no;

先执行子查询high_scores，返回一个临时表。
再执行子查询top_students，返回一个临时表。
最后执行外层查询，返回结果。

# 递归查询

如果想使用WITH AS 递归查询，需要使用RECURSIVE关键字。

-- 使用递归查询，查询category表中所有分类的树状结构
WITH RECURSIVE category_tree AS (
    -- 查询category表中parent_id为空的分类
    SELECT id, name, parent_id
    FROM category
    WHERE parent_id IS NULL
    UNION ALL
    -- 查询category表中parent_id为上一级分类id的分类
    SELECT c.id, c.name, c.parent_id
    FROM category c
    JOIN category_tree ct ON c.parent_id = ct.id
)
-- 查询category_tree表中的所有数据
SELECT * FROM category_tree;

上面sql可以进行拆分

初始查询：

SELECT id, name, parent_id
FROM category
WHERE parent_id IS NULL

查询category表中parent_id为空的分类，即顶级分类。这是递归的起点。

递归查询：

SELECT c.id, c.name, c.parent_id
FROM category c
JOIN category_tree ct ON c.parent_id = ct.id

查询category表中parent_id为上一级分类id的分类，即子分类。这是递归的继续。每次递归都会基于上一级分类的id查找下一级分类。

终止条件

该sql存在隐式终止条件：当递归查询不再返回任何新行时，递归结束。如当JOIN category_tree ct ON c.parent_id = ct.id无法找到任何匹配的子分类时，递归查询返回空结果，递归终止。

显示的终止条件示例：下面的n<10 就是显示的终止条件。

WITH RECURSIVE number_sequence AS (
    SELECT 1 AS n  -- 初始查询：起点
    UNION ALL
    SELECT n + 1
    FROM number_sequence
    WHERE n < 10  -- 终止条件：达到10
)
SELECT * FROM number_sequence;

# 替代子查询

WITH avg_scores AS (
    SELECT course_no, AVG(score) AS avg_score
    FROM score
    GROUP BY course_no
)
SELECT sc.student_no, sc.score
FROM score sc
JOIN avg_scores a ON sc.course_no = a.course_no
WHERE sc.score > a.avg_score;

# 和临时表对比

WITH AS：

生命周期仅限于当前查询。
不需要显式创建和删除。
适合简单的临时结果集。

临时表：

生命周期可以跨多个查询。
需要显式创建和删除。
适合复杂或需要多次重用的结果集。

← 语法-函数语法-视图 →