SQL子查询

🍋子查询的初步使用

企业常用的子查询示例？
有哪些必须用子查询的情况，有哪些常见的改join/with提高性能的例子？
Hash Join、Semi Join、宽表、FilterProject、SemiJoin 、HashBuilder这些词什么意思

下面把“必须用子查询”和“可以改 JOIN / WITH 提速”这两种场景一次性拆开讲透。

每条都给出 Trino（Presto）语法示例，并注明 为什么必须/可以改写

一、4 种“必须用子查询”的场景

（改写 JOIN 会语义错误或更慢）

场景	必须用子查询的原因	Trino 示例
1. 聚合后回查原表（“找高于部门平均”）	聚合值与明细行逐行比较，JOIN 先聚合再回查=自己和自己连，只能用相关子查询或窗口函数	SELECT * FROM employee e WHERE `salary >` (SELECT `AVG(salary)` FROM employee WHERE dept=e.dept)
2. EXISTS / NOT EXISTS	语义是“有无匹配”，JOIN 会放大行数；NOT IN 对 NULL 敏感	SELECT * FROM customer c WHERE NOT `EXISTS` (`SELECT 1` FROM orders o WHERE o.customer_id=c.id)
3. 行号/ Top-N 子查询（每组取最新 1 条）	需要先排序再过滤，JOIN 写不出“每组”限制	SELECT * FROM (SELECT *, row_number() OVER (PARTITION BY user_id ORDER BY login_dt DESC) rn FROM login_log) t `WHERE rn=1`
4. 测量数据质量（重复主键、空值率）	只关心统计结果，不需要展开明细；JOIN 会强行拉明细	SELECT order_id, COUNT() FROM orders GROUP BY order_id `HAVING COUNT()>1`

二、6 个“常见可改写”例子

（子查询 → JOIN / WITH，性能 2-10 倍提升）

1. 相关子查询 → JOIN

原写法（慢）

1
2
3

SELECT emp_id, emp_name,
       (SELECT dept_name FROM department d WHERE d.dept_id = e.dept_id) AS dept_name
FROM employee e;

改写（快）

1
2
3

SELECT e.emp_id, e.emp_name, d.dept_name
FROM employee e
JOIN department d ON d.dept_id = e.dept_id;

提速点：把 每行一次索引回表 改成 一次 Hash Join。

2. 相关聚合子查询 → WITH + JOIN

原写法（O(N²)）

1
2
3

SELECT emp_id, salary,
       salary - (SELECT AVG(salary) FROM employee WHERE dept_id = e.dept_id) AS diff
FROM employee e;

改写（O(N)）

WITH dept_avg AS (
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM employee
    GROUP BY dept_id
)
SELECT e.emp_id, e.salary, e.salary - d.avg_salary AS diff
FROM employee e
JOIN dept_avg d ON d.dept_id = e.dept_id;

提速点：聚合只算一次，复用结果。

3. IN (子查询) → Semi Join

原写法

1 2	SELECT * FROM customer WHERE customer_id IN (SELECT customer_id FROM orders WHERE order_amt > 10000);

改写（Trino 自动做，也可显式写）

1
2
3

SELECT c.*
FROM customer c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND o.order_amt > 10000);

提速点：Semi Join 可在 Hash Build 阶段直接过滤，不放大右表。

4. 子查询当“临时宽表” → WITH 展开后多次复用

场景：同一张中间结果要被选两次

WITH high_val_customer AS (
    SELECT customer_id, SUM(order_amt) AS total
    FROM orders
    GROUP BY customer_id
    HAVING SUM(order_amt) > 100000
)
SELECT h.customer_id, h.total, a.address
FROM high_val_customer h
JOIN address a ON a.customer_id = h.customer_id;

提速点：只扫描一次 orders，CTE 结果默认物化（Trino 358+ 支持 CTE Reuse）。

5. 多层嵌套子查询 → 一步 JOIN

原写法

SELECT *
FROM sku s
WHERE category_id = (SELECT category_id FROM category WHERE category_name = '手机')
  AND brand_id    = (SELECT brand_id   FROM brand    WHERE brand_name    = '华为');

改写

SELECT s.*
FROM sku s
JOIN category c ON c.category_id = s.category_id AND c.category_name = '手机'
JOIN brand    b ON b.brand_id    = s.brand_id    AND b.brand_name    = '华为';

提速点：两次相关子查询 → 一次多表 Hash Join，且可以利用 复合索引。

