sql-sub-queries

SQL 子查询

什么是子查询？

子查询，也称为内部查询（inner query）或嵌套查询（nested query），是一个嵌套在另一个 SQL 查询中的 SELECT 查询。子查询允许您通过将一个查询的结果作为另一个查询的输入，从而构建更复杂、更强大的查询。

它们可以在主（外部）查询的 SELECT、FROM、WHERE 和 HAVING 子句中使用，通常与 IN、NOT IN、EXISTS、ANY 和 ALL 等操作符结合使用。

子查询的关键准则

• 子查询必须始终用括号 () 括起来。
• 子查询可以返回单个值（标量，scalar）、多行单列，或者一个完整的表。
• 子查询中通常不允许使用 ORDER BY 子句，除非您同时使用了 TOP 或 LIMIT 子句。
• 为了提高可读性，尤其是在嵌套子查询中，考虑使用公用表表达式（CTEs）作为现代替代方案。

WHERE 子句中的子查询

子查询最常见的用途是在 WHERE 子句中过滤结果。子查询首先执行，其结果被外部查询使用。

让我们使用 CUSTOMERS 表作为示例。（有关 CREATE 和 INSERT 语句，请参阅上一章节）。

SELECT ID, NAME, SALARY
FROM CUSTOMERS
WHERE ID IN (SELECT CUSTOMER_ID FROM ORDERS WHERE AMOUNT > 2000);

此查询首先从 ORDERS 表中查找所有订单金额大于 2000 的 CUSTOMER_ID。然后，外部查询从 CUSTOMERS 表中选择这些客户的详细信息。

现代替代方案：公用表表达式（CTEs）

对于复杂或嵌套的子查询，现代 SQL 提供了一种更具可读性和可维护性的替代方案：公用表表达式（CTEs），使用 WITH 子句定义。CTE 允许您定义一个临时的、命名的结果集，您可以在主查询中引用它。

以下是使用 CTE 重写的先前示例：

WITH HighValueCustomers AS (
    -- 首先，定义具有高价值订单的客户 ID 集合
    SELECT CUSTOMER_ID
    FROM ORDERS
    WHERE AMOUNT > 2000
)
-- 现在，在主查询中使用这个命名结果集
SELECT c.ID, c.NAME, c.SALARY
FROM CUSTOMERS c
JOIN HighValueCustomers hvc ON c.ID = hvc.CUSTOMER_ID;

请注意，逻辑被分解为清晰、顺序的步骤，这使得查询更易于理解和调试。

与 DML 语句一起使用的子查询

与 INSERT 一起使用的子查询

子查询可用于填充表，使用来自另一个表的数据。

-- 假设 CUSTOMERS_BKP 是一个空表，其结构与 CUSTOMERS 相同
INSERT INTO CUSTOMERS_BKP (ID, NAME, AGE, CITY, SALARY)
SELECT ID, NAME, AGE, CITY, SALARY
FROM CUSTOMERS
WHERE AGE > 30;

与 UPDATE 一起使用的子查询

您可以使用子查询来确定要更新哪些行。

-- 将孟买客户的薪资增加 10%
UPDATE CUSTOMERS
SET SALARY = SALARY * 1.10
WHERE CITY = 'Mumbai';

一个使用子查询的更复杂示例：

-- 为下过订单的客户提供 5% 的奖金
UPDATE CUSTOMERS
SET SALARY = SALARY * 1.05
WHERE ID IN (SELECT DISTINCT CUSTOMER_ID FROM ORDERS);

与 DELETE 一起使用的子查询

子查询可以识别要删除的行。

-- 删除没有订单的客户
DELETE FROM CUSTOMERS
WHERE ID NOT IN (SELECT CUSTOMER_ID FROM ORDERS);

性能与陷阱

相关子查询

相关子查询是指其值依赖于外部查询的子查询。它会为外部查询处理的每一行评估一次，这可能导致性能不佳。通常，相关子查询可以重写为 JOIN 以提高效率。

常见陷阱：标量子查询错误

如果子查询用在期望单个值的位置（例如，WHERE column = (subquery)），如果子查询返回多于一行，则会报错。在这种情况下，您可能需要使用 IN 而不是 =，或者重新考虑您的逻辑。