结构化查询语言(Structured Query Language)

子查询 (Subquery)

子查询是一个完整的 SELECT 命令，用于另一个 SELECT、UPDATE、INSERT 或 DELETE 命令中。与普通的 SELECT 不同，子查询被括号 () 包围。

---

分类

根据子查询返回的结果类型，可分为三类：

1. 标量值子查询 (Scalar Value Subquery)
   子查询返回一个单一值，例如：
   (SELECT max(weight) FROM person)。

2. 行子查询 (Row Subquery)
   子查询返回一行或多列值，例如：
   (SELECT min(weight), max(weight) FROM person)。

3. 表子查询 (Table Subquery)
   子查询返回多行，形成一个表，例如：
   (SELECT lastname, weight FROM person)。
   表子查询可以返回 0 行、1 行或多行，与行子查询的区别在于表子查询可能返回多行。

子查询可用在与其类型相匹配的位置，例如标量值子查询可用在需要单个值的位置。表子查询还能用作 EXISTS、IN、SOME、ANY 或 ALL 谓词的参数。

此外，子查询还可分为以下两类：

• 相关子查询 (Correlated Subquery)：子查询中使用了外层查询的值，且每次外层查询的行变化时，子查询都会执行。
• 非相关子查询 (Non-Correlated Subquery)：子查询独立于外层查询，仅执行一次。

由于相关子查询每行都会执行一次，性能可能较差，但它是一种常用构造方式。很多情况下，可以用 JOIN 替代相关子查询，性能表现因数据库管理系统 (DBMS)、索引存在与否以及表数据量等因素而异。

---

标量值子查询

示例 1：计算全局平均值

SELECT id,
       lastname,
       weight,
       (SELECT avg(weight) FROM person) AS avg_weight
FROM   person
ORDER BY lastname;

解释：

• 子查询使用了 avg() 函数，返回单个值，因此是标量值子查询。
• 它可以在需要单一值的位置使用，例如 SELECT 列表中。

示例 2：在 WHERE 子句中使用子查询

-- 查找体重不低于全体平均体重的人员
SELECT id, lastname, weight
FROM   person
WHERE  weight >= (SELECT avg(weight) FROM person)
ORDER BY lastname;

以上两个示例中的子查询是非相关子查询，它们独立于外层查询，仅执行一次。

---

相关子查询

当子查询需要使用外层查询中的值时，称为相关子查询。例如：

-- 列出每位人员及其家庭的平均体重
SELECT id, firstname, lastname, weight,
       (SELECT avg(weight)
        FROM   person sq
        WHERE  sq.lastname = p.lastname
       ) AS family_average
FROM   person p
ORDER BY lastname, weight;

解释：

• 子查询通过别名 sq 引用表 person，外层查询则用别名 p。
• 子查询根据 p.lastname 查找同一姓氏的所有人员，并计算家庭平均体重。
• 性能警告：由于相关子查询对外层查询的每一行执行一次，性能较差。如果可能，使用 JOIN 或 GROUP BY 重写以优化性能。

---

行子查询

子查询返回一行或多列值。例如，查找姓和名按字母顺序最靠前的人：

-- 找到姓和名按字母顺序排列最靠前的人员
SELECT *
FROM   person
WHERE  (firstname, lastname) = (SELECT MIN(firstname), MIN(lastname) FROM person);

解释：

• 子查询返回最小的 firstname 和 lastname，确保结果中最多包含一行。
• 外层查询会逐行检查是否匹配子查询的结果。

注意：
如果最小的 firstname 和 lastname 来自不同的人，此查询仍然有效，但可能返回空结果。

---

总结

子查询的使用场景多样，且支持灵活嵌套：

1. 标量值子查询：适合在需要单个值的位置使用，例如 SELECT 列表或 WHERE 子句中。
2. 相关子查询：外层查询的每一行都会执行一次子查询，常用于需要动态参数的场景，但性能可能较差。
3. 行子查询：返回一行或多列值，用于匹配特定行的条件。

尽量在可能的情况下用 JOIN 或其他优化方式替代性能较差的相关子查询。

子查询 (Subquery)

示例 1：按家庭获取最小的名字和姓氏

以下示例用于检索每个家庭中按字母顺序最小的名字和姓氏。为此，必须使用相关的行子查询：

-- 每个家庭一行，共 7 行
SELECT *
FROM   person p
WHERE  (firstname, lastname) = 
  (SELECT MIN(firstname), MIN(lastname) FROM person sq WHERE p.lastname = sq.lastname);

