尧图网页设计 
做 Oracle 技术内容的时间越长我越发现一个规律大家平时写的 SELECT、WHERE、ORDER BY 都很熟练真正翻车的地方往往集中在几个“不起眼”的基础关键词上——NULL、DISTINCT、ROWNUM、COALESCE、HAVING、ROLLUP、EXISTS以及它们之间的组合行为。这一篇是 Oracle 语句系列的第 23 期我打算把基础查询类关键词里值得补充的细节一次性理清楚。面向的是已经能写简单 SQL、但想再抠抠边界情况的读者也适合那些经常被查询结果“莫名其妙不符合预期”困扰的人对照排查。有时候你不是不会写 SQL而是不知道某个关键词在特定数据形态下会怎么表现。比如DISTINCT到底怎么处理 NULLNOT IN遇到子查询返回 NULL 会怎样LEFT JOIN的条件写 ON 和写 WHERE 结果有什么区别这些都属于“基础查询”但绝不“初级”的知识点。下面我按关键词的职责分类来补每块都会带上执行逻辑解释和实操例子。1. SELECT、FROM、WHERE 的执行顺序三个让你写错别名的底层原因1.1 逻辑执行顺序早于书写顺序别在 WHERE 里引用列别名很多初学者学 SQL 时会下意识觉得“我先写 SELECT 再写 FROM那 SQL 就是先算 SELECT 的”。但 Oracle 的执行逻辑顺序和书写顺序完全相反。标准 SQL 的逻辑处理顺序大致是FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY。这个顺序直接决定了一个高频报错不能在 WHERE 里使用 SELECT 中定义的列别名。-- 这段 SQL 会报 ORA-00904: ANNUAL_SALARY invalid identifier SELECT emp_name, salary * 12 AS annual_salary FROM emp WHERE annual_salary 50000;原因就是 WHERE 在 SELECT 之前执行Oracle 根本还没算出 annual_salary自然无法引用。正确写法是把计算表达式原样搬进 WHERE或者包一层子查询SELECT emp_name, annual_salary FROM ( SELECT emp_name, salary * 12 AS annual_salary FROM emp ) WHERE annual_salary 50000;这个“包一层”的思路在基础查询里很常用尤其是在做 TOP-N、分页、复杂表达式过滤的时候。别觉得子查询慢很多时候它是在帮你绕过 SQL 执行顺序的限制。提示ORDER BY 和 GROUP BY 对别名的待遇完全不同。ORDER BY 可以引用 SELECT 里的列别名比如ORDER BY annual_salary是合法的GROUP BY 在 Oracle 中不能直接用别名必须写成表达式本身。1.2 表别名、列别名与常数列的实用细节表别名不只是一个“缩写”它还是解决列名冲突的关键。当多张表都有相同字段时Oracle 会报 ORA-00918: column ambiguously defined。比如SELECT emp_id, dept_id FROM emp e JOIN dept d ON e.dept_id d.dept_id;这里 emp_id 只在 emp 表有没问题dept_id 两张表都有就必须写成e.dept_id或d.dept_id。我的习惯是一旦用 JOIN所有字段都加上表别名前缀哪怕暂时没有冲突。这看起来啰嗦但能避免后续加字段时突然冒出 ORA-00918。列别名有几个特殊字符注意点。不加双引号的别名会被转成大写加了双引号则保留原始大小写和空格SELECT emp_name AS 姓名, salary * 12 AS Annual Salary FROM emp;在 Oracle 里姓名 双引号字符串作为别名没问题但在 Java 或 MyBatis 中返回列映射时会多一层注意。另外基础查询里经常需要加一个常量列比如标记数据来源、区分标识SELECT 在职 AS status_flag, emp_name, hire_date FROM emp WHERE resign_date IS NULL;这种方式在报表合并、数据比对时非常实用因为常量列不依赖任何表字段可以在 SELECT 中直接使用。2. DISTINCT、NULL、ROWNUM 与行限定过滤类关键词的边界条件2.1 DISTINCT 对 NULL 的处理与 UNIQUE 的关系DISTINCT可能是看起来最简单、实际坑最多的关键词之一。它做的是整行去重而不是单列去重。很多人以为SELECT DISTINCT col1, col2是先给 col1 去重再给 col2 去重其实它是对 col1 col2 的组合去重。关于 NULLOracle 的 DISTINCT 会把所有 NULL 视为“同一个值”。也就是说如果表里有 10 行 manager_id 都是 NULLSELECT DISTINCT manager_id FROM emp最后只会返回一行 NULL。这个行为对理解去重结果很重要它和 GROUP BY 对 NULL 的处理一致——NULL 会自动归为同一组。UNIQUE是 Oracle 早期提供的同义词效果和 DISTINCT 相同。我很少用 UNIQUE因为它是 Oracle 私有的不通用跨数据库时还要改回来。如果接手旧项目看到 UNIQUE知道它等于 DISTINCT 就行。需要注意DISTINCT 不是聚合函数不要和 GROUP BY 混叠使用否则结果容易让人误解。比如SELECT DISTINCT department_id, COUNT(*) FROM emp GROUP BY department_id;这语法能执行但 DISTINCT 在这里毫无意义反而会引入不必要的排序和去重开销。规范写法是直接用 GROUP BY去掉 DISTINCT。2.2 WHERE 里的 NULL 判断为什么不能用等号这是最经典的基础知识点但我还是要再补一层NULL 不是值它表示“未知”。所以在 Oracle 中col NULL的结果既不是 TRUE 也不是 FALSE而是 UNKNOWNWHERE 只保留 TRUE 的行所以这类条件永远查不到数据。-- 查不到任何行即使表里有大量 NULL SELECT * FROM emp WHERE manager_id NULL; -- 正确写法 SELECT * FROM emp WHERE manager_id IS NULL;另一个隐蔽的坑是IN列表里带 NULL。比如WHERE dept_id IN (10, NULL)dept_id 10 的行能返回dept_id 为 NULL 的行不会返回因为 NULL NULL 是 UNKNOWN。更危险的是WHERE dept_id NOT IN (10, NULL)这个条件的逻辑是“dept_id ! 10 AND dept_id ! NULL”后者恒为 UNKNOWN最终整条结果的过滤相当于什么都不返回。处理思路很简单要么在子查询或 IN 列表里显式排除 NULL要么改用 NOT EXISTS。这个点我下面在讲 EXISTS 时还会再展开。2.3 TOP-N 查询ROWNUM、FETCH FIRST 与经典分页Oracle 里取前 N 行最直接的就是ROWNUM。但 ROWNUM 是在 WHERE 过滤之后、ORDER BY 排序之前赋值的这个顺序导致了很多“SQL 逻辑没毛病但结果不对”的案例。-- 希望取工资最高的 3 人但结果往往不是期望的 SELECT emp_name, salary FROM emp WHERE ROWNUM 3 ORDER BY salary DESC;这条 SQL 先取了前 3 行再对这 3 行排序所以返回的是“表里随机 3 行中工资最高的”而不是“全表工资最高的 3 人”。正确做法是先排序再取前 N 行SELECT emp_name, salary FROM ( SELECT emp_name, salary FROM emp ORDER BY salary DESC ) WHERE ROWNUM 3;如果继续做分页Oracle 12c 之前最常用的三层写法SELECT * FROM ( SELECT t.*, ROWNUM AS rn FROM ( SELECT emp_name, salary FROM emp ORDER BY salary DESC ) t WHERE ROWNUM 20 ) WHERE rn 10;12c 之后可以直接用 FETCH FIRST 语法清爽很多SELECT emp_name, salary FROM emp ORDER BY salary DESC OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;ROWNUM 还有一个特性初学者容易踩WHERE ROWNUM 1永远返回空。因为第一行满足条件时 ROWNUM 会被赋为 1但 1 1 不成立这一行被过滤掉第二行又从头开始赋 ROWNUM同样不成立所以结果永远为零行。能记住这个基本就理解了 ROWNUM 的赋值时机。3. DECODE、CASE、COALESCE条件分支关键词的取舍3.1 DECODE 与 CASE 的差异以及一个容易踩的空值判断DECODE是 Oracle 独有的条件判断函数写法很紧凑适合简单的等值映射SELECT emp_name, DECODE(department_id, 10, 财务部, 20, 研发部, 其他) AS dept_name FROM emp;但 DECODE 的局限也很明显它只能做等值比较不能做范围判断嵌套多层之后可读性很差。CASE是标准 SQL两种写法我都列出来-- 简单 CASE适合等值比较 SELECT emp_name, CASE department_id WHEN 10 THEN 财务部 WHEN 20 THEN 研发部 ELSE 其他 END AS dept_name FROM emp; -- 搜索 CASE适合范围判断 SELECT emp_name, salary, CASE WHEN salary 10000 THEN 高薪 WHEN salary 5000 THEN 中薪 ELSE 低薪 END AS salary_level FROM emp;一个容易被忽视的差异是 DECODE 对 NULL 的“相等”判断。DECODE 内部把 NULL 和 NULL 视为相等所以DECODE(manager_id, NULL, 无上级, 有上级)能正常命中第一个分支。而简单 CASE 的CASE manager_id WHEN NULL THEN ...不会命中 NULL因为简单 CASE 底层也是等值比较NULL 不等于 NULL。如果你在简单 CASE 里想判断 NULL要显式写成搜索 CASECASE WHEN manager_id IS NULL THEN 无上级 ELSE 有上级 END。另外搜索 CASE 的判断顺序是自上而下一旦命中就结束。写区间条件时要先写高值再写低值否则低值会提前截胡。比如先写WHEN salary 5000 THEN 中薪再写WHEN salary 10000 THEN 高薪高薪员工会全部落到中薪分支。3.2 NVL、COALESCE、NULLIF 在空值处理里的分工NVL 是 Oracle 最常用的空值替换函数SELECT emp_name, NVL(commission_pct, 0) AS comm FROM emp;COALESCE 是标准 SQL 的更通用版本它接受多个参数返回第一个非 NULL 值SELECT COALESCE(phone, mobile, 无联系方式) AS contact FROM customer;等价于NVL(phone, NVL(mobile, 无联系方式))。两者的一个注意点是参数类型要一致或能隐式转换。COALESCE(1, a)这种混搭可能报 ORA-00932实践中尽量统一类型。NULLIF 比前两个冷门但用途很明确两个值相等时返回 NULL否则返回第一个值。我常用它来做除零保护-- 防止 rate 为 0 时除零 SELECT amount / NULLIF(rate, 0) AS result FROM payment;rate 为 0 时NULLIF 返回 NULL除法结果也是 NULL。后续可以再配合 NVL 包一层。基础查询的空值函数看起来简单但它们和 NULL 的所有特性是一脉相承的遇到“结果比预期少了几行”的问题时优先检查是不是 NULL 参与了运算或比较。4. LIKE、INSTR、REGEXP_LIKE模糊查询类关键词的正确使用方式4.1 LIKE 的匹配规则与 ESCAPE 转义LIKE是模糊查询的基础关键词%匹配任意长度包括零字符_匹配单个字符。应用场景我不多说重点讲转义。当你需要查询包含%或_本身的数据时必须指定 ESCAPE 字符-- 查询备注里包含 10% 折扣的记录 SELECT * FROM product WHERE remark LIKE %10\%% ESCAPE \;这里第一个和最后一个%是通配符中间的\%通过 ESCAPE 转义表示真正的百分号。如果不加 ESCAPE查询会匹配“以任意字符开头、包含 10、后跟任意字符且最后以任意字符结尾”的数据结果几乎可以肯定不是你想要的。另一个高频问题是大小写。Oracle 默认区分大小写所以 LIKE 匹配英文字段时经常要先转换SELECT * FROM product WHERE UPPER(product_name) LIKE %ORACLE%;代价是这个写法无法使用 product_name 上的普通 B 树索引。如果这个查询频率很高解决方案是建函数索引CREATE INDEX idx_prod_upname ON product(UPPER(product_name));索引列上有函数时优化器才可能走该函数索引。这个思路适用于所有字符函数不只是 LIKE。4.2 字符函数组合解析字段以及函数索引建议INSTR 和 SUBSTR 是处理字符串拆分的黄金搭档。INSTR 返回子串位置SUBSTR 按位置截取二者组合可以替代很多简单的正则场景。比如订单号格式是ORD-2024-000123想拆出前缀部分SELECT order_no, SUBSTR(order_no, 1, INSTR(order_no, -) - 1) AS order_prefix, SUBSTR(order_no, INSTR(order_no, -) 1) AS order_rest FROM order_table;REPLACE 和 TRIM 也很常用。REPLACE 做整段替换TRIM 去除首尾空格。一个常见教训是从 Excel 或第三方文件导入的数据经常带不可见空格直接WHERE emp_no A001查不到要先TRIM(emp_no)再比。更隐蔽的是全角空格TRIM 默认只处理普通空格如果遇到其他空白字符可以用TRIM(REPLACE(col, CHR(160), ))先把不间断空格转成普通空格。Oracle 还支持REGEXP_LIKE它可以在条件里使用正则表达式-- 匹配 1 开头、第二位 3-9 的 11 位手机号 SELECT * FROM customer WHERE REGEXP_LIKE(phone, ^1[3-9][0-9]{9}$);正则表达式的优势是匹配能力强代价是执行开销更大而且通常无法走普通索引。基础查询里能用 LIKE 和 INSTR 解决的不一定非要上正则只有验证格式、复杂模式匹配时才值得用。5. GROUP BY、HAVING、ROLLUP分组聚合关键词的细节与误区5.1 分组查询的合法列表ORA-00979 的根源GROUP BY 的核心规则只有一句话SELECT 列表里出现的非聚合列必须全部出现在 GROUP BY 子句中。违反规则就会报 ORA-00979: not a GROUP BY expression。-- 错误emp_name 不在 GROUP BY 中 SELECT department_id, emp_name, COUNT(*) FROM emp GROUP BY department_id;这条 SQL 的问题在于部门 10 里可能有 10 个不同的 emp_name数据库没办法确定你要哪一个人的名字。Oracle 不像 MySQL 的宽松模式允许这种写法所以它能帮你避免不少“随机取一个值”的逻辑错误。GROUP BY 后面可以跟表达式比如按入职月份统计SELECT TRUNC(hire_date, MM) AS hire_month, COUNT(*) FROM emp GROUP BY TRUNC(hire_date, MM) ORDER BY hire_month;记住 SELECT 里的表达式要和 GROUP BY 里的表达式保持一致不能一个用TRUNC(hire_date, MM)一个用别名。这点和前面说的执行顺序是同一个源头。WHERE 和 HAVING 的区别也要再强调WHERE 在分组前过滤行HAVING 在分组后过滤组。想过滤“人数大于 5 的部门”只能用 HAVINGSELECT department_id, COUNT(*) FROM emp GROUP BY department_id HAVING COUNT(*) 5;而 WHERE 里不能写聚合函数因为它执行时还没有聚合结果。5.2 COUNT 家族与 NULL 的纠缠COUNT 是聚合函数里最容易踩 NULL 坑的一个。COUNT(*)统计所有行COUNT(column)只统计该列非 NULL 的行COUNT(DISTINCT column)统计非 NULL 且去重后的数量。-- 两个结果很可能不一样 SELECT COUNT(*) AS total_rows, COUNT(manager_id) AS has_manager, COUNT(DISTINCT manager_id) AS distinct_manager FROM emp;如果某列有 100 行其中 30 个是 NULL那么 COUNT(列) 最多返回 70如果 70 个非 NULL 值里有重复COUNT(DISTINCT 列) 可能更少。这就解释了一个现象为什么有些报表 SUM 和 COUNT 对不上。另外GROUP BY 一个包含 NULL 的列时NULL 会作为单独一组出现在结果里这和 DISTINCT 的行为一致。5.3 ROLLUP、GROUPING 与报表小计ROLLUP、CUBE、GROUPING SETS 这三个关键词在基础查询里属于“进阶补充”但能显著简化报表逻辑。ROLLUP 会在分组维度上自动生成小计和总计行SELECT department_id, job_id, COUNT(*) AS cnt, GROUPING(department_id) AS dept_flag, GROUPING(job_id) AS job_flag FROM emp GROUP BY ROLLUP(department_id, job_id);这个查询会返回四类行按部门岗位的明细统计、按部门的小计job_flag1、总计dept_flag1 且 job_flag1。GROUPING 函数用来判断当前行是否为汇总行报表程序可以通过 dept_flag 和 job_flag 的组合高亮汇总行非常方便。如果你只需要部分组合的汇总用 GROUPING SETS 比 ROLLUP 更精细SELECT department_id, job_id, COUNT(*) FROM emp GROUP BY GROUPING SETS ((department_id), (job_id), ());后面的空括号表示总计。CUBE 则是对所有维度排列组合生成全部小计维度多了之后行数爆炸基础场景用 ROLLUP 就够。这组关键词的价值在于把需要 UNION 多次的 SQL 合并成一条逻辑更清晰性能也更可控。6. JOIN、IN、EXISTS连接与子查询关键词的取舍逻辑6.1 JOIN 类型与 USING左右连接和全外连接怎么用连接查询的基础分类不复杂我直接用一张表总结JOIN 类型返回内容典型场景INNER JOIN两表匹配上的行只查有订单的客户LEFT JOIN左表全部 右表匹配行查所有客户及其订单RIGHT JOIN右表全部 左表匹配行等价于左右表互换的 LEFT JOINFULL OUTER JOIN两表全部未匹配补 NULL查全部客户和全部订单不管是否匹配CROSS JOIN笛卡尔积生成排列组合危险度高关系型数据库的“连接语义”在实际数据里很容易验证。比如查所有客户及其订单即使客户没有订单也要显示客户就选 LEFT JOINSELECT c.cust_name, o.order_id FROM customers c LEFT JOIN orders o ON c.cust_id o.cust_id ORDER BY c.cust_name;如果两张表连接列名称相同可以用 USING 简化并且 USING 会合并输出这一列不会出现两个 cust_idSELECT c.cust_name, o.order_id FROM customers c JOIN orders o USING (cust_id);要注意USING 只能用于等值连接而且连接列不能在 ON 子句或表前缀里再写。混合使用 ON 和 USING 容易报错建议同一查询里只选一种写法。NATURAL JOIN 我一般不推荐它自动按两表同名列等值连接看起来省事但一旦表结构加了新字段连接语义可能悄悄改变很容易出线上事故。6.2 ON 和 WHERE 的过滤时机左连接结果不一样LEFT JOIN 时ON 子句和 WHERE 子句里的过滤条件行为完全不同这是基础查询里“看起来简单但做错率极高”的知识点。-- 写法 A条件放在 ON未匹配客户仍会保留 SELECT c.cust_name, o.order_id, o.status FROM customers c LEFT JOIN orders o ON c.cust_id o.cust_id AND o.status VALID; -- 写法 B条件放在 WHERE未匹配客户被过滤掉等价于 INNER JOIN SELECT c.cust_name, o.order_id, o.status FROM customers c LEFT JOIN orders o ON c.cust_id o.cust_id WHERE o.status VALID;写法 A 是先按“客户订单 且订单有效”做外连接结果里保留了没有有效订单的客户o.status 为 NULL。写法 B 是在外连接完成后又用 WHERE 把 status 非 VALID 或 NULL 的行过滤掉等价于只查“有有效订单的客户”。实际报表里最常见的就是用写法 B 本来想看所有客户结果发现客户数少了一查就是连接条件被 WHERE 干扰。排查思路就是看过滤条件对结果集的“保留义务”到底属于哪一步。6.3 NOT IN 的 NULL 陷阱与 EXISTS 改写IN 和 EXISTS 的取舍是基础查询里的经典话题。IN 语义上等价于“等于子查询结果中的任意一个值”EXISTS 语义是“子查询至少返回一行”。两者在结果一致时优化器经常互相改写但逻辑正确性上有一个绝对的坑NOT IN 的子查询结果若包含 NULL整个查询结果为空。-- 危险写法orders.statusVALID 的 cust_id 里只要出现一个 NULL结果就空 SELECT cust_name FROM customers WHERE cust_id NOT IN ( SELECT cust_id FROM orders WHERE status VALID );为什么NOT IN 会被解析为“不等于里面每一个值”只要遇到 NULL就变成cust_id ! 1 AND cust_id ! NULL后者是 UNKNOWN整个条件不可能为 TRUE。稳妥写法是 NOT EXISTSSELECT cust_name FROM customers c WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.cust_id c.cust_id AND o.status VALID );NOT EXISTS 只判断子查询是否有返回行不受 NULL 影响。另一个预防办法是先把子查询中的 NULL 排除掉WHERE cust_id IS NOT NULL。但在复杂查询里最省心的还是直接 NOT EXISTS。EXISTS 还有一个配合优势它使用的关联子查询可以在内层引用外层表的列写法灵活比如“统计有有效订单的客户且订单金额大于 1000”EXISTS 很容易表达SELECT c.cust_name FROM customers c WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.cust_id c.cust_id AND o.amount 1000 );这一篇我挑出来的这些关键词全是我在培训和脚本审查中反复见到的问题点。建议你拿自己的库跑一遍上面的例子特别是 NULL 场景和 LEFT JOIN 的过滤条件位置十次有八次的问题出在这两处。最后再分享一个小习惯我会在测试环境故意往表里插几条带 NULL、重复值、边界长度的记录再验证每个查询关键词的表现很多隐藏问题都是这样提前暴露的。Oracle 基础查询的底线不在语法而在你对每个关键词边界条件的理解。 Oracle基础查询关键词避坑指南：NULL、DISTINCT、ROWNUM等细节解析
做 Oracle 技术内容的时间越长我越发现一个规律大家平时写的 SELECT、WHERE、ORDER BY 都很熟练真正翻车的地方往往集中在几个“不起眼”的基础关键词上——NULL、DISTINCT、ROWNUM、COALESCE、HAVING、ROLLUP、EXISTS以及它们之间的组合行为。这一篇是 Oracle 语句系列的第 23 期我打算把基础查询类关键词里值得补充的细节一次性理清楚。面向的是已经能写简单 SQL、但想再抠抠边界情况的读者也适合那些经常被查询结果“莫名其妙不符合预期”困扰的人对照排查。有时候你不是不会写 SQL而是不知道某个关键词在特定数据形态下会怎么表现。比如DISTINCT到底怎么处理 NULLNOT IN遇到子查询返回 NULL 会怎样LEFT JOIN的条件写 ON 和写 WHERE 结果有什么区别这些都属于“基础查询”但绝不“初级”的知识点。下面我按关键词的职责分类来补每块都会带上执行逻辑解释和实操例子。1. SELECT、FROM、WHERE 的执行顺序三个让你写错别名的底层原因1.1 逻辑执行顺序早于书写顺序别在 WHERE 里引用列别名很多初学者学 SQL 时会下意识觉得“我先写 SELECT 再写 FROM那 SQL 就是先算 SELECT 的”。但 Oracle 的执行逻辑顺序和书写顺序完全相反。标准 SQL 的逻辑处理顺序大致是FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY。这个顺序直接决定了一个高频报错不能在 WHERE 里使用 SELECT 中定义的列别名。-- 这段 SQL 会报 ORA-00904: ANNUAL_SALARY invalid identifier SELECT emp_name, salary * 12 AS annual_salary FROM emp WHERE annual_salary 50000;原因就是 WHERE 在 SELECT 之前执行Oracle 根本还没算出 annual_salary自然无法引用。正确写法是把计算表达式原样搬进 WHERE或者包一层子查询SELECT emp_name, annual_salary FROM ( SELECT emp_name, salary * 12 AS annual_salary FROM emp ) WHERE annual_salary 50000;这个“包一层”的思路在基础查询里很常用尤其是在做 TOP-N、分页、复杂表达式过滤的时候。别觉得子查询慢很多时候它是在帮你绕过 SQL 执行顺序的限制。提示ORDER BY 和 GROUP BY 对别名的待遇完全不同。ORDER BY 可以引用 SELECT 里的列别名比如ORDER BY annual_salary是合法的GROUP BY 在 Oracle 中不能直接用别名必须写成表达式本身。1.2 表别名、列别名与常数列的实用细节表别名不只是一个“缩写”它还是解决列名冲突的关键。当多张表都有相同字段时Oracle 会报 ORA-00918: column ambiguously defined。比如SELECT emp_id, dept_id FROM emp e JOIN dept d ON e.dept_id d.dept_id;这里 emp_id 只在 emp 表有没问题dept_id 两张表都有就必须写成e.dept_id或d.dept_id。我的习惯是一旦用 JOIN所有字段都加上表别名前缀哪怕暂时没有冲突。这看起来啰嗦但能避免后续加字段时突然冒出 ORA-00918。列别名有几个特殊字符注意点。不加双引号的别名会被转成大写加了双引号则保留原始大小写和空格SELECT emp_name AS 姓名, salary * 12 AS Annual Salary FROM emp;在 Oracle 里姓名 双引号字符串作为别名没问题但在 Java 或 MyBatis 中返回列映射时会多一层注意。另外基础查询里经常需要加一个常量列比如标记数据来源、区分标识SELECT 在职 AS status_flag, emp_name, hire_date FROM emp WHERE resign_date IS NULL;这种方式在报表合并、数据比对时非常实用因为常量列不依赖任何表字段可以在 SELECT 中直接使用。2. DISTINCT、NULL、ROWNUM 与行限定过滤类关键词的边界条件2.1 DISTINCT 对 NULL 的处理与 UNIQUE 的关系DISTINCT可能是看起来最简单、实际坑最多的关键词之一。它做的是整行去重而不是单列去重。很多人以为SELECT DISTINCT col1, col2是先给 col1 去重再给 col2 去重其实它是对 col1 col2 的组合去重。关于 NULLOracle 的 DISTINCT 会把所有 NULL 视为“同一个值”。也就是说如果表里有 10 行 manager_id 都是 NULLSELECT DISTINCT manager_id FROM emp最后只会返回一行 NULL。这个行为对理解去重结果很重要它和 GROUP BY 对 NULL 的处理一致——NULL 会自动归为同一组。UNIQUE是 Oracle 早期提供的同义词效果和 DISTINCT 相同。我很少用 UNIQUE因为它是 Oracle 私有的不通用跨数据库时还要改回来。如果接手旧项目看到 UNIQUE知道它等于 DISTINCT 就行。需要注意DISTINCT 不是聚合函数不要和 GROUP BY 混叠使用否则结果容易让人误解。比如SELECT DISTINCT department_id, COUNT(*) FROM emp GROUP BY department_id;这语法能执行但 DISTINCT 在这里毫无意义反而会引入不必要的排序和去重开销。规范写法是直接用 GROUP BY去掉 DISTINCT。2.2 WHERE 里的 NULL 判断为什么不能用等号这是最经典的基础知识点但我还是要再补一层NULL 不是值它表示“未知”。所以在 Oracle 中col NULL的结果既不是 TRUE 也不是 FALSE而是 UNKNOWNWHERE 只保留 TRUE 的行所以这类条件永远查不到数据。-- 查不到任何行即使表里有大量 NULL SELECT * FROM emp WHERE manager_id NULL; -- 正确写法 SELECT * FROM emp WHERE manager_id IS NULL;另一个隐蔽的坑是IN列表里带 NULL。比如WHERE dept_id IN (10, NULL)dept_id 10 的行能返回dept_id 为 NULL 的行不会返回因为 NULL NULL 是 UNKNOWN。更危险的是WHERE dept_id NOT IN (10, NULL)这个条件的逻辑是“dept_id ! 10 AND dept_id ! NULL”后者恒为 UNKNOWN最终整条结果的过滤相当于什么都不返回。处理思路很简单要么在子查询或 IN 列表里显式排除 NULL要么改用 NOT EXISTS。这个点我下面在讲 EXISTS 时还会再展开。2.3 TOP-N 查询ROWNUM、FETCH FIRST 与经典分页Oracle 里取前 N 行最直接的就是ROWNUM。但 ROWNUM 是在 WHERE 过滤之后、ORDER BY 排序之前赋值的这个顺序导致了很多“SQL 逻辑没毛病但结果不对”的案例。-- 希望取工资最高的 3 人但结果往往不是期望的 SELECT emp_name, salary FROM emp WHERE ROWNUM 3 ORDER BY salary DESC;这条 SQL 先取了前 3 行再对这 3 行排序所以返回的是“表里随机 3 行中工资最高的”而不是“全表工资最高的 3 人”。正确做法是先排序再取前 N 行SELECT emp_name, salary FROM ( SELECT emp_name, salary FROM emp ORDER BY salary DESC ) WHERE ROWNUM 3;如果继续做分页Oracle 12c 之前最常用的三层写法SELECT * FROM ( SELECT t.*, ROWNUM AS rn FROM ( SELECT emp_name, salary FROM emp ORDER BY salary DESC ) t WHERE ROWNUM 20 ) WHERE rn 10;12c 之后可以直接用 FETCH FIRST 语法清爽很多SELECT emp_name, salary FROM emp ORDER BY salary DESC OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;ROWNUM 还有一个特性初学者容易踩WHERE ROWNUM 1永远返回空。因为第一行满足条件时 ROWNUM 会被赋为 1但 1 1 不成立这一行被过滤掉第二行又从头开始赋 ROWNUM同样不成立所以结果永远为零行。能记住这个基本就理解了 ROWNUM 的赋值时机。3. DECODE、CASE、COALESCE条件分支关键词的取舍3.1 DECODE 与 CASE 的差异以及一个容易踩的空值判断DECODE是 Oracle 独有的条件判断函数写法很紧凑适合简单的等值映射SELECT emp_name, DECODE(department_id, 10, 财务部, 20, 研发部, 其他) AS dept_name FROM emp;但 DECODE 的局限也很明显它只能做等值比较不能做范围判断嵌套多层之后可读性很差。CASE是标准 SQL两种写法我都列出来-- 简单 CASE适合等值比较 SELECT emp_name, CASE department_id WHEN 10 THEN 财务部 WHEN 20 THEN 研发部 ELSE 其他 END AS dept_name FROM emp; -- 搜索 CASE适合范围判断 SELECT emp_name, salary, CASE WHEN salary 10000 THEN 高薪 WHEN salary 5000 THEN 中薪 ELSE 低薪 END AS salary_level FROM emp;一个容易被忽视的差异是 DECODE 对 NULL 的“相等”判断。DECODE 内部把 NULL 和 NULL 视为相等所以DECODE(manager_id, NULL, 无上级, 有上级)能正常命中第一个分支。而简单 CASE 的CASE manager_id WHEN NULL THEN ...不会命中 NULL因为简单 CASE 底层也是等值比较NULL 不等于 NULL。如果你在简单 CASE 里想判断 NULL要显式写成搜索 CASECASE WHEN manager_id IS NULL THEN 无上级 ELSE 有上级 END。另外搜索 CASE 的判断顺序是自上而下一旦命中就结束。写区间条件时要先写高值再写低值否则低值会提前截胡。比如先写WHEN salary 5000 THEN 中薪再写WHEN salary 10000 THEN 高薪高薪员工会全部落到中薪分支。3.2 NVL、COALESCE、NULLIF 在空值处理里的分工NVL 是 Oracle 最常用的空值替换函数SELECT emp_name, NVL(commission_pct, 0) AS comm FROM emp;COALESCE 是标准 SQL 的更通用版本它接受多个参数返回第一个非 NULL 值SELECT COALESCE(phone, mobile, 无联系方式) AS contact FROM customer;等价于NVL(phone, NVL(mobile, 无联系方式))。两者的一个注意点是参数类型要一致或能隐式转换。COALESCE(1, a)这种混搭可能报 ORA-00932实践中尽量统一类型。NULLIF 比前两个冷门但用途很明确两个值相等时返回 NULL否则返回第一个值。我常用它来做除零保护-- 防止 rate 为 0 时除零 SELECT amount / NULLIF(rate, 0) AS result FROM payment;rate 为 0 时NULLIF 返回 NULL除法结果也是 NULL。后续可以再配合 NVL 包一层。基础查询的空值函数看起来简单但它们和 NULL 的所有特性是一脉相承的遇到“结果比预期少了几行”的问题时优先检查是不是 NULL 参与了运算或比较。4. LIKE、INSTR、REGEXP_LIKE模糊查询类关键词的正确使用方式4.1 LIKE 的匹配规则与 ESCAPE 转义LIKE是模糊查询的基础关键词%匹配任意长度包括零字符_匹配单个字符。应用场景我不多说重点讲转义。当你需要查询包含%或_本身的数据时必须指定 ESCAPE 字符-- 查询备注里包含 10% 折扣的记录 SELECT * FROM product WHERE remark LIKE %10\%% ESCAPE \;这里第一个和最后一个%是通配符中间的\%通过 ESCAPE 转义表示真正的百分号。如果不加 ESCAPE查询会匹配“以任意字符开头、包含 10、后跟任意字符且最后以任意字符结尾”的数据结果几乎可以肯定不是你想要的。另一个高频问题是大小写。Oracle 默认区分大小写所以 LIKE 匹配英文字段时经常要先转换SELECT * FROM product WHERE UPPER(product_name) LIKE %ORACLE%;代价是这个写法无法使用 product_name 上的普通 B 树索引。如果这个查询频率很高解决方案是建函数索引CREATE INDEX idx_prod_upname ON product(UPPER(product_name));索引列上有函数时优化器才可能走该函数索引。这个思路适用于所有字符函数不只是 LIKE。4.2 字符函数组合解析字段以及函数索引建议INSTR 和 SUBSTR 是处理字符串拆分的黄金搭档。INSTR 返回子串位置SUBSTR 按位置截取二者组合可以替代很多简单的正则场景。比如订单号格式是ORD-2024-000123想拆出前缀部分SELECT order_no, SUBSTR(order_no, 1, INSTR(order_no, -) - 1) AS order_prefix, SUBSTR(order_no, INSTR(order_no, -) 1) AS order_rest FROM order_table;REPLACE 和 TRIM 也很常用。REPLACE 做整段替换TRIM 去除首尾空格。一个常见教训是从 Excel 或第三方文件导入的数据经常带不可见空格直接WHERE emp_no A001查不到要先TRIM(emp_no)再比。更隐蔽的是全角空格TRIM 默认只处理普通空格如果遇到其他空白字符可以用TRIM(REPLACE(col, CHR(160), ))先把不间断空格转成普通空格。Oracle 还支持REGEXP_LIKE它可以在条件里使用正则表达式-- 匹配 1 开头、第二位 3-9 的 11 位手机号 SELECT * FROM customer WHERE REGEXP_LIKE(phone, ^1[3-9][0-9]{9}$);正则表达式的优势是匹配能力强代价是执行开销更大而且通常无法走普通索引。基础查询里能用 LIKE 和 INSTR 解决的不一定非要上正则只有验证格式、复杂模式匹配时才值得用。5. GROUP BY、HAVING、ROLLUP分组聚合关键词的细节与误区5.1 分组查询的合法列表ORA-00979 的根源GROUP BY 的核心规则只有一句话SELECT 列表里出现的非聚合列必须全部出现在 GROUP BY 子句中。违反规则就会报 ORA-00979: not a GROUP BY expression。-- 错误emp_name 不在 GROUP BY 中 SELECT department_id, emp_name, COUNT(*) FROM emp GROUP BY department_id;这条 SQL 的问题在于部门 10 里可能有 10 个不同的 emp_name数据库没办法确定你要哪一个人的名字。Oracle 不像 MySQL 的宽松模式允许这种写法所以它能帮你避免不少“随机取一个值”的逻辑错误。GROUP BY 后面可以跟表达式比如按入职月份统计SELECT TRUNC(hire_date, MM) AS hire_month, COUNT(*) FROM emp GROUP BY TRUNC(hire_date, MM) ORDER BY hire_month;记住 SELECT 里的表达式要和 GROUP BY 里的表达式保持一致不能一个用TRUNC(hire_date, MM)一个用别名。这点和前面说的执行顺序是同一个源头。WHERE 和 HAVING 的区别也要再强调WHERE 在分组前过滤行HAVING 在分组后过滤组。想过滤“人数大于 5 的部门”只能用 HAVINGSELECT department_id, COUNT(*) FROM emp GROUP BY department_id HAVING COUNT(*) 5;而 WHERE 里不能写聚合函数因为它执行时还没有聚合结果。5.2 COUNT 家族与 NULL 的纠缠COUNT 是聚合函数里最容易踩 NULL 坑的一个。COUNT(*)统计所有行COUNT(column)只统计该列非 NULL 的行COUNT(DISTINCT column)统计非 NULL 且去重后的数量。-- 两个结果很可能不一样 SELECT COUNT(*) AS total_rows, COUNT(manager_id) AS has_manager, COUNT(DISTINCT manager_id) AS distinct_manager FROM emp;如果某列有 100 行其中 30 个是 NULL那么 COUNT(列) 最多返回 70如果 70 个非 NULL 值里有重复COUNT(DISTINCT 列) 可能更少。这就解释了一个现象为什么有些报表 SUM 和 COUNT 对不上。另外GROUP BY 一个包含 NULL 的列时NULL 会作为单独一组出现在结果里这和 DISTINCT 的行为一致。5.3 ROLLUP、GROUPING 与报表小计ROLLUP、CUBE、GROUPING SETS 这三个关键词在基础查询里属于“进阶补充”但能显著简化报表逻辑。ROLLUP 会在分组维度上自动生成小计和总计行SELECT department_id, job_id, COUNT(*) AS cnt, GROUPING(department_id) AS dept_flag, GROUPING(job_id) AS job_flag FROM emp GROUP BY ROLLUP(department_id, job_id);这个查询会返回四类行按部门岗位的明细统计、按部门的小计job_flag1、总计dept_flag1 且 job_flag1。GROUPING 函数用来判断当前行是否为汇总行报表程序可以通过 dept_flag 和 job_flag 的组合高亮汇总行非常方便。如果你只需要部分组合的汇总用 GROUPING SETS 比 ROLLUP 更精细SELECT department_id, job_id, COUNT(*) FROM emp GROUP BY GROUPING SETS ((department_id), (job_id), ());后面的空括号表示总计。CUBE 则是对所有维度排列组合生成全部小计维度多了之后行数爆炸基础场景用 ROLLUP 就够。这组关键词的价值在于把需要 UNION 多次的 SQL 合并成一条逻辑更清晰性能也更可控。6. JOIN、IN、EXISTS连接与子查询关键词的取舍逻辑6.1 JOIN 类型与 USING左右连接和全外连接怎么用连接查询的基础分类不复杂我直接用一张表总结JOIN 类型返回内容典型场景INNER JOIN两表匹配上的行只查有订单的客户LEFT JOIN左表全部 右表匹配行查所有客户及其订单RIGHT JOIN右表全部 左表匹配行等价于左右表互换的 LEFT JOINFULL OUTER JOIN两表全部未匹配补 NULL查全部客户和全部订单不管是否匹配CROSS JOIN笛卡尔积生成排列组合危险度高关系型数据库的“连接语义”在实际数据里很容易验证。比如查所有客户及其订单即使客户没有订单也要显示客户就选 LEFT JOINSELECT c.cust_name, o.order_id FROM customers c LEFT JOIN orders o ON c.cust_id o.cust_id ORDER BY c.cust_name;如果两张表连接列名称相同可以用 USING 简化并且 USING 会合并输出这一列不会出现两个 cust_idSELECT c.cust_name, o.order_id FROM customers c JOIN orders o USING (cust_id);要注意USING 只能用于等值连接而且连接列不能在 ON 子句或表前缀里再写。混合使用 ON 和 USING 容易报错建议同一查询里只选一种写法。NATURAL JOIN 我一般不推荐它自动按两表同名列等值连接看起来省事但一旦表结构加了新字段连接语义可能悄悄改变很容易出线上事故。6.2 ON 和 WHERE 的过滤时机左连接结果不一样LEFT JOIN 时ON 子句和 WHERE 子句里的过滤条件行为完全不同这是基础查询里“看起来简单但做错率极高”的知识点。-- 写法 A条件放在 ON未匹配客户仍会保留 SELECT c.cust_name, o.order_id, o.status FROM customers c LEFT JOIN orders o ON c.cust_id o.cust_id AND o.status VALID; -- 写法 B条件放在 WHERE未匹配客户被过滤掉等价于 INNER JOIN SELECT c.cust_name, o.order_id, o.status FROM customers c LEFT JOIN orders o ON c.cust_id o.cust_id WHERE o.status VALID;写法 A 是先按“客户订单 且订单有效”做外连接结果里保留了没有有效订单的客户o.status 为 NULL。写法 B 是在外连接完成后又用 WHERE 把 status 非 VALID 或 NULL 的行过滤掉等价于只查“有有效订单的客户”。实际报表里最常见的就是用写法 B 本来想看所有客户结果发现客户数少了一查就是连接条件被 WHERE 干扰。排查思路就是看过滤条件对结果集的“保留义务”到底属于哪一步。6.3 NOT IN 的 NULL 陷阱与 EXISTS 改写IN 和 EXISTS 的取舍是基础查询里的经典话题。IN 语义上等价于“等于子查询结果中的任意一个值”EXISTS 语义是“子查询至少返回一行”。两者在结果一致时优化器经常互相改写但逻辑正确性上有一个绝对的坑NOT IN 的子查询结果若包含 NULL整个查询结果为空。-- 危险写法orders.statusVALID 的 cust_id 里只要出现一个 NULL结果就空 SELECT cust_name FROM customers WHERE cust_id NOT IN ( SELECT cust_id FROM orders WHERE status VALID );为什么NOT IN 会被解析为“不等于里面每一个值”只要遇到 NULL就变成cust_id ! 1 AND cust_id ! NULL后者是 UNKNOWN整个条件不可能为 TRUE。稳妥写法是 NOT EXISTSSELECT cust_name FROM customers c WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.cust_id c.cust_id AND o.status VALID );NOT EXISTS 只判断子查询是否有返回行不受 NULL 影响。另一个预防办法是先把子查询中的 NULL 排除掉WHERE cust_id IS NOT NULL。但在复杂查询里最省心的还是直接 NOT EXISTS。EXISTS 还有一个配合优势它使用的关联子查询可以在内层引用外层表的列写法灵活比如“统计有有效订单的客户且订单金额大于 1000”EXISTS 很容易表达SELECT c.cust_name FROM customers c WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.cust_id c.cust_id AND o.amount 1000 );这一篇我挑出来的这些关键词全是我在培训和脚本审查中反复见到的问题点。建议你拿自己的库跑一遍上面的例子特别是 NULL 场景和 LEFT JOIN 的过滤条件位置十次有八次的问题出在这两处。最后再分享一个小习惯我会在测试环境故意往表里插几条带 NULL、重复值、边界长度的记录再验证每个查询关键词的表现很多隐藏问题都是这样提前暴露的。Oracle 基础查询的底线不在语法而在你对每个关键词边界条件的理解。