TDSQL Boundless 子查询

本文档介绍如何在 TDSQL Boundless 数据库中使用子查询。子查询是嵌套在另一个查询内部的 SQL 查询，允许您在一条语句中使用另一个查询的结果。
子查询的分类
在 TDSQL Boundless 中，子查询通常有以下几种形式：
标量子查询
标量子查询返回单行单列的值，可以出现在 SELECT 列表、WHERE 条件等任何需要单个值的位置。其关键特征是子查询的结果等价于一个常量值。
SELECT 
    c_name,
    c_acctbal,
    (SELECT AVG(c_acctbal) FROM customer) AS avg_balance
FROM customer
LIMIT 5;
派生表
派生表是放在 FROM 子句中的子查询，作为一个临时表参与后续查询。其关键特征是子查询必须用括号包裹并指定别名。
SELECT seg.c_mktsegment, seg.cnt
FROM (
    SELECT c_mktsegment, COUNT(*) AS cnt
    FROM customer
    GROUP BY c_mktsegment
) seg
ORDER BY seg.cnt DESC;
存在性子查询
通过 EXISTS、NOT EXISTS、IN、NOT IN 等关键字判断子查询是否返回数据，结果是布尔值。其关键特征是不关心子查询返回的具体值，只关心是否有行存在。
-- EXISTS：判断是否存在匹配行
SELECT c_name FROM customer c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.o_custkey = c.c_custkey
);
﻿
-- IN：判断值是否在结果集中
SELECT c_name FROM customer
WHERE c_nationkey IN (SELECT n_nationkey FROM nation WHERE n_name = 'CHINA');
集合比较子查询
使用 ANY、ALL、SOME 关键字将一个值与子查询返回的结果集进行比较。其关键特征是比较运算符（=、>、< 等）与 ANY/ALL 组合使用。
-- = ANY 等价于 IN
SELECT c_name, c_acctbal FROM customer
WHERE c_acctbal > ANY (
    SELECT o_totalprice FROM orders WHERE o_orderstatus = 'F'
);
﻿
-- > ALL：大于子查询返回的所有值
SELECT c_name, c_acctbal FROM customer
WHERE c_acctbal > ALL (
    SELECT AVG(c_acctbal) FROM customer GROUP BY c_mktsegment
);
作为比较运算符操作数
子查询直接作为比较运算符（>、<、=、>=、<=、<>）的一侧操作数。其关键特征是子查询必须返回单行单列（即标量），与标量子查询的区别在于它出现在 WHERE/HAVING 的比较条件中。
SELECT o_orderkey, o_totalprice
FROM orders
WHERE o_totalprice > (
    SELECT AVG(o_totalprice) FROM orders
);
子查询的相关性
根据子查询是否引用了外层查询的列，可分为非关联子查询和关联子查询两类。
非关联子查询
非关联子查询不引用外层查询的任何列，其结果独立于外层查询。TDSQL Boundless 会先执行内层子查询，将结果作为常量代入外层查询。
查询账户余额高于所有客户平均余额的客户：
SELECT c_custkey, c_name, c_acctbal
FROM customer
WHERE c_acctbal > (
    SELECT AVG(c_acctbal) FROM customer
);
TDSQL Boundless 在处理该查询时，会先执行内层子查询：
SELECT AVG(c_acctbal) FROM customer;
假设计算结果为 4990.51，则外层查询等价于：
SELECT c_custkey, c_name, c_acctbal
FROM customer
WHERE c_acctbal > 4990.51;
使用 IN 子查询查找有过订单的客户：
SELECT c_custkey, c_name, c_mktsegment
FROM customer
WHERE c_custkey IN (
    SELECT DISTINCT o_custkey FROM orders
    WHERE o_orderdate >= '1995-01-01'
);
内层子查询独立执行，返回一组 o_custkey 值，外层查询在这组值中进行匹配。
关联子查询
关联子查询引用了外层查询的列，因此内层查询的结果依赖于外层查询当前正在处理的行。从逻辑上看，关联子查询需要对外层的每一行都重新执行一次内层查询。
查询每个客户中金额最大的订单：
SELECT o_orderkey, o_custkey, o_totalprice, o_orderdate
FROM orders o1
WHERE o_totalprice = (
    SELECT MAX(o2.o_totalprice)
    FROM orders o2
    WHERE o2.o_custkey = o1.o_custkey
);
内层子查询引用了外层的 o1.o_custkey，对于外层每一行，子查询计算该客户的最大订单金额，然后只保留金额等于最大值的行。
查询账户余额高于同市场细分客户平均余额的客户：
SELECT c1.c_custkey, c1.c_name, c1.c_mktsegment, c1.c_acctbal
FROM customer c1
WHERE c1.c_acctbal > (
    SELECT AVG(c2.c_acctbal)
    FROM customer c2
    WHERE c2.c_mktsegment = c1.c_mktsegment
);
TDSQL Boundless 优化器会尝试对关联子查询进行去关联（Unnesting）优化，将其改写为等价的 JOIN 查询以提升性能。例如上述查询可能被改写为：
SELECT c1.c_custkey, c1.c_name, c1.c_mktsegment, c1.c_acctbal
FROM customer c1
INNER JOIN (
    SELECT c_mktsegment, AVG(c_acctbal) AS avg_acctbal
    FROM customer
    GROUP BY c_mktsegment
) c2 ON c1.c_mktsegment = c2.c_mktsegment
WHERE c1.c_acctbal > c2.avg_acctbal;
改写后的查询只需对 customer 表扫描两次（一次聚合、一次连接），而不是对每个客户都执行一次子查询，性能显著提升。
优化器的去关联优化
TDSQL Boundless 优化器提供了多种自动去关联优化机制，将关联子查询改写为等价的非关联形式，减少子查询的重复执行次数。以下介绍三种主要的去关联优化路径。
Semi-Join 去关联
对于 WHERE 或 INNER JOIN ON 中的 IN/EXISTS 关联子查询，优化器将其转换为 Semi-Join（半连接），子查询只执行一次而非逐行执行。这是最常用的去关联路径，受 optimizer_switch 中的 semijoin 开关控制（默认开启）。
-- 原始关联子查询
SELECT * FROM orders WHERE o_custkey IN (SELECT c_custkey FROM customer WHERE c_nationkey = 1);
﻿
-- 优化器自动改写为 Semi-Join 后，EXPLAIN 中不再出现 DEPENDENT SUBQUERY
EXPLAIN SELECT * FROM orders WHERE o_custkey IN (SELECT c_custkey FROM customer WHERE c_nationkey = 1);
-- id: 1  select_type: SIMPLE  table: orders
-- id: 1  select_type: SIMPLE  table: customer  Extra: FirstMatch(orders)
物化解关联优化
当关联子查询位于 OR 条件中、或子查询含 GROUP BY/HAVING 等原生 Semi-Join 无法处理的场景时，优化器可通过物化解关联规则将关联 EXISTS/IN 子查询改写为非关联 IN 子查询，使其可独立物化执行。
该优化受 transformer_switch 中的 subquery_to_materialize 开关控制（默认开启），并要求 optimizer_switch 中的 materialization 也为开启状态。可通过 SUBQUERY(MATERIALIZATION) 和 SUBQUERY(INTOEXISTS) Hint 分别强制启用或禁用。
改写机制：优化器从子查询的 WHERE/HAVING 中提取形如 inner_col = outer_expr 的等值关联谓词，将其从子查询中剥离，并将关联内侧列追加到子查询的 SELECT 列表。改写后子查询不再引用外层列，变为非关联 IN 子查询，可被独立物化。
-- 改写前：关联 EXISTS 位于 OR 条件中，Semi-Join 无法处理
SELECT * FROM t1
WHERE t1.a = 1
   OR EXISTS (SELECT 1 FROM t2 WHERE t2.b = t1.b AND t2.c > 10);
