SQL CASE语句增量小于比较与BETWEEN算子的性能对比

结论先行 #

在修正为逻辑等价的前提下，两种写法的实际性能没有可观测的差异，执行计划一致就已经说明数据库底层执行的是完全相同的逻辑，不会因为你写的语法形式不同产生性能差别。

首先要注意的问题：两种写法逻辑不等价 #

你当前给出的两个SQL返回结果本身就不一样，优化的前提是逻辑等价，先明确差异点：

• 原写法的BETWEEN是左闭右闭规则，col=20会命中第一个BETWEEN 0 AND 20分支返回0；改写后的写法col=20不满足<20，会命中第二个分支返回20，结果完全不同。
• 原写法覆盖了0~10000的区间，小于0或者大于等于10000时走ELSE 0返回0；改写后的写法没有ELSE分支，col>=1000时会返回NULL，和原逻辑不符。

性能差异的疑问解答 #

你原本认为“单个数值比较比区间判断快”属于对数据库优化器的认知误区：
现在主流的MySQL、PostgreSQL、Oracle等关系型数据库的查询优化器，都具备条件等价化简的能力，在逻辑优化阶段就会把连续的BETWEEN区间判断、顺序的小于判断这两种等价逻辑，转换成同一种内部执行的条件判断结构，最终生成的执行算子完全一致，所以执行计划没有区别，实际运行时的CPU耗时差异也会在误差范围内，完全感知不到。

实际使用建议 #

1. 优先保证逻辑正确性，选择可读性更高、不容易出端点错误的写法即可，不需要在这种语法细节上做无收益的“微优化”。
2. 如果要真的优化这个查询的性能，更应该关注核心优化点：比如查询有没有过滤条件、col是否需要建索引、是否可以避免全表扫描等，这类优化的收益比调整CASE WHEN写法高几个数量级。

内容的提问来源于stack exchange，提问作者Amir