不能用BETWEEN是因为它会漏掉边界相接记录、受时区和类型转换干扰、无法被优化器识别为范围扫描；正确写法是显式用>=和
区间JOIN为什么不能用BETWEEN
用 BETWEEN 写区间匹配（比如 o.order_time BETWEEN p.valid_from AND p.valid_until）看似简洁，实际会漏掉边界相接的记录。例如价格区间是 [2025-01-01, 2025-01-31] 和 [2025-02-01, 2025-02-28]，订单时间刚好是 '2025-02-01'，BETWEEN 在某些时区或类型隐式转换下可能判定为不包含——尤其当字段是 TIMESTAMP WITHOUT TIME ZONE 且数据库做了夏令时偏移时。
更关键的是，BETWEEN 在多数数据库中无法被优化器识别为标准范围扫描条件，容易退化成全表扫描。真正可预测、可索引的写法是显式拆解交集逻辑：
o.order_time >= p.valid_fromo.order_time- 两个条件必须同时出现在
ON子句中，且字段非空（建议加AND p.valid_from IS NOT NULL AND p.valid_until IS NOT NULL）
如何让区间JOIN走索引（Sargable写法）
“Sargable”不是语法糖，而是指能让数据库用上索引的谓词形式。对区间JOIN来说，核心是：关联字段必须独立出现在比较操作符左侧，不能被函数、表达式或类型转换包裹。
常见反例：
-
DATE(o.order_time) = DATE(p.valid_from)→DATE()包裹导致索引失效 -
o.order_time >= p.valid_from::date→ 类型强制转换干扰索引选择 -
o.order_time >= COALESCE(p.valid_from, '1970-01-01')→COALESCE在部分旧版本 MySQL 中阻止索引下推
正确写法（以 PostgreSQL / MySQL 8.0+ / SQL Server 为主）：
- 确保
p.valid_from和p.valid_until上建联合索引，顺序必须是(valid_from, valid_until)—— 只有前导列valid_from能用于范围扫描 - 关联条件保持裸字段比较：
o.order_time >= p.valid_from AND o.order_time - 若字段可能为 NULL，优先在写入层清洗，而非查询时用
COALESCE补默认值；如必须处理，改用WHERE p.valid_from IS NOT NULL配合索引过滤
LEFT JOIN + 区间匹配容易触发笛卡尔爆炸
当左表每行要匹配右表中所有与其时间重叠的区间时，结果集规模是 O(n×m)，而非线性增长。典型症状是执行几秒就卡住、内存暴涨、返回几十万行却只想要“最匹配的一条”。
避免方式不是靠加 LIMIT，而是提前剪枝：
- 先用区间条件做基础 JOIN，再用窗口函数标序：
ROW_NUMBER() OVER (PARTITION BY o.order_id ORDER BY p.valid_from DESC)，外层取rn = 1 - 如果业务只要“生效时间最近的一条”，ORDER BY 推荐用
p.valid_from DESC（不是p.valid_until），因为起始时间更稳定、索引友好 - 不要在 LEFT JOIN 后加 WHERE 过滤右表字段，否则空匹配行会被剔除（等价于转成 INNER JOIN）；过滤必须写进 ON，例如
AND p.status = 'active'
跨数据库项目别碰OVERLAPS操作符
PostgreSQL 的 OVERLAPS 看起来省事：(o.order_time, o.order_time) OVERLAPS (p.valid_from, p.valid_until)，但它在 MySQL、SQL Server、Oracle 中完全不可用。
更麻烦的是它不透明：
- 底层按闭区间处理，对半开时间区间（如
[start, end)）易多连 1 秒，尤其在毫秒级时间戳场景下出错 - 无法利用索引加速，实测在百万级价格表上比手写范围条件慢 8 倍以上
- 执行计划里常出现 Seq Scan，且无法通过加索引改善
如果必须兼容多库，统一用显式交集逻辑，并在建表时约定字段类型：时间区间一律用 TIMESTAMP WITH TIME ZONE，避免夏令时隐式偏移引发的关联漂移。
