between 不支持左右边界为独立子查询，正确写法是用 >= 和 = (select 0.9 avg(amount) from orders) and amount
WHERE 字段 IN (子查询) 才是正确写法，BETWEEN 不能嵌套子查询
直接说结论：BETWEEN 语法不支持左右边界用两个独立子查询（比如 col BETWEEN (SELECT min_val...) AND (SELECT max_val...) 在多数数据库里会报错或语义错误）。真正能安全、通用实现“字段值落在子查询返回的某个范围区间内”的方式，是用 IN（当子查询返回离散值列表时），或改用 >= 和 分别搭配单值子查询（当子查询明确只返回一行一列时）。
子查询返回单个最小值和最大值，用 >= 和
如果你的子查询本意是获取一个动态范围（例如“查出销售额在部门平均值±10%区间内的员工”），就必须把上下界拆成两个独立标量子查询，并确保每个子查询都严格返回**一行一列**，否则会报错 Subquery returns more than 1 row。
常见写法示例：
SELECT * FROM orders WHERE amount >= (SELECT 0.9 * AVG(amount) FROM orders) AND amount
- 两个子查询必须结构一致、可独立执行；建议先单独运行验证是否真只返回一个数字
- MySQL / PostgreSQL / SQL Server 都支持这种标量子查询用法；Oracle 要求用
(SELECT ... FROM DUAL)包裹（但此处通常不需要） - 性能上，这两个子查询可能被重复执行；如需优化，可改用 CTE 或 JOIN（见下一条）
子查询返回多行离散值，必须用 IN，不是 BETWEEN
如果子查询结果是一组具体值（比如“查出所有属于活跃品类的商品”，而活跃品类 ID 存在另一张表里），IN 是唯一合理选择。此时用 BETWEEN 完全无法表达语义。
错误示范（语法可能通过，但逻辑错）：
-- ❌ 错误：BETWEEN 期待两个标量，不是一列值 SELECT * FROM products WHERE category_id BETWEEN (SELECT id FROM categories WHERE status = 'active');
正确写法：
SELECT * FROM products WHERE category_id IN (SELECT id FROM categories WHERE status = 'active');
-
IN子查询最多返回 1000 个值（Oracle 限制）；PostgreSQL 和 MySQL 无硬限制但性能随数量下降 - 若子查询可能返回
NULL，IN会整体判为未知（UNKNOWN），导致整行被过滤掉；可用category_id IS NOT NULL AND category_id IN (...)显式规避 - SQL Server 中大结果集建议改用
EXISTS或临时表提升性能
想复用子查询结果？优先考虑 CTE 或派生表
当上下界计算复杂或需要多次引用（比如同时用于过滤和排序），硬写两个相同子查询不仅难维护，还可能被重复执行。CTE 是最清晰的解法。
WITH range_bounds AS ( SELECT MIN(salary) AS lo, MAX(salary) AS hi FROM employees WHERE dept = 'tech' ) SELECT name, salary FROM employees WHERE salary >= (SELECT lo FROM range_bounds) AND salary
- CTE 只执行一次，后续引用不重复计算 —— 这点在子查询含聚合或关联时特别关键
- 注意 CTE 名称后不能加括号（如
range_bounds()是错的） - SQLite 3.8.3+、PostgreSQL 8.4+、SQL Server 2005+、MySQL 8.0+ 均支持标准 CTE；老版本 MySQL 只能用派生表（
FROM (SELECT ...) AS t）
真正容易被忽略的是子查询的“单值性”校验 —— 很多人测试时数据刚好只有一行，上线后数据变化就突然报错。写完务必用 SELECT 单独跑一遍子查询，确认它稳定返回一个值，而不是靠运气。
