调试经验——Oracle 使用NVL函数进行包含空值的列的筛选（Filtering of field values contaning Null value)

问题描述：

在原来的查询语句（query）中，包含了类似 SAMPLETABLE.SAMPLEFIELD NOT IN ('EXCLUEDED VALUE1','EXCLUEDED VALUE2')这样的条件，结果是，当一条数据的SAMPLEFIELD为空时，该条数据查不出来。

经过近一小时的调试，才找到了解决办法：

使用NVL函数封装该字段后再进行判断，即NVL(SAMPLETABLE.SAMPLEFIELD,0) NOT IN ('EXCLUEDED VALUE1','EXCLUEDED VALUE2')

分析：当Null值与任何值进行比较时，返回的结果都是Null，所以要将Null值转换为一个非Null的值再进行字符串的比较。

Oracle_Database_SQL_Language_Reference_11.2中对NVL函数的讲解如下：

--------------------------------------------------------------------------------------------------

Purpose
NVL lets you replace null (returned as a blank) with a string in the results of a query. If
expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1.
The arguments expr1 and expr2 can have any data type. If their data types are
different, then Oracle Database implicitly converts one to the other. If they cannot be
converted implicitly, then the database returns an error. The implicit conversion is
implemented as follows:
1. If expr1 is character data, then Oracle Database converts expr2 to the data type of
expr1 before comparing them and returns VARCHAR2 in the character set of expr1.
2. If expr1 is numeric, then Oracle Database determines which argument has the
highest numeric precedence, implicitly converts the other argument to that data
type, and returns that data type.

Examples
The following example returns a list of employee names and commissions,
substituting "Not Applicable" if the employee receives no commission:
SELECT last_name, NVL(TO_CHAR(commission_pct), 'Not Applicable') commission
FROM employees
WHERE last_name LIKE 'B%'
ORDER BY last_name;
LAST_NAME COMMISSION
------------------------- ----------------------------------------
Baer  Not Applicable
Baida  Not Applicable
Banda  .1
Bates  .15
Bell  Not Applicable
Bernstein  .25
Bissot  Not Applicable
Bloom  .2
Bull  Not Applicable