6. 子查询里再子查询 → 扁平化 JOIN + 窗口函数

原写法

SELECT *
FROM (
    SELECT *, (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) AS cnt
    FROM customer c
) t
WHERE cnt >= 5;

改写

SELECT c.*, t.cnt
FROM customer c
JOIN (SELECT customer_id, COUNT(*) AS cnt FROM orders GROUP BY customer_id) t
  ON t.customer_id = c.customer_id
WHERE t.cnt >= 5;

提速点：把 相关子查询 变成 非相关聚合，再 Hash Join。

三、1 张决策表，10 秒判断“能不能改”

检查项	子查询必须保留	可改写 JOIN/WITH
是否 EXISTS / NOT EXISTS	✅	❌
是否“聚合后回查明细”	✅（或改窗口）	❌
是否“每组 Top-N”	✅（需窗口）	❌
是否只拿聚合指标	❌	✅（先 WITH 聚合）
是否相关子查询且无聚合	❌	✅（直接 JOIN）
是否 IN (子查询)	❌	✅（Semi Join）

四、小结口诀

“聚合回查、存在性、Top-N 必须子查；其余一律拉出来做 JOIN/WITH。”

拿不准执行计划时，EXPLAIN (TYPE DISTRIBUTED) 看有没有 FilterProject+SemiJoin 或 HashBuilder 即可。

高频黑话

Hash Join（哈希连接）

定义：把两表里连接键先算成哈希表，再一次性“对号入座”做等值匹配。
类比：老师拿两张名单，先给 A 班每人发一个“学号-座位号”小纸条（建哈希表），再让 B 班学生按纸条找座位，O(1) 定位。

Trino 计划里：

1
2
3

└─ InnerJoin[hash = (user_id)]  
   ├─ Scan[user_id, …]  ←  Build 端（通常选小表）  
   └─ Scan[user_id, …]  ←  Probe 端

Semi Join（半连接）

定义：只关心左表记录是否在右表存在匹配，不返回右表列，也不去重左表。
类比：安检门只看你是否在“黑名单”里，查到就拦下，不会把黑名单详细信息打印出来。

Trino 自动把 EXISTS / IN (子查询) 改写成 SemiJoin：

1
2
3

└─ SemiJoin[hash = (user_id)]  
   ├─ Scan…  ← 左表（旅客）  
   └─ Scan…  ← 右表（黑名单）

HashBuilder（哈希建造者）

定义：Trino 执行计划里的一个运行时算子，负责把 Build 端数据全部读进内存并建成哈希表。
类比：安检人员先把黑名单一次性录入扫描枪（HashBuilder），后面每过来一个旅客就扫一下枪（Probe）。
计划关键字：HashBuilder[hash = (key)] 常出现在 SemiJoin 或 HashJoin 的右分支。

FilterProject（过滤+投影）

定义：Trino 里最轻量的算子，先过滤行（Filter）再裁剪列（Project），无 I/O 开销。
类比：超市收银台：先把你购物车里的过期商品扔掉（Filter），再把不需要的小票副联撕掉（Project）。

计划示例：

1 2	└─ FilterProject[predicate = (status = 'PAID')] └─ Scan…

宽表（Wide Table）

定义：把多张维度表字段冗余到一张事实表里，减少 Join，牺牲存储换查询速度。
类比：原本要点 5 份外卖（5 张表 Join），现在直接买一份“全家桶”（宽表），一口吃到所有菜。
在 Trino 里：宽表通常以 Hive/Iceberg Parquet/ORC 形式存在，列式存储 + 分区，配合 列裁剪 扫描极快。

总结

术语	口诀
Hash Join	小表先变哈希，大表逐行探。
Semi Join	只问“有没有”，不拿右表数据。
HashBuilder	造哈希表的阶段，看右支。
FilterProject	先过滤行，再裁剪列。
宽表	把字段先拼好，省掉 Join。

🍋子查询其他

LATERAL横向子查询

横向子查询是什么
这效率很不高吧
那我为什么不用普通 JOIN
ON true什么意思

“横向子查询”是 LATERAL子查询的直译，也有人叫横向派生表、横向连接。它的核心作用只有一句话：

让右侧的子查询能够引用左侧已经列出的表/别名，实现“逐行计算”。

出现位置只能出现在 FROM 或 JOIN 子句里，且前面必须带关键字 LATERAL（PostgreSQL、SQL Server、Oracle、MySQL 8.0+ 都支持）。
与普通子查询的区别

