课程 6.3: 相关子查询

在之前的课程中，我们使用了可以独立运行的 "独立" 子查询。在本课程中，我们介绍 相关子查询 - 一种更高级的子查询类型，它依赖于外部查询中的单元格或值。

什么是相关子查询？

当子查询引用外部查询中表的列时，它就是 相关的。与常规子查询不同，相关子查询不能独立于外部查询执行。

工作原理：

1. 数据库从 外部查询 中获取一行。
2. 它使用该特定行的值执行 内部查询。
3. 它使用内部查询的结果来满足 WHERE（或 SELECT）子句。
4. 它移动到 下一行 并重复该过程。

性能注意： 由于相关子查询可能会为外部查询中的每一行执行一次，因此在非常大的数据集上，它可能比 JOIN 或常规子查询慢。

1. 在 WHERE 中的相关子查询

最常见的用法是将一行的值与特定于 该 行的一组数据进行比较。

场景： 查找所有替换成本高于 同一评级类别（例如 G、PG、R）电影的平均替换成本的电影。

SELECT
    title,
    rating,
    replacement_cost
FROM
    film AS f1
WHERE
    replacement_cost > (
        SELECT AVG(replacement_cost)
        FROM film AS f2
        WHERE f1.rating = f2.rating
    );

• 关联性： f1.rating = f2.rating 将内部查询与外部查询的当前行链接。
• 逻辑： 对于每部电影，数据库计算其特定评级的平均成本，并检查该电影是否更贵。

2. 在 SELECT 中的相关子查询

您可以使用相关子查询来检索每行的描述性数据或聚合，而无需使用 GROUP BY 子句。

场景： 显示每个类别的类别名称和最长电影的标题。

SELECT
    c.name AS category_name,
    (
        SELECT f.title
        FROM film f
        JOIN film_category fc ON f.film_id = fc.film_id
        WHERE fc.category_id = c.category_id
        ORDER BY f.length DESC
        LIMIT 1) AS longest_film_title
FROM
    category AS c;

3. 与 EXISTS 的相关子查询

我们在上一课中看到了 EXISTS 操作符。EXISTS 几乎总是与相关子查询一起使用。

场景： 查找在特定商店（商店 1）至少租赁过一部电影的客户。

SELECT
    first_name,
    last_name
FROM
    customer AS c
WHERE
    EXISTS (
        SELECT 1
        FROM rental AS r
        INNER JOIN inventory AS i ON r.inventory_id = i.inventory_id
        WHERE r.customer_id = c.customer_id
        AND i.store_id = 1
    );

本课的关键要点

• 相关子查询 依赖于外部查询的值。
• 它是 逐行执行 的（每个候选行执行一次）。
• 别名 对于区分外部和内部表实例至关重要。
• 它们在 组相对比较（将一行与其自身组进行比较）中非常强大。
• 在处理数百万条记录时，请注意 性能。