﻿
-- 改写后：EXISTS 转为非关联 IN，子查询独立物化执行
SELECT * FROM t1
WHERE t1.a = 1
   OR t1.b IN (SELECT t2.b FROM t2 WHERE t2.c > 10 GROUP BY t2.b);
改写后，EXPLAIN 输出中原来的 DEPENDENT SUBQUERY 标记变为 MATERIALIZED，表示子查询已去关联并被物化执行。
适用场景：该优化主要面向原生 Semi-Join 无法处理的场景，包括 OR 条件中的 EXISTS/IN 子查询、含 GROUP BY/HAVING 的关联子查询等。优化器基于代价在物化执行和 EXISTS 回退两种路径间选择最优方案，不会导致性能退化。
限制：不支持 NOT IN/NOT EXISTS（NULL 三值逻辑语义不同）、含窗口函数的子查询、含 OFFSET 的子查询、含 RAND() 等非确定性函数的子查询等场景，此时静默跳过，不影响查询正确性。
subquery_to_derived 变换
TDSQL Boundless 支持将 WHERE/ON 中的关联 IN/EXISTS 子查询转换为派生表（derived table）JOIN 形式，使子查询结果作为临时表一次性计算，主表通过 JOIN 匹配。该变换受 transformer_switch 中的 subquery_to_derived 开关控制（默认关闭），与 optimizer_switch 中的同名开关为"或"关系（任一开启即生效）。如需启用，执行 SET transformer_switch = 'subquery_to_derived=on'，session 级别即时生效。
-- 原始：关联子查询，外表每行都触发一次子查询执行
SELECT * FROM t1 WHERE t1.a IN (SELECT t2.a FROM t2 WHERE t2.b = t1.b);
﻿
-- 变换后等价形式：子查询结果作为派生表一次性计算，主表通过 JOIN 匹配
SELECT t1.* FROM t1 JOIN (SELECT DISTINCT t2.a, t2.b FROM t2) AS derived
  ON t1.a = derived.a AND t1.b = derived.b;
