Subqueries
Nest 查询内部查询: scalar, correlated, IN, EXISTS, and CTEs.
你将学到
- 什么 subquery 是 and problems 它解决
- Scalar subqueries returning 单个值
- Subqueries 使用 IN and NOT IN for 设置 membership
- Correlated subqueries 那个引用外部查询
- Using EXISTS for efficient existence 检查
概念讲解
什么是子查询?
subquery 是一种 SELECT statement nested inside another SQL statement. It lets you compute a value or a set of values on the fly, without a separate query or application code. Subqueries can appear in the SELECT list, the FROM clause (as a derived table), or the WHERE clause.
Scalar Subqueries
A scalar subquery returns exactly one row and one column. It can be used anywhere a single value is expected. For example, SELECT name, price FROM products WHERE price > (SELECT AVG(price) FROM products) finds products above the average price. The inner query runs first, returns a single number, and the outer query compares each row against it.
IN and NOT IN
IN (subquery) checks if a value is in the set returned by the subquery. SELECT * FROM 顺序 WHERE user_id IN (SELECT id FROM 用户 WHERE active = TRUE) returns orders from active users only. Be cautious with NOT IN and NULLs — if the subquery returns any NULL, NOT IN returns no rows at all due to three-valued logic.
Correlated Subqueries
A correlated subquery references a column from the outer query, so it is re-evaluated for each row. This can be powerful but slow on large tables. For example, SELECT name, (SELECT COUNT(*) FROM 顺序 WHERE 顺序.user_id = 用户.id) AS order_count FROM 用户 gives each user's order count. Modern SQL prefers JOINs or CTEs for better performance and readability.
EXISTS
EXISTS (subquery) returns TRUE if the subquery returns at least one row. It is often more efficient than IN because the database can short-circuit as soon as it finds a match. SELECT * FROM 用户 WHERE EXISTS (SELECT 1 FROM 顺序 WHERE 顺序.user_id = 用户.id) finds users who have at least one order.
示例代码
-- Scalar subquery: products above average price
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products)
ORDER BY price DESC;
-- IN subquery: orders from active users
SELECT id, total, created_at
FROM orders
WHERE user_id IN (
SELECT id FROM users WHERE active = TRUE
);
-- Correlated subquery: order count per user
SELECT
u.name,
(SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u
ORDER BY order_count DESC;
-- EXISTS: users who have placed at least one order
SELECT u.name
FROM users u
WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = u.id
);
Four subquery 模式: scalar for comparison, IN for 设置 membership, correlated for 每-行 computation, and EXISTS for existence 检查.
动手试一试
- SQL Formatter
格式 nested subqueries 使用 proper indentation.
- JSON Path
JSONPath 是相似到 subqueries — extract nested 数据.
常见错误
错误写法
Using NOT IN 使用 subquery 那个可能返回 NULL
正确做法
NOT IN 返回否 行如果 subquery 包含任何 NULL. Use NOT EXISTS 相反, 哪个 handles NULLs 正确地.
错误写法
编写 correlated subquery 当 JOIN 会 suffice
正确做法
Correlated subqueries re-运行 for 每个行. LEFT JOIN 使用 GROUP BY 是经常 faster and 更多 readable. 检查 execution 计划.
练习 Subqueries
5 个练习
查阅不认识的术语
新手友好的术语表用大白话解释编程术语——变量、函数、DOM、Promise 等等。
动手做项目
通过引导式项目应用所学,含起始代码与解决方案。
解读错误信息
用大白话解释常见错误——什么意思、为什么发生、如何修复。