1. NULL值在Oracle中的本质特性NULL在Oracle数据库中是一个特殊标记它表示未知的、不存在的或不适用的值。与空字符串或数字0不同NULL在数据库引擎内部有独特的处理机制。当我们在Oracle中执行CREATE TABLE语句时默认情况下所有列都允许NULL值除非显式指定NOT NULL约束。注意Oracle中的NULL与空字符串在语义上是等价的这与某些其他数据库系统不同。在SQL语句中会被自动转换为NULL。NULL值在存储时具有以下特点不占用表空间除列头标记外在索引中不创建条目B树索引不包含NULL值在统计信息中被单独计数2. NULL在SQL操作中的特殊行为2.1 比较运算中的三值逻辑Oracle中的比较运算遇到NULL时会产生三值逻辑TRUE/FALSE/UNKNOWNSELECT * FROM employees WHERE salary 5000; -- 如果某行的salary为NULL该行不会出现在结果中正确的NULL比较应该使用IS NULL或IS NOT NULLSELECT * FROM employees WHERE salary IS NULL;2.2 聚合函数对NULL的处理常见聚合函数对NULL的处理方式COUNT(*)计算所有行包括NULLCOUNT(column)忽略NULL值SUM()/AVG()/MAX()/MIN()忽略NULL值LISTAGG()默认忽略NULL可使用ON NULL子句处理示例-- 计算非NULL的commission_pct平均值 SELECT AVG(commission_pct) FROM employees; -- 计算所有员工的commission_pct平均值包括NULL视为0 SELECT AVG(NVL(commission_pct, 0)) FROM employees;3. 处理NULL值的实用函数3.1 NVL函数家族Oracle提供了一系列处理NULL的函数NVL(expr1, expr2)如果expr1为NULL则返回expr2NVL2(expr1, expr2, expr3)expr1非NULL返回expr2否则返回expr3COALESCE(expr1, expr2, ..., exprn)返回第一个非NULL表达式NULLIF(expr1, expr2)两表达式相等时返回NULL否则返回expr1使用示例-- 将NULL佣金显示为0 SELECT employee_name, NVL(commission_pct, 0) FROM employees; -- 使用COALESCE设置多个备用值 SELECT COALESCE(phone_number, mobile_number, No contact) FROM contacts;3.2 DECODE和CASE表达式条件逻辑也可以处理NULL值-- 使用DECODEOracle特有 SELECT DECODE(commission_pct, NULL, No commission, Has commission) FROM employees; -- 使用标准CASE表达式 SELECT CASE WHEN commission_pct IS NULL THEN 0 ELSE commission_pct END AS adjusted_commission FROM employees;4. NULL在索引和约束中的影响4.1 唯一约束与NULLOracle的唯一约束允许多个NULL值存在因为NULL被认为不等于任何值包括另一个NULLCREATE TABLE unique_nulls ( id NUMBER, code VARCHAR2(10), CONSTRAINT unq_code UNIQUE (code) ); -- 以下插入语句都能成功执行 INSERT INTO unique_nulls VALUES (1, NULL); INSERT INTO unique_nulls VALUES (2, NULL);4.2 函数索引处理NULL创建函数索引时可以控制NULL的行为-- 创建忽略NULL值的函数索引 CREATE INDEX idx_emp_comm ON employees (NVL(commission_pct, 0)); -- 查询时需要使用相同的表达式才能利用索引 SELECT * FROM employees WHERE NVL(commission_pct, 0) 0.2;5. NULL在表连接中的处理5.1 外连接中的NULL外连接会产生NULL值来表示未匹配的行-- 左外连接示例 SELECT e.employee_name, d.department_name FROM employees e LEFT JOIN departments d ON e.department_id d.department_id;5.2 使用NATURAL JOIN的陷阱NATURAL JOIN会自动匹配同名列可能产生意外结果-- 如果两个表都有created_date列且值不同可能导致无匹配 SELECT * FROM employees NATURAL JOIN departments;6. 高级NULL处理技巧6.1 使用DEFAULT值替代NULLOracle 12c引入了DEFAULT ON NULL语法CREATE TABLE orders ( order_id NUMBER, order_date DATE DEFAULT ON NULL SYSDATE, status VARCHAR2(20) DEFAULT ON NULL NEW );6.2 使用NULLS FIRST/LAST排序控制NULL在排序结果中的位置-- NULL值排在最后默认 SELECT * FROM employees ORDER BY commission_pct NULLS LAST; -- NULL值排在最前 SELECT * FROM employees ORDER BY commission_pct NULLS FIRST;6.3 分析函数中的IGNORE NULLS某些分析函数支持IGNORE NULLS选项-- 查找前一个非NULL的commission值 SELECT employee_id, commission_pct, LAG(commission_pct IGNORE NULLS) OVER (ORDER BY employee_id) AS prev_comm FROM employees;7. 实际应用中的NULL处理经验查询优化在WHERE子句中避免对NULL列使用函数这会阻止索引使用-- 不推荐索引失效 SELECT * FROM table WHERE NVL(column, 0) 1; -- 推荐写法 SELECT * FROM table WHERE column 1 OR (column IS NULL AND 0 1);报表设计在报表中明确区分NULL和0值可以使用NVL配合注释SELECT product_name, NVL(sales_amount, 0) AS sales, CASE WHEN sales_amount IS NULL THEN (未销售) ELSE END AS comment FROM sales_report;数据迁移在不同数据库系统间迁移时特别注意NULL与空字符串的处理差异。Oracle中就是NULL但其他数据库可能不同。批量处理使用MERGE语句时NULL比较需要特别注意MERGE INTO target t USING source s ON (t.id s.id AND (t.value s.value OR (t.value IS NULL AND s.value IS NULL))) WHEN MATCHED THEN UPDATE SET t.value s.value;统计计算计算平均值时考虑是否应该包含NULL值。使用AVG(NVL(column, 0))会将NULL视为0而AVG(column)会忽略NULL行。