变换生效后，EXPLAIN 输出中会出现 derived 标识（如 derived2），表示子查询已被转换为派生表。通过该标识可判断变换是否生效。
说明：
当变换条件不满足时（例如关联条件为非等值比较、子查询含 RAND() 等非确定性函数、子查询含 OFFSET 等），TDSQL Boundless 会静默回退到原始执行方式，查询正常执行且结果不变。这与社区 MySQL 的行为不同：社区 MySQL 在上述场景下会返回 ER_SUBQUERY_TRANSFORM_REJECTED 错误导致查询失败。
启用变换：该变换默认关闭，如需启用，执行 SET transformer_switch = 'subquery_to_derived=on'，session 级别即时生效。也可通过 optimizer_switch 设置 subquery_to_derived=on 达到相同效果。
长 IN 列表转 Semi-Join
当 WHERE col IN (c1, c2, ..., cN) 形式的 IN 列表元素数量超过阈值时，优化器可将其改写为基于 Hash 物化派生表的 Semi-Join，将匹配复杂度从 O(N) 降为 O(1)。该优化受 transformer_switch 中的 inlist_to_join 开关控制（默认开启），触发阈值为系统变量 qt_inlist_to_join_threshold（默认5000，范围2 - 1048576）。
改写机制：
-- 改写前
SELECT * FROM t WHERE col IN (c1, c2, ..., cN);
﻿
-- 改写后（语义等价）
SELECT * FROM t WHERE col IN (
    SELECT _col_1 FROM (VALUES ROW(c1), ROW(c2), ..., ROW(cN)) AS tvc_0
);
改写后，IN 列表被转换为 Table Value Constructor（TVC）派生表，物化后建立 auto_key 索引实现 O(1) Hash 查找。可通过 INLIST_TO_JOIN(@qb_name) 和 NO_INLIST_TO_JOIN(@qb_name) Hint 分别强制启用或禁用。
验证方式：使用 EXPLAIN FORMAT=TREE 查看执行计划，出现 Materialize + scan on in-list: N rows 节点表示转换成功。
-- 未触发转换（IN 列表元素少于阈值）
EXPLAIN FORMAT=TREE SELECT * FROM t WHERE a IN (1,2,3,5);
-- -> Filter: (t.a IN (1,2,3,5))
-- -> Table scan on t
﻿
-- 触发转换（通过 Hint 强制或元素数超过阈值）
EXPLAIN FORMAT=TREE SELECT /*+ INLIST_TO_JOIN(@`select#1`) */ * FROM t WHERE a IN (1,2,3,5);
-- -> Nested loop semijoin
-- -> Filter: (t.a IS NOT NULL)
-- -> Table scan on t
-- -> Filter: (t.a = tvc_0._col_1)
-- -> Index lookup on tvc_0 using <auto_key0> (_col_1=t.a)
-- -> Materialize
-- -> scan on in-list: 5 rows
注意：
长 IN 列表转 Semi-Join 存在以下限制：仅适用于 SELECT 语句；LHS 不支持 BLOB、JSON、GEOMETRY 类型；必须在 AND 上下文中（OR 嵌套中的 IN 不做转换）；外连接（LEFT/RIGHT JOIN）的 ON 条件中的 IN 不做转换；NOT IN 默认不转换；RHS 必须全部为常量值且不含 NULL；optimizer_switch 中 semijoin=off 时不转换。
低基数列的潜在回退：对于 DATE/DATETIME/TIMESTAMP 等低基数时间类型列，转换后可能丧失存储引擎级并行扫描优势导致性能回退，因此默认对该类列不转换（可通过 INLIST_TO_JOIN Hint 绕过）。
关闭变换：执行 SET transformer_switch = 'inlist_to_join=off' 可关闭该优化，session 级别即时生效。
子查询消除优化
除了去关联改写外，TDSQL Boundless 优化器在 SQL 解析绑定阶段还会尝试直接判定谓词子查询（EXISTS、IN、ANY、ALL）的真值。若子查询的结果在此阶段即可确定，优化器会将整个子查询谓词折叠为布尔常量 TRUE 或 FALSE，并完全移除该子查询，从而避免后续的查询优化与执行开销。该能力受 transformer_switch 中的 subquery_elimination 开关控制（默认开启）。
消除场景
TDSQL Boundless 支持以下三类消除场景：
场景
触发条件
折叠结果
适用谓词
空表查询
子查询不包含任何表（例如 FROM DUAL），且不含常量假的 WHERE/HAVING 条件、不含不安全表达式
TRUE
EXISTS
标量聚合查询
子查询为隐式分组的聚合查询（例如 SELECT COUNT(*) FROM t），且 HAVING 条件非常量假
TRUE
EXISTS
常量空结果查询
子查询的 WHERE 或 HAVING 条件在优化阶段可确定为常量 FALSE，即结果集为空
FALSE（ALL 折叠为 TRUE）
EXISTS、IN、ANY、ALL
空表查询示例：EXISTS 子查询未引用任何表，恒定返回一行，因此恒为 TRUE。
SELECT a FROM t1 WHERE EXISTS (SELECT 1 FROM dual) ORDER BY a;
-- 等价于：SELECT a FROM t1 ORDER BY a;
标量聚合查询示例：聚合函数在隐式分组（无 GROUP BY）下必然返回一行，因此 EXISTS 恒为 TRUE。
SELECT a FROM t1 WHERE EXISTS (SELECT COUNT(*) FROM t2) ORDER BY a;
-- 等价于：SELECT a FROM t1 ORDER BY a;
常量空结果查询示例：子查询的 WHERE 条件在优化阶段可确定为常量 FALSE，结果集必为空。
-- IN 谓词：空结果集折叠为 FALSE
SELECT a FROM t1 WHERE a IN (SELECT a FROM t2 WHERE FALSE) ORDER BY a;
-- 等价于：SELECT a FROM t1 WHERE FALSE ORDER BY a;（返回 0 行）
﻿
-- ALL 谓词：对空结果集恒为 TRUE（无反例）
SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t2 WHERE FALSE) ORDER BY a;
-- 等价于：SELECT a FROM t1 ORDER BY a;
使用限制
以下场景不会被消除，子查询将保留原有执行路径：
子查询包含 UNION、INTERSECT、EXCEPT。
子查询包含窗口函数，或使用 WITH ROLLUP。
子查询的 LIMIT 不是正整数常量，或 OFFSET 不为 0。
子查询涉及加锁读，例如 FOR UPDATE、LOCK IN SHARE MODE。
子查询中包含可能有副作用或存在运行时报错风险的表达式，例如 JSON 函数、空间函数、正则函数、DIV/MOD 运算、用户变量赋值、存储函数调用等。
ANY/ALL 左侧操作数为行值表达式（例如 (a, b) > ALL (...)），仅支持标量左操作数。
IN/ANY/ALL 左侧表达式包含不安全或可能报错的内容。
验证与关闭
可通过 optimizer_trace 查看子查询消除的执行详情：
SET optimizer_trace = 'enabled=on';
SELECT a FROM t1 WHERE EXISTS (SELECT 1 FROM dual);
SELECT trace FROM information_schema.OPTIMIZER_TRACE;
命中消除时，trace 中的 subquery_elimination 节点包含 "transformed": true 及折叠结果 "type": true（折叠为 TRUE）或 "type": false（折叠为 FALSE）；未命中时包含 "transformed": false 及具体的 rejection_reason。
如需关闭该优化，执行以下语句，session 级别即时生效：
SET SESSION transformer_switch = 'subquery_elimination=off';
常见子查询场景
EXISTS 子查询
EXISTS 用于判断子查询是否返回了至少一行数据，常用于存在性检查。
查询有过高金额订单（金额 > 300000）的客户：
SELECT c.c_custkey, c.c_name, c.c_acctbal
FROM customer c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.o_custkey = c.c_custkey
      AND o.o_totalprice > 300000
);
查询没有下过订单的客户（NOT EXISTS）：
SELECT c.c_custkey, c.c_name, c.c_phone
FROM customer c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o
    WHERE o.o_custkey = c.c_custkey
);
NOT EXISTS 在语义上等价于 LEFT JOIN ... WHERE ... IS NULL，但在某些场景下两者的执行效率不同，可通过 EXPLAIN 进行比较。
IN 子查询
IN 用于判断某个值是否在子查询返回的结果集中。
查询来自 ASIA 区域国家的客户：
SELECT c_custkey, c_name, c_nationkey
FROM customer
WHERE c_nationkey IN (
    SELECT n_nationkey FROM nation
    WHERE n_regionkey IN (
        SELECT r_regionkey FROM region
        WHERE r_name = 'ASIA'
    )
);
IN 与 EXISTS 的选择：当子查询返回的结果集较小时，IN 和 EXISTS 性能差异不大；当外层表较小而子查询结果集较大时，EXISTS 通常更高效。
标量子查询
标量子查询返回单个值，可以出现在 SELECT 列表、WHERE 条件等位置。
在 SELECT 列表中使用标量子查询 — 查询每个订单及其客户名称：
SELECT 
    o_orderkey,
    o_totalprice,
    o_orderdate,
    (SELECT c_name FROM customer WHERE c_custkey = o_custkey) AS customer_name
