Oracle基础查询易错点全解析：从LIKE到分页的实战指南

1. 基础查询关键词为什么还需要特意补充很多人觉得 Oracle 的基础查询就是SELECT ... FROM ... WHERE ...好像没什么好讲的。但我在实际开发和运维中见过太多“基础用法”翻车的案例——明明语句不长逻辑也不复杂结果一执行要么报ORA-00942要么结果集跟预期完完全全不一样还有些慢查询慢到让人怀疑数据库要挂了。说到底基础查询关键词背后的执行规则、NULL 处理机制、隐式转换陷阱才是真正决定你 SQL 写得好不好的分水岭。这一期作为“Oracle 语句”系列的第 23 期重点不是把SELECT的语法抄一遍而是把那些最常用、最容易忽略、踩过无数次坑的细节补全。内容围绕条件过滤、去重排序、日期字符串处理、子查询和集合操作展开每一块都配上实战中的教训和排查思路。适合刚看完 Oracle 入门教程、准备写业务报表的新手也适合写过一段时间 SQL 但总被边界情况折磨的开发同学。读完你能直接把这些规则用到自己的查询里少走弯路。2. 条件过滤中的易错点LIKE、IN、BETWEEN、IS NULL2.1 LIKE 模糊匹配的细节转义与下划线LIKE可能是最被低估的关键词。很多人只会用%关键字%往两边加百分号但遇到一个很实际的问题就懵了用户搜索的内容里正好包含%或者_怎么办比如要查包含“100%纯棉”的商品名称直接写WHERE name LIKE %100%纯棉%结果会把“100abc纯棉”也捞出来因为%在 LIKE 里是任意多个字符的通配符。这时候必须用ESCAPE指定转义字符Oracle 默认没有全局转义符得自己声明。最常见的写法是SELECT * FROM product WHERE name LIKE %100\%纯棉% ESCAPE \;这里\%表示一个普通的百分号。同理如果要匹配下划线本身也要转义。还有一个细节是大小写问题Oracle 默认比较时是区分大小写的LIKE %Oracle%查不到oracle。如果业务不区分大小写要么用UPPER(列) LIKE UPPER(%oracle%)要么在建表时用NLS_UPPER或相关排序规则。不过要注意在列上套UPPER函数一般会阻断普通索引的使用。在 Oracle 上常规做法是建函数索引否则就要接受全表扫描。这是我给业务方写查询时最常提醒的一个点。另外LIKE的搜索条件如果以%开头即使列上建了 B 树索引也用不上这点在后面的慢查询部分还会展开。如果是LIKE abc%则有机会走索引范围扫描前提是列上确实有索引且基数合适。理解这个差异对优化查询很有帮助。2.2 IN、BETWEEN 的边界陷阱IN看起来很直观但它本质上是多个OR的缩写。有一个经典问题WHERE col NOT IN (1, 2, NULL)。这条语句不会返回任何行哪怕表里有很多不是 1、不是 2 的行。原因是 SQL 的三值逻辑与NULL做比较的结果不是TRUE也不是FALSE而是UNKNOWN只有TRUE的行才会被 where 条件选出。所以col 1和col NULL同时成立才选结果永远是UNKNOWN一行都选不出来。这也是为什么我会在团队规范里明确要求生产环境的 WHERE 条件中NOT IN后面不要出现可能为 NULL 的子查询也不要手动写 NULL 值。如果真的需要排除空值改成NOT EXISTS更安全因为EXISTS对于非空判定不敏感。BETWEEN的边界是包含两端的即BETWEEN 10 AND 20等价于col 10 AND col 20。这个大家知道但容易忽略的是日期场景。如果我们写WHERE create_date BETWEEN 2024-01-01 AND 2024-01-31如果create_date是DATE类型且包含时分秒那么它的值其实是2024-01-31 00:00:00到2024-01-31 23:59:59而我们的条件只在 1 月 31 日零点整做了上界这样 1 月 31 日当天几乎所有的数据都进不来。正确的写法一般是左闭右开create_date DATE 2024-01-01 AND create_date DATE 2024-02-01。这个错误在月报场景里几乎每周都能碰到值得所有写过滤条件的人警惕。还有一个小细节IN列表里的元素是允许为空的col IN (1, NULL)等价于col 1 OR col NULL所以它只会返回col 1的行。注意区分COL IN (1,NULL)和COL NOT IN (1,NULL)后者结果为空前者还能用。只要记住 SQL 的 NULL 不参与等值匹配这类问题就不会踩坑。2.3 与 NULL 打交道的正确姿势Oracle 里NULL不是空字符串也不是 0它表示“未知值”。判断 NULL 不能写 NULL得用IS NULL或IS NOT NULL。这是一个初学者都会背的规则但实际查询里容易忘的是在CASE WHEN和聚合函数中处理 NULL。聚合函数会忽略 NULL比如COUNT(列)只统计非空值而COUNT(*)统计所有行。如果需要统计某列值为空的个数不能直接COUNT(col)应该写成COUNT(*) - COUNT(col)或者SUM(CASE WHEN col IS NULL THEN 1 ELSE 0 END)。这个差异在校验数据质量时特别常见我在导出数据质量报告时踩过一次最后排查了很久才发现是 COUNT 的语义问题。还有函数NVL(col, 0)可以把 NULL 替换成 0适合做计算前兜底。但注意NVL的类型兼容问题NVL(amount, 0)如果amount是字符串类型Oracle 会尝试把0隐式转换为字符串结果就是0如果amount是日期NVL第二个参数得写日期。用COALESCE更灵活一些它会从左到右取第一个非 NULL 值支持多个参数且不必所有参数类型完全一致但也要在可隐式转换范围内。对于复杂判断建议写CASE WHEN ... ELSE ... END可读性也更好。3. 数据去重与排序DISTINCT 和 ORDER BY 的隐藏规则3.1 DISTINCT 并非万能去重键与排序键的关系SELECT DISTINCT col1, col2 FROM table会把col1和col2组合起来去重这是大家都会的。但有几个隐藏规则需要注意。第一个是DISTINCT后跟ORDER BY时的列限制。在 Oracle 中ORDER BY后面的列必须出现在SELECT列表中否则会报ORA-01791: not a SELECTed expression。比如下面这句会报错SELECT DISTINCT dept_id FROM emp ORDER BY hire_date;因为hire_date没有出现在 select 列表中而去重后的结果里同一个dept_id可能有多条不同hire_date数据库不知道按哪个日期排序。如果要按某个字段排序就必须把这个字段也放进 select 列表或者改用GROUP BY加聚合函数。这是很多写报表的人第一次遇到就会愣住的问题但理解了语义之后就通了。第二个是DISTINCT会隐式排序。在旧版本里DISTINCT经常通过 sort unique 操作实现结果集看起来是有序的。但这不能依赖因为执行计划可能随着数据量和统计信息变化而改变。如果你需要明确顺序仍然要写ORDER BY。另外DISTINCT的成本可能很高如果只是去重但不在乎顺序可以考虑GROUP BY替代。语法不同但语义在特定场景下等价执行计划也可能不同。第三个是COUNT(DISTINCT column)在统计唯一值数量时非常有用但 Oracle 对这个操作的性能不算友好数据量大时会很慢。一个经验做法是如果只是想知道“大概”有多少种值可以用APPROX_COUNT_DISTINCT12c 以上误差很小但速度快很多。适合在大表做初步数据探查时用。3.2 ORDER BY 的 NULL 排序与多列排序排序时Oracle 默认把NULL视为比非 NULL 值更大。所以升序排列时 NULL 排最后降序时 NULL 排最前。这个默认行为跟很多开发者的直觉相反比如业务方希望按“更新时间倒序”取最新记录如果某些行的更新时间为 NULL用默认降序这些 NULL 反而跑到最前面了用户看到一堆没有时间的数据就会觉得排序有问题。解决办法是明确写NULLS LAST或NULLS FIRST。例如SELECT id, update_time FROM audit_log ORDER BY update_time DESC NULLS LAST;这句话表达的意思是更新时间大的排前面没有更新时间的放最后。这也是我在项目规范里固定下来的写法任何 ORDER BY 中只要存在可能为 NULL 的列必须显式指定 NULLS 策略不能依赖数据库默认行为。多列排序时要理解语句从左到右的优先级先按第一列排第一列相同再按第二列排。如果第二列也想倒序得分别写DESC。很多人会写ORDER BY col1, col2 DESC误以为两个都是倒序其实只有 col2 是倒序。要两个都倒序应该是ORDER BY col1 DESC, col2 DESC。这个细节虽然看起来小但在多条件排序的报表中直接决定结果是否正确。3.3 配合 ROWNUM 实现分页查询Oracle 在 12c 之前没有LIMIT分页靠ROWNUM。但ROWNUM是查询结果生成后指定的伪列它在 where 条件过滤之前就会被赋值这就导致一个经典问题WHERE ROWNUM 5永远查不出数据因为第一条行的ROWNUM固定为 1不满足条件就被丢弃下一条行仍然是 1于是永远也得不到大于 5 的编号。同样地ORDER BY如果在ROWNUM之后执行取到的前 N 行是排序前的行根本不是你要的“前 N 大”。正确分页姿势是把排序先做进子查询再在外层用ROWNUM控制范围。比如取第 11 到第 20 条SELECT * FROM ( SELECT t.*, ROWNUM rn FROM ( SELECT emp_id, salary FROM emp ORDER BY salary DESC ) t WHERE ROWNUM 20 ) WHERE rn 10;这里最内层先做排序第二层先限制最大行数并给每行编号rn外层再用rn 10切出后半段。这个三级嵌套模式看着绕但只要理解了ROWNUM的赋值时机就不会写错。12c 之后有OFFSET ... ROWS FETCH NEXT ... ROWS ONLY语法更简单但底层有时还是会做ROWNUM类似的转换。分页查询还有一个重要原则排序列必须唯一或者至少稳定否则分页时会出现数据重复或遗漏。我自己就遇到过按薪水排序分页多条记录薪水相同第二页出现了第一页已经显示的行。后来把主键加到 ORDER BY 的最后一列才彻底解决。这个经验我几乎每次分享分页都会提。4. 日期与字符串处理让查询更稳妥4.1 TO_DATE/TO_CHAR 与 TRUNC(SYSDATE) 的常见坑日期的查询是基础中的基础但出错率极高。一个常见的坑是TO_DATE的格式字符串必须完全匹配输入的字符。比如TO_DATE(2024-02-30, YYYY-MM-DD)会报无效日期但TO_DATE(2024-02-29, YYYY-MM-DD)在闰年合法非闰年报错。更隐蔽的问题是YYYY和RRRR的差异TO_DATE(24-01-01, RRRR-MM-DD)在 Oracle 中会把 0-49 年转成 2000 年代50-99 转成 1900 年代而YYYY则严格按四位理解。如果输入是两位年用RRRR更符合人类直觉但代码里如果写死YYYY遇到 49 和 50 就会发现结果差了一百年。处理SYSDATE时TRUNC(SYSDATE)是很多 DBA 习惯用的它能把当时时间直接截断到当天零点。但这个函数如果在查询列上使用就要小心WHERE TRUNC(hire_date) TRUNC(SYSDATE)这种写法如果hire_date有索引左边套了函数就无法走索引。更好的做法是使用范围条件hire_date TRUNC(SYSDATE) AND hire_date TRUNC(SYSDATE) 1。这样既保留了索引可用性又能把当天所有时刻的记录都捞进来。这个优化思路对很多“按天统计”的查询都适用。还有一个容易被忽略的默认格式问题。如果直接写WHERE create_date 2024-01-01Oracle 会按照会话的NLS_DATE_FORMAT做隐式转换。如果你的日期格式设置是DD-MON-YYYY这条语句可能直接报错或者得到错误结果。最稳妥的做法是显式使用DATE 2024-01-01Oracle 9i 以上支持或TO_DATE(2024-01-01,YYYY-MM-DD)永远不要依赖隐式转换。4.2 判断字符串是否包含INSTR 还是 LIKE在 Oracle 中判断某个字符串是否包含另一个字符串有两种常见写法LIKE %关键字%和INSTR(列, 关键字) 0。两者在功能上大多数场景等价但细节不同。INSTR返回子串首次出现的位置找不到返回 0。它的一个优势是不会把%和_当通配符搜索文本里有这些字符时不需要转义。比如要查找包含“百分号”的记录INSTR写起来更干净。性能方面LIKE %关键字%由于左侧通配符存在B 树索引基本用不上。INSTR(列, 关键字)如果列上没有函数索引也是全表扫描。所以从这点看两者半斤八两。但如果搜索的是前缀比如LIKE abc%它可能走索引范围扫描性能不错而INSTR(col, abc) 0则很难用索引。所以我的习惯是只要业务上能确认是前缀匹配优先用LIKE abc%配合索引如果必须是包含匹配且数据量大干脆考虑全文索引或CONTAINS或者借助ORACLE TEXT而不是在业务 SQL 里硬扛。判断是否包含还有一种做法是用REGEXP_LIKE比如正则匹配模式abc但它更灵活也相对更耗资源简单包含场景没必要上正则。另外INSTR和LIKE对字符集大小写的敏感程度是一致的都是精确匹配。如果忽略大小写UPPER(列) LIKE UPPER(%abc%)或者用REGEXP_LIKE(列, abc, i)。注意REGEXP_LIKE的第三个参数可以传i表示忽略大小写这是 Oracle 正则的一个便利点。4.3 过滤不可转为数字的字符串正则与 VALIDATE_CONVERSION在数据清洗场景里我们经常遇到一列 VARCHAR2 字段里混着数字和非数字字符想要过滤出那些可以转成数字的记录。比如一个batch_no列理论上应该是流水号但历史数据有脏值我们需要写查询“只看是纯数字的行”。最常用的做法是正则SELECT batch_no FROM table WHERE REGEXP_LIKE(batch_no, ^[0-9]$);这个写法匹配整串都是数字的记录如果字段是 123a45 就会被过滤掉。要注意^和$表示整段匹配不能少。如果允许负数或小数还要把规则改成^[-]?[0-9](\.[0-9])?$。另外正则表达式在数据量大时比较费 CPU但因为这里本身需要逐行判断也没有更好的索引方案。Oracle 12.2 开始提供了更直接的函数VALIDATE_CONVERSION它的用法是SELECT batch_no FROM table WHERE VALIDATE_CONVERSION(batch_no AS NUMBER) 1;这个函数不会抛错返回 1 表示能转换返回 0 表示不能。比起正则它更理解 Oracle 内部数字格式对于科学计数法、前后空格等处理更自然。但它同样不能走普通索引只能算表达式。我在处理历史数据质量报告时就用这个函数快速统计了不可转数字的占比比正则写起来省心很多。如果数据库版本不支持VALIDATE_CONVERSION也可以写一个不带异常处理的技巧TRANSLATE(batch_no, 0123456789, ) IS NOT NULL之类的来判断纯数字但可读性差不推荐。核心思想是先把数字字符替换掉如果剩下的内容为空说明全数字。不过这种方法对负号和小数点无能为力通常只用于正整数的简单清洗。还是那句话能用官方函数就别自己造轮子。5. 子查询与集合操作基础查询的高阶补充5.1 子查询的三种位置与性能心态子查询可以出现在SELECT列表中用于标量计算出现在FROM子句中作为一个派生表出现在WHERE子句中配合IN、EXISTS等使用。这三个位置虽然语法上都叫子查询但语义和性能差异很大。SELECT列表里的标量子查询要求返回值是单行单列否则会报ORA-01427: single-row subquery returns more than one row。它适合做“一对一”的补充计算比如SELECT emp_id, (SELECT dept_name FROM dept d WHERE d.dept_id e.dept_id) AS dept_name FROM emp e;这种写法非常方便但要注意如果外层表数据量大每一行都要执行一次子查询性能可能很差。更优方案是把子查询改成JOIN让数据库一次处理完。FROM子查询也可以叫内联视图它必须有自己的别名。比如前面分页的三层嵌套中间层就是FROM子查询。内联视图的列可以直接用于外层过滤但注意 Oracle 优化器可能会对子查询进行视图合并导致你所“以为”的执行顺序和实际并不一样。好在大多数情况下合并是有利的不需要刻意阻止。WHERE子查询中IN和EXISTS是永恒的话题。一个经验是如果子查询结果集很小外层表很大用IN通常不错如果子查询结果很大外层是驱动表EXISTS更适合。但这只是直觉真正还是要看统计信息和执行计划。还有一个语义区别NOT IN如果子查询返回任何 NULL 行结果为空而NOT EXISTS不受 NULL 影响。所以业务上如果用“不存在某种记录”的语义我总是建议直接写NOT EXISTS避免 NULL 陷阱。5.2 集合操作 UNION/UNION ALL/MINUS 的取舍把两个查询结果合并起来UNION和UNION ALL是最常用的。UNION会去重并默认做一次排序UNION ALL直接拼接结果不去重也不排序。很多业务场景其实不需要去重比如按条件分片取数两个查询结果天然不会重复这时应该用UNION ALL省掉去重这一步能明显减少临时表和排序的消耗。我见过有人习惯性写UNION结果两张十万行表合并后被去重卡了几秒改成UNION ALL立刻快到毫秒级。不能说UNION一定慢但在不需要去重时它确实是在做无用功。使用集合操作有一个硬性要求两侧查询的列数和数据类型必须匹配。Oracle 要求两侧对应列的数据类型可隐式转换而且结果集的列名来自第一个查询的列名。如果两侧列数不一致直接报ORA-01789。另外ORDER BY只能放在整个集合操作的最后不能写在第一个查询里否则会报错误。如果要对结果排序可以在ORDER BY中使用第一个查询的列名或者排序位置。MINUS用于求差集INTERSECT用于求交集。这两个操作同样会去重并排序。比如要找出 A 表有而 B 表没有的 ID用SELECT id FROM a_table MINUS SELECT id FROM b_table。注意这里结果集是去重后的如果 a_table 里同一个 id 出现多次结果中只会出现一次。如果想保留重复且不排序没有直接的集合操作需要把两个结果用LEFT JOIN或者用ROW_NUMBER来实现。分析这些“隐形”的去重行为才能避免在数据统计时算错数量。集合操作还有一个与 NULL 相关的注意点。UNION去重时会把两个 NULL 视为相同的值所以它们只保留一个。这在统计去重后的 NULL 数量时没什么问题但如果你期望保留每一处的 NULL就必须用UNION ALL。理解集合操作的“默认去重”语义能让你的查询更可控。6. 实操经验慢查询与错误排查实录6.1 我踩过的三类基础查询坑第一类坑是隐式类型转换。有一次同事写WHERE order_no 123456order_no列是 VARCHAR2 类型。Oracle 在比较时会尝试把列的值转成数字去和数字常量比较相当于在列上应用了TO_NUMBER(order_no)结果索引失效全表扫描。更危险的是如果列里有非数字字符转换过程会直接抛ORA-01722: invalid number。所以一定要让字段类型和常量类型保持一致最简单的方式是写成WHERE order_no 123456。凡是发现执行计划里出现TO_NUMBER或TO_DATE在索引列上的都是这类问题。第二类坑是ROWNUM与ORDER BY的顺序。我见过一个分页报表外层先取ROWNUM 100里层才排序结果页面数据忽前忽后。一查执行计划果然排序发生在ROWNUM之后。后来我改成了三层嵌套才稳定下来。这个教训也写进了团队的 SQL 规范。第三类坑是NOT IN子查询返回了 NULL。有一个权限查询要找出“不在黑名单用户”里的用户黑名单表里有一条用户号是空的记录结果整个查询返回 0 行。排查时我一眼就怀疑NOT IN改成NOT EXISTS后立刻正常。从那以后我再也不敢在同事的代码里看到裸NOT IN而不加注解了。排查思路也很简单先单独执行子查询看有没有 NULL如果有再用EXISTS重写。6.2 常用基础查询关键词速查表这里把这一期提到的关键词和容易踩的坑整理成一张表方便你写 SQL 时对照自查。关键词/场景正确用法关键提醒LIKE 模糊匹配LIKE %abc%%和_是通配符需要匹配它们时用ESCAPEIN / NOT INcol IN (1,2)子查询里有 NULL 时NOT IN结果为空优先用NOT EXISTSBETWEENcol BETWEEN 10 AND 20包含边界日期注意时间部分建议用左闭右开区间IS NULLcol IS NULL不能用 NULL判断DISTINCTSELECT DISTINCT a,b去重后如果ORDER BY列不在 SELECT 中会报错ORDER BYORDER BY col DESC NULLS LAST多列排序每个列要单独指定方向NULL 排序要显式控制ROWNUM 分页三层嵌套写法切忌直接ROWNUM N排序必须在 ROWNUM 之前TRUNC(SYSDATE)hire_date TRUNC(SYSDATE)避免在索引列上套 TRUNC使用范围条件判断字符串包含INSTR(col,abc) 0或LIKE %abc%前缀匹配优先用 LIKE abc% 并创造索引机会过滤非数字REGEXP_LIKE(col,^[0-9]$)或VALIDATE_CONVERSION(col AS NUMBER)1版本低于 12.2 只能用正则或表达式注意 CPU 消耗子查询位置不同性能不同标量子查询只允许单行单列FROM 子查询必须加别名UNION / UNION ALL不需要去重时用 UNION ALLUNION 会去重并排序成本更高两侧列顺序数量要一致MINUS / INTERSECT求差集和交集会去重结果集中 NULL 被视为一个值这张表不能替代手册但把你最容易犯的毛病都汇总起来了。每次写完 SQL 如果怀疑结果不对先对着表自查一遍通常能定位到 80% 的问题。6.3 最值得养成的一个习惯把基础查询当“语法数据”来理解说句实在话基础查询的关键词并不多难点在于每个关键词在数据库内部都有固定的执行语义。写 SQL 时不要只想着“我要查什么”还要想“数据库会先做什么、后做什么”。FROM先确定数据源WHERE做行过滤GROUP BY分组HAVING过滤分组SELECT计算结果ORDER BY最后排序。理解这个顺序你就能解释为什么WHERE里不能用聚合函数为什么SELECT里定义的别名不能在WHERE里直接引用。这些不是 Oracle 故意为难你而是执行顺序决定的。我以前带新人时会让他们把每条 SQL 的执行计划打印出来看每一步做了什么。虽然不用完全看懂成本值但要注意有没有全表扫描在索引列上发生有没有SORT操作在无谓的地方出现有没有FILTER在子查询循环执行。时间一长再遇到慢查询和奇怪结果猜原因比满世界搜资料快得多。基础查询指标不多但任何一环出了偏差结果都不安全。根据我个人经验这一期内容最值得收藏的是NULL相关的几个坑和分页模板。项目中因为NOT IN和ROWNUM出的问题占了我过去几年答疑的大头。把这些规则记牢在别的环境换汤不换药也能用上。希望这篇补充能让你在写 Oracle 基础查询时心里更有底。