解释：

• 外层查询：为每行提供 p.lastname。
• 子查询：获取与外层行同姓的人员中名字和姓氏按字母顺序排列最小的值。
• 相关性：子查询依赖外层查询的 p.lastname，因此对每一行都执行一次。

对于每个家庭，至少有一人满足条件。如果家庭中存在多个满足条件的人，结果可能包含多个匹配行。

---

示例 2：表子查询

表子查询返回多行。以下示例检索有联系方式的人员：

SELECT * 
FROM   person 
WHERE  id IN 
  (SELECT person_id FROM contact);  -- 子查询

解释：

• 子查询：返回 contact 表中所有包含 person_id 的行。
• 外层查询：仅检索 id 与子查询结果匹配的 person 表中的行。

注意：IN 运算符可以作用于子查询生成的表，但诸如 = 或 > 等运算符则无法直接用于表子查询。

---

示例 3：相关表子查询

将相关性添加到子查询中。例如，查找电子邮件地址中包含姓氏的人员：

SELECT * 
FROM   person p
WHERE  id IN 
  (SELECT person_id 
   FROM   contact c
   WHERE  c.contact_type = 'email'
   AND    UPPER(c.contact_value) LIKE CONCAT(CONCAT('%', UPPER(p.lastname)), '%'));

解释：

• 相关性：子查询引用了外层查询中的 p.lastname。
• 逻辑：子查询筛选出电子邮件类型的联系方式，检查其中是否包含对应人员的姓氏（不区分大小写）。
• 结果：仅 Mr. Goldstein 满足条件。

---

示例 4：子查询与 JOIN 结合

以下示例将子查询的结果与外层查询进行 JOIN，获取每个家庭中最大体重的成员信息：

SELECT *
FROM   person p
JOIN   (SELECT lastname, max(weight) AS max_fam_weight
        FROM   person
        GROUP BY lastname) AS sq
ON     p.lastname = sq.lastname;

解释：

• 子查询：按姓氏分组，计算每个家庭的最大体重。
• 外层查询：通过 lastname 将 person 表与子查询结果连接。

---

历史数据问题：检索最新版本记录

示例 1：非相关表子查询

检索每个预订中最新版本的记录：

SELECT *
FROM   booking b
WHERE  (booking_number, version) IN
  (SELECT booking_number, MAX(version) FROM booking sq GROUP BY booking_number)
ORDER BY booking_number;

解释：

• 子查询：生成每个 booking_number 的最高版本号。
• 外层查询：根据子查询的结果匹配完整记录。

---

示例 2：相关标量子查询

另一种方法是使用相关子查询来获取最新版本：

SELECT *
FROM   booking b
WHERE  version = 
  (SELECT max(version) FROM booking sq WHERE sq.booking_number = b.booking_number)
ORDER BY booking_number;

解释：

• 对于 booking 表的每一行，子查询返回相同预订编号的最高版本号。
• 外层查询筛选出版本号与子查询结果匹配的行。

---

示例 3：获取历史版本记录

检索某个特定预订的历史记录（非最新版本）：

SELECT *
FROM   booking b
WHERE  version != 
  (SELECT max(version) FROM booking sq WHERE sq.booking_number = b.booking_number)
AND    booking_number = 4711
ORDER BY version;

解释：

• 过滤条件：仅限于指定预订编号 (4711) 的非最高版本记录。

---

陷阱

如果未正确关联子查询和外层查询，可能导致意外结果。例如：

-- 意外结果：包含实际最新版本
SELECT *
FROM   booking b
WHERE  version != (SELECT max(version) FROM booking)
AND    booking_number = 4711
ORDER BY version;

原因：子查询未限制为特定的 booking_number，结果返回所有预订中的最高版本号，而不是仅限当前预订。

修正：需要明确关联子查询和外层查询：

SELECT *
FROM   booking b
WHERE  version != 
  (SELECT max(version) FROM booking sq WHERE sq.booking_number = b.booking_number)
AND    booking_number = 4711
ORDER BY version;

---

总结

子查询提供了强大的灵活性，但在性能和可读性上需要平衡：

1. 非相关子查询更高效，因为只需执行一次。
2. 相关子查询因需要多次执行，性能较差，建议在可能的情况下使用 JOIN 优化。
3. 理解子查询的类型和作用场景（标量、行、表）有助于构建更高效的 SQL 查询。