FROM orders
WHERE o_orderdate = '1995-03-15';
SELECT 列表中的标量子查询在逻辑上会对每一行执行一次，当外层行数较多时建议改写为 JOIN：
SELECT 
    o.o_orderkey,
    o.o_totalprice,
    o.o_orderdate,
    c.c_name AS customer_name
FROM orders o
INNER JOIN customer c ON o.o_custkey = c.c_custkey
WHERE o.o_orderdate = '1995-03-15';
相关文档
﻿并行子查询执行策略﻿
场景	触发条件	折叠结果	适用谓词
空表查询	子查询不包含任何表（例如 `FROM DUAL`），且不含常量假的 `WHERE`/`HAVING` 条件、不含不安全表达式	`TRUE`	`EXISTS`
标量聚合查询	子查询为隐式分组的聚合查询（例如 `SELECT COUNT(*) FROM t`），且 `HAVING` 条件非常量假	`TRUE`	`EXISTS`
常量空结果查询	子查询的 `WHERE` 或 `HAVING` 条件在优化阶段可确定为常量 `FALSE`，即结果集为空	`FALSE`（`ALL` 折叠为 `TRUE`）	`EXISTS`、`IN`、`ANY`、`ALL`
子查询

本页目录：

子查询的分类

标量子查询

派生表

存在性子查询

集合比较子查询

作为比较运算符操作数

子查询的相关性

非关联子查询

关联子查询

优化器的去关联优化

Semi-Join 去关联

物化解关联优化

subquery_to_derived 变换

长 IN 列表转 Semi-Join

子查询消除优化

消除场景

使用限制

验证与关闭

常见子查询场景

EXISTS 子查询

IN 子查询

标量子查询

相关文档
