SQL查询技巧：如何正确使用子查询定义动态范围？
WHERE 字段 IN (子查询) 才是正确写法，BETWEEN 不能嵌套子查询
先说一个常见的误区：很多开发者直觉上会想用 BETWEEN 配合子查询来筛选一个动态范围，比如 col BETWEEN (SELECT min_val...) AND (SELECT max_val...)。但实际情况是，在绝大多数主流数据库里，这种写法要么直接报语法错误，要么会产生非预期的语义。真正安全且通用的解决方案，其实就两种：当子查询返回的是一组离散值时，用 IN；当子查询明确返回单个最小值和最大值时，则拆分成 >= 和 <= 两个条件。
子查询返回单个最小值和最大值，用 >= 和 <=
如果你需要的是一个动态计算出的范围区间，比如“找出销售额在部门平均销售额上下10%浮动区间的订单”，那就必须把上下限拆成两个独立的标量子查询。这里的关键在于，每个子查询都必须确保只返回一行一列，否则数据库会直接抛出“子查询返回多行”的错误。
标准的写法示例如下：
SELECT * FROM orders WHERE amount >= (SELECT 0.9 * A VG(amount) FROM orders) AND amount <= (SELECT 1.1 * A VG(amount) FROM orders);
- 务必确保两个子查询都能独立运行，并且结果都是单一数值。一个良好的习惯是，在组装完整语句前，先单独执行这两个子查询验证一下。
- 这种标量子查询的用法在 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时需要注意结果集的大小。例如，Oracle 对 IN 列表有 1000 个元素的限制。PostgreSQL 和 MySQL 虽然没有硬性限制，但列表过长会显著影响性能。 - 如果子查询可能返回
NULL值，需要留意IN的语义：category_id IN (NULL, 1, 2)如果遇到 NULL，整个判断结果会是未知（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 <= (SELECT hi FROM range_bounds);
- CTE 的优势在于，其中的查询只执行一次，后续可以像引用普通表一样多次使用。这对于包含聚合函数或复杂关联的子查询来说，性能提升非常明显。
- 注意 CTE 的语法细节：定义时名称后面直接跟
AS，不要加括号（写成range_bounds()是错误的）。 - CTE 在 SQLite 3.8.3+、PostgreSQL 8.4+、SQL Server 2005+ 和 MySQL 8.0+ 中都得到了支持。对于不支持 CTE 的旧版本 MySQL，可以使用派生表（
FROM (SELECT ...) AS t）作为替代方案。
最后，分享一个实践中极易踩坑的细节：子查询的“单值性”校验。很多问题在测试阶段不会暴露，因为测试数据恰好只有一行。一旦上线后数据量变化，子查询意外返回多行，程序就会立刻报错。因此，写完查询后，务必单独执行一下用作边界值的子查询，确认它始终返回一个确定的值，而不是依赖运气。
