前阵子有个同事跑过来跟我说，他写了一个统计2024年1月订单量的SQL，WHERE条件里明明白白写着BETWEEN DATE'2024-01-01' AND DATE'2024-01-31'，结果31号白天产生的订单一条都没统计进去。我看了一眼说，这不是Oracle有毛病，是BETWEEN的边界没算清楚。
BETWEEN是大多数人学SQL时最早接触的操作符之一，语法简单到一句话就能说清——“在两值之间”。但越基础的语法，埋在下面的细节往往越多：闭区间怎么理解、日期边界怎么处理、字符串比较走的是字典序还是数字序、NULL值会不会被悄悄吞掉。这一期Oracle语句，专门把BETWEEN掰开揉碎讲一遍，从核心语义讲到日期、字符串、NULL还有执行计划，最后给出我自己常用的替代写法。正在学Oracle的同学可以完整过一遍，写了好几年SQL但偶尔被边界问题绊倒的开发、报表工程师和数据运维，建议重点看日期和NULL两章。
1. BETWEEN的核心语义：闭区间、三段式与三种类型的比较规则
1.1 先确认一个最容易出问题的前提：它是闭区间
BETWEEN的基本语法长这样：
expr BETWEEN low AND high它等价于：
expr >= low AND expr <= high也就是说，下限和上限本身都是包含在内的。BETWEEN 1 AND 5查出来的是1、2、3、4、5，不包含0也不包含6。这个“闭区间”三个字，就是后续所有边界问题的总根源。
很多人写SQL的时候会凭直觉认为“BETWEEN 1 AND 5”是1到5之间、不含两端，或者只含一端。这种误解在数字场景里影响小，因为数字边界好识别；但一到了日期和字符串场景，理解偏差就直接变成数据缺失或数据多余。
另一个新手容易困惑的点是语法里的这个AND。它是一个固定语法标记，不是逻辑运算符。所以别在BETWEEN里面塞col BETWEEN 1 AND (2 OR 3)这种写法，Oracle会直接报ORA-00907之类的语法错误。BETWEEN的两个边界只能是表达式，不能是逻辑判断。
1.2 三种数据类型的比较方式，表现完全不一样
BETWEEN虽然写法统一，但底层比较规则取决于参与比较的数据类型：
- 数字类型：最直观，
sal BETWEEN 2000 AND 3000就是字面意义的数值区间。 - 日期类型：Oracle的DATE包含年月日时分秒，比较时按内部数值比对。小到秒的差异都会影响边界结果，这是后面第2章的重头戏。
- 字符类型：按字符集的排序规则逐个字符比较，本质是字典序，跟数字大小没有任何关系。字符之间的大小顺序和数字序是两套完全不同的逻辑。
先看一个最基础的数字示例：
-- 查询工资在2000到3000之间的员工 SELECT empno, ename, sal FROM emp WHERE sal BETWEEN 2000 AND 3000; -- 完全等价的写法 SELECT empno, ename, sal FROM emp WHERE sal >= 2000 AND sal <= 3000;两者的执行计划和结果集理论上完全一致。既然语义等价，为什么还有人会刻意避开BETWEEN？因为“闭区间”三个字在日期和字符场景下很容易埋雷，后面章节会逐个拆。
2. 日期边界：BETWEEN查日历区间时那几类隐蔽缺口
2.1 带时间的DATE列，用纯日期做上界必然丢数据
回到开头那个案例。Oracle的DATE类型从设计上就带着时分秒，哪怕你插入的时候只给了2024-01-31，存进去的实际值是2024-01-31 00:00:00。而业务表里的订单时间，绝大多数是2024-01-31 14:32:18这种带时间的值。
当你写：
SELECT order_id, order_time FROM orders WHERE order_time BETWEEN DATE'2024-01-01' AND DATE'2024-01-31';这个条件的上界其实是2024-01-31 00:00:00。而2024-01-31 14:32:18 > 2024-01-31 00:00:00，所以这行记录不会被选中。
我做过不少报表问题的排查，这种“月末最后一天少数据”的现象，十次里有七次是BETWEEN的日期边界引起的。测试环境数据少，且大多是在零点前后批量插入，所以不太容易暴露；生产环境一旦有白天的实时数据进来，月报表、周报表就会平白无故缺一块。
通常有两类修正方式。第一类是把上界补到当天的最后一刻：
WHERE order_time BETWEEN DATE'2024-01-01' AND (DATE'2024-02-01' - INTERVAL '1' SECOND)第二类是我个人更推荐的写法，左闭右开：
WHERE order_time >= DATE'2024-01-01' AND order_time < DATE'2024-02-01';左闭右开的意思是包含1月1日零点，但不包含2月1日零点。这样整个1月所有时点的数据全部覆盖，而且不需要在脑子里反复计算“23:59:59”和“23:59:59.999”到底差多少。
2.2 TRUNC(SYSDATE)和SYSDATE组合出来的“假今天区间”
热搜词里出现了oracle中的trunc(sysdate)，这个确实和BETWEEN的日期坑高度相关。开发里最常见的需求之一是查“今天的数据”，很多人会写成：
SELECT * FROM orders WHERE create_time BETWEEN TRUNC(SYSDATE) AND SYSDATE;这个写法在白天运行看起来是正常的，比如下午3点执行，查出来的就是今天0点到下午3点的数据。但有两个隐患。
第一个隐患：如果这个SQL在凌晨0点整附近运行，TRUNC(SYSDATE)和SYSDATE几乎相等，BETWEEN就退化成等值判断，大概率什么都查不到。第二个隐患：这个查询的语义其实是“今天零点到当前时刻”，不是“今天全天”。如果业务上要的是“今天全天”，那今晚23点之后产生的数据就永远不会被包含进去，因为上界被锁死在了“执行那一刻”。
正确的“今天全天”写法应该是：
SELECT * FROM orders WHERE create_time >= TRUNC(SYSDATE) AND create_time < TRUNC(SYSDATE) + 1;TRUNC(SYSDATE) + 1就是明天零点，Oracle的日期加减直接按天计算，不用写INTERVAL '1' DAY那么啰嗦。用<而不是<=，也是为了保证明天零点整的数据不会算进今天。
2.3 月份统计的三种写法里，只有一种真正安全
统计某个月的订单，常见的写法有三种，我们逐个分析：
-- 写法一：字符串隐式转日期，风险很大 WHERE hire_date BETWEEN '20240101' AND '20240131'; -- 写法二：看着合理，仍然漏月末白天数据 WHERE hire_date BETWEEN DATE'2024-01-01' AND LAST_DAY(DATE'2024-01-01'); -- 写法三：推荐 WHERE hire_date >= DATE'2024-01-01' AND hire_date < DATE'2024-02-01';写法一依赖Oracle把'20240101'隐式转换成日期，转换规则受会话级的NLS_DATE_FORMAT参数影响。同一个SQL，在一个会话里正常，换个客户端就报ORA-01861“文字与格式字符串不匹配”，这种问题我遇到不止一次。
写法二用了LAST_DAY，从语义上看已经比直接写'20240131'严谨了，但LAST_DAY(DATE'2024-01-01')返回的是1月31日的零点，依然会漏掉1月31日白天产生的那一大批数据。
写法三的DATE'2024-02-01'是2月1日零点，配合<号，天然覆盖整个1月。这才是真正的“按自然月”统计，而且hire_date列没有被函数包裹，索引不会被废掉。
3. 字符串与隐式转换：BETWEEN在VARCHAR2列上的离奇表现
3.1 字典序不是数字序，VARCHAR2列上BETWEEN的结果常常反直觉
字符串的BETWEEN按字典序比较。所谓字典序，就是从头开始逐个字符比编码值，第一位能分出大小就结束，分不出就看下一位，如果某个字符串是另一个的前缀，短的更小。
看个具体例子：
WITH t AS ( SELECT '1' AS code FROM dual UNION ALL SELECT '10' FROM dual UNION ALL SELECT '2' FROM dual UNION ALL SELECT '9' FROM dual UNION ALL SELECT '100' FROM dual ) SELECT code FROM t WHERE code BETWEEN '1' AND '10';很多人的第一反应是“查1到10，那应该返回1和10”，但实际上结果集是1和10。为什么2不在？因为'2'和'10'比较时，首字符'2'大于'1'，所以'2' > '10'，自然不在[1, 10]这个字符串区间内。'9'同理。而'100'为什么不在？因为'10'是'100'的前缀，所以'100' > '10'。
如果业务代码里把数字存成了VARCHAR2，然后用BETWEEN做范围筛选，结果就会非常离谱。这也是很多历史遗留系统里“明明数据存在，SQL就是查不出来”的原因之一。遇到这类情况，第一反应应该是确认列类型，再看比较规则，而不是盯着数据发呆。
3.2 列类型不一致时，ORA-01722只是表象
热搜词里有一条是“oracle 过滤不可转为数字的字符串”，和这个问题正好对上。假设某个表的主键id是VARCHAR2类型，里面混了'1001'、'NR-2024-001'这种非数字值。你写：
SELECT * FROM t_material WHERE id BETWEEN 1001 AND 1010;Oracle在比较VARCHAR2列和数字字面量时，会优先把字符串转成数字，因为数字类型的优先级更高。这会导致两个问题：一是列被隐式转换后无法正常走B树索引，查询性能退化；二是只要表里有一行id不是合法数字，整个查询直接报ORA-01722: invalid number，而不是跳过那行。
有人会想到把字面量改成字符串来避免报错：
SELECT * FROM t_material WHERE id BETWEEN '1001' AND '1010';这种方式确实不会报错了，但走进了3.1里说的字典序陷阱。'10010'、'109'这类值可能会混进来，而'2'这种“看起来在1到10之间”的值反而查不出来。
正确的思路是先确认业务到底需要什么。如果业务上这个字段本来就是数字，最该做的是改表结构，而不是在SQL里绕来绕去。如果结构动不了，至少用VALIDATE_CONVERSION（Oracle 12.2+）先把非数字行过滤掉：
SELECT * FROM t_material WHERE VALIDATE_CONVERSION(id AS NUMBER) = 1 AND TO_NUMBER(id) BETWEEN 1001 AND 1010;写SQL的时候，最怕的不是数据脏，而是类型系统把“脏数据”的问题掩盖成了“执行报错”或者更隐蔽的“结果缺失”。所以我一直建议大家，遇到ORA-01722先别急着查数据，先看是不是类型不匹配触发了隐式转换。
3.3 判断“包含”别往BETWEEN上靠
热搜词里还有一条是“oracle判断字符串是否包含某个字符串”。判断字符串包含关系，标准做法是LIKE或INSTR，比如查地址里有没有“朝阳路”：
SELECT * FROM customer WHERE INSTR(address, '朝阳路') > 0; -- 或者 SELECT * FROM customer WHERE address LIKE '%朝阳路%';BETWEEN解决的是“范围”问题，LIKE和INSTR解决的是“模式匹配/子串定位”问题。两者底层机制完全不同。BETWEEN不认%、_这些通配符，也不具备“从中间找子串”的能力。写WHERE address BETWEEN '%A%' AND '%B%'不仅查不出想要的结果，还会按字典序把一批无关的字符串拉进来，这个用户很容易自己把自己绕晕。
4. NULL与NOT BETWEEN：反向筛选为什么总会漏掉NULL行
4.1 任何值和NULL做比较，结果都是UNKNOWN
SQL的布尔逻辑是三值逻辑：TRUE、FALSE、UNKNOWN。任何值与NULL做比较，结果都不是TRUE也不是FALSE，而是UNKNOWN。WHERE子句只保留结果为TRUE的行，UNKNOWN和FALSE的行都会被过滤掉。
所以下面这条SQL查出来的结果永远是空：
SELECT * FROM emp WHERE sal BETWEEN NULL AND 3000;很多人会直觉认为“NULL没填，那就跳过这个条件，查所有工资小于3000的”，但数据库不是这么理解的。数据库看到的是sal >= NULL，而这个表达式无法判定真假，结果就是UNKNOWN，整行被丢弃。
这个坑在应用传参的场景里特别常见。页面上的开始日期没填，程序往SQL里传了个NULL，语句变成BETWEEN :start_date AND :end_date，最终结果就是空集，排查半天以为数据出问题了，其实是空值参与比较导致的。
4.2 NOT BETWEEN不等价于“补集”，NULL行会被吞掉
NOT BETWEEN看起来是BETWEEN的补集，但因为它等价于：
col < low OR col > highNULL行会因为col < low为UNKNOWN、col > high也为UNKNOWN，OR之后还是UNKNOWN，最终照样被过滤掉。
实际案例非常常见。比如查“年龄不在18到60岁之间”的员工，想找出未成年和退休人员：
SELECT * FROM employee WHERE age NOT BETWEEN 18 AND 60;这个查询会把age为NULL的员工全部丢掉。如果拿BETWEEN的结果和NOT BETWEEN的结果加起来对总数，你会惊讶地发现合计数比全表记录少了一截。业务方来质问的时候，你得解释清楚：不是SQL写错了，是NULL本来就不参与任何一边。
真正的补集写法需要显式带上NULL：
SELECT * FROM employee WHERE age NOT BETWEEN 18 AND 60 OR age IS NULL;要不要带age IS NULL，取决于业务口径：NULL到底算“年龄未知”还是“年龄不在区间”。这种口径问题建议在写报表SQL之前先问清楚，别自己替业务方拍板。
4.3 存储过程里的NULL入参：NVL兜底和动态SQL怎么选
热搜词里提到了“oracle存储过程”，说明不少人在PL/SQL里也遇到了同样的NULL问题。存储过程接收范围参数时，调用方经常传NULL进来，尤其是从Web层直接透传的查询条件。
我常用的兜底方式是NVL给默认区间：
CREATE OR REPLACE PROCEDURE p_query_emp( p_min_sal IN NUMBER DEFAULT 0, p_max_sal IN NUMBER DEFAULT 1000000 ) AS v_min_sal NUMBER := NVL(p_min_sal, 0); v_max_sal NUMBER := NVL(p_max_sal, 9999999); BEGIN FOR r IN ( SELECT ename, sal FROM emp WHERE sal BETWEEN v_min_sal AND v_max_sal ) LOOP DBMS_OUTPUT.PUT_LINE(r.ename || ':' || r.sal); END LOOP; END; /但这里有一个隐含问题：如果表里存在比默认最大值还大的数据，兜底值会掩盖查询条件的错误。所以NVL兜底只适合业务上能明确“表格内数值不会超出默认范围”的场景。如果范围不可控，更稳妥的做法是动态SQL——条件传了才拼进WHERE，没传就不拼：
v_sql := 'SELECT ename, sal FROM emp WHERE 1 = 1'; IF p_min_sal IS NOT NULL THEN v_sql := v_sql || ' AND sal >= :min_sal'; END IF; IF p_max_sal IS NOT NULL THEN v_sql := v_sql || ' AND sal <= :max_sal'; END IF;动态SQL要记得用绑定变量，别直接拼字符串，先不说安全风险，绑定变量还能让SQL复用游标，减少硬解析开销。
5. 执行计划与替代写法：什么时候该把BETWEEN换掉
5.1 区间大小决定执行计划：从INDEX RANGE SCAN到FULL TABLE SCAN
BETWEEN在索引列上，优化器通常会生成INDEX RANGE SCAN。但要注意，这只是“通常”。如果区间过大，比如sal BETWEEN 1 AND 9999999，估算行数接近全表，优化器会觉得走索引再回表的成本高于全表扫描，于是选择TABLE ACCESS FULL。
这个现象提醒我们：BETWEEN不是写得对就够了，还要考虑区间选择性。排查性能问题时，可以先用EXPLAIN PLAN看一眼：
EXPLAIN PLAN FOR SELECT * FROM emp WHERE sal BETWEEN 2000 AND 3000; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);如果执行计划里出现FULL TABLE SCAN，而业务上这个区间本来应该很窄，那通常说明统计信息过期，或者索引被函数包裹了。
5.2 函数包裹列，BETWEEN再标准也白搭
这一类问题在日期字段上最典型：
WHERE TRUNC(create_time) BETWEEN DATE'2024-01-01' AND DATE'2024-01-31'create_time被TRUNC函数包裹后，B树索引就失效了，因为索引里存的是原始值，不是TRUNC之后的值。改写方式我之前已经提过，把函数从列上挪到边界值上：
WHERE create_time >= TRUNC(SYSDATE) AND create_time < TRUNC(SYSDATE) + 1这条SQL的create_time没有被函数包裹，索引可以正常使用。
前面讲的VARCHAR2列和数字字面量比较也有同样问题。Oracle隐式执行了TO_NUMBER(id)，等于给列套了函数，索引作废。检查执行计划的谓词部分时，如果看到TO_NUMBER("ID")这种字样，基本可以断定索引失效。
5.3 日期字段强烈推荐用 >= 和 < 替代 BETWEEN
说了这么多，你可能也看出来了，我对日期字段的建议很明确：能用左闭右开，就别用BETWEEN。原因有三点。
一是语义精确。>= DATE'2024-01-01' AND < DATE'2024-02-01'明确表达了“1月1日零点起，2月1日零点止”，不需要去算月末最后一天的23:59:59到底够不够覆盖全部时点。
二是对高精度类型更安全。Oracle的TIMESTAMP支持小数秒，如果业务里用的是TIMESTAMP(9)，你写23:59:59.999依然可能漏掉最后一个纳秒的数据。< 下一天零点则彻底没有这个烦恼。
三是可读性更好。后续接手的同事一看两个边界就明白统计的窗口，不会再去翻业务文档确认“23:59:59”到底包不包含秒。
5.4 ROWNUM分页不要用BETWEEN接缝
热搜词里有“oracle分页”，这个多少和BETWEEN有点关系，因为有不少人试过用ROWNUM BETWEEN ... AND ...做分页：
SELECT * FROM emp WHERE ROWNUM BETWEEN 11 AND 20;这条SQL查不到任何行。原因是ROWNUM是伪列，它在行被取出来之后、排序和过滤之前按1递增分配。第一行进来，ROWNUM先变成1，然后判断1 BETWEEN 11 AND 20为假，行被丢弃；第二行再进来时，ROWNUM还是从1开始，所以永远到不了11。那个常见的“ROWNUM > 10查不到数据”的问题，本质也一样。
正确做法是先用子查询把最大行号取够，外层再裁掉前N行：
SELECT * FROM ( SELECT emp.*, ROWNUM AS rn FROM emp WHERE ROWNUM <= 20 ) WHERE rn >= 11;12c及以上的版本可以直接用OFFSET FETCH：
SELECT * FROM emp OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;这里要给BETWEEN正个名：ROWNUM分页的问题出在ROWNUM伪列的分配机制上，不是BETWEEN本身有什么错。但这个故事说明一个道理——写SQL前必须搞清楚数据到底是怎么流动的，边界条件是不是符合数据本身的特性。
我自己这些年维护统计报表，已经把“日期用左闭右开、字符区间先确认类型、凡是范围条件都考虑NULL、写完顺手看执行计划”固化成了固定习惯。这套习惯让我在数据核对和性能问题上少踩了很多坑，也省了不少跟业务方解释“为什么少了数据”的功夫。BETWEEN本身并没有问题，数字区间我照样用得顺手，但理解它背后的比较规则和边界行为，才是让它真正为你所用的前提。