普通派生表	LATERAL 子查询
先整体算出结果，再拿给外层用	对外层每一行都重新计算一次
内部看不到外层的任何列	内部可以直接引用外层“左边”已经出现的列

一眼看懂例子
- 需求：对每笔订单，找出“该客户”历史订单里金额最高的那一条。
- orders订单表

SELECT o.*, top.*
FROM orders AS o
LEFT JOIN LATERAL (
	SELECT * FROM orders o2
    WHERE o2.customer_id = o.customer_id      -- 直接用到外层 o 的列
    ORDER BY o2.amount DESC
    LIMIT 1
) AS top ON true;

典型场景速记

每组/每行求 Top-N、累积、相邻行、行列转换
把“相关子查询”改写成 JOIN 形式，方便利用索引、避免重复扫描
需要“行级函数”效果，又不想写存储过程

🍋子查询的时候用 EXISTS 还是 IN

简单来说：大多数情况下建议优先使用 EXISTS，因为它通常有更好的性能表现，并且能更自然地处理 NULL 值。

1. 核心区别

IN： 先执行子查询，将结果集存在内存中，然后对外部查询进行遍历，检查外部表的值是否在子查询的结果集中。
EXISTS： 对外部查询进行遍历，对每一行数据都执行一次子查询，如果子查询返回任何记录，则放入结果集（不关心子查询返回的具体内容，只关心是否有返回行）。

2. 性能对比

当子查询表大，外部表小时（EXISTS 胜出）：
如果子查询返回的数据量很大，使用 IN 可能会消耗较多内存和临时表空间。EXISTS 因为是一种半连接机制，通常可以利用索引更快地找到匹配项。
当子查询表小，外部表大时（不一定）：
如果子查询很小，IN 可能会更快（因为子查询只需要执行一次）。EXISTS 需要对外部表进行全表扫描，但如果外部表的关联字段有索引，EXISTS 依然很快。
现代数据库的优化：

在 MySQL 8.0+、PostgreSQL、SQL Server 等现代数据库中，优化器通常会自动将 IN 转换为 SEMI JOIN 或类似 EXISTS 的执行计划，两者性能差异已不大。

但注意：当子查询无法使用索引时，手动选择仍然重要。

3. NOT IN 与 NOT EXISTS（巨大差异）

NOT IN 有风险：
如果子查询的结果中包含任何一个 NULL，NOT IN 会返回空集（因为 id NOT IN (1, 2, NULL) 等价于 id != 1 AND id != 2 AND id != NULL，而 id != NULL 永远是未知的）。
因此，如果使用 NOT IN，必须确保子查询的结果集剔除掉 NULL 值。
NOT EXISTS 很安全：
不受 NULL 影响，只要子查询没找到匹配行，就返回真。

4. 使用建议

1. 优先使用 EXISTS 的场景

-- 检查存在性（相关子查询）
SELECT * FROM A 
WHERE EXISTS (SELECT 1 FROM B WHERE B.id = A.id);

-- 优势：找到第一个匹配就停止，不会全表扫描B

子查询表 B 很大，主查询表 A 较小
需要判断”是否存在”而非获取具体值
如果做反查询（找不存在的数据），除非能100%确定子查询结果不含 NULL，否则请直接使用 NOT EXISTS。

2. 优先使用 IN 的场景

--  固定小集合查询
SELECT * FROM A 
WHERE id IN (1, 2, 3, 4, 5);

--  子查询结果很小且确定
SELECT * FROM A 
WHERE id IN (SELECT id FROM B WHERE status = 'active' LIMIT 10);

子查询结果集 很小且确定（如常量列表）
需要匹配具体值列表

3. 避免使用的场景

-- ❌ IN + 子查询含 NULL（返回空结果）
SELECT * FROM A WHERE id IN (SELECT id FROM B); -- B.id 含 NULL

-- ❌ IN + 子查询结果集巨大（性能差）
SELECT * FROM A WHERE id IN (SELECT id FROM B); -- B 有百万行

5. 最佳实践总结

场景	推荐	原因
判断存在性	`EXISTS`	短路求值，性能稳定
小值列表	`IN`	简洁高效
子查询可能含 NULL	`EXISTS` 或 `IN + NOT NULL` 过滤	避免逻辑错误
两表都很大	`EXISTS` 或 `JOIN`	优化器更易生成高效计划

一句话原则：不确定时，用 EXISTS 更安全；确定子查询结果很小，用 IN 更简洁。