本文是文章Nulls: Nothing to Worry About的翻译+笔记。

避免三值逻辑出现问题。

ISO SQL 标准中的NULL可以是任何东西，但不是一个值。 NULL是指示完全缺乏值的标记。 它们会导致三值逻辑，使用起来很混乱，而且这种混乱常常导致粗心的人编写返回错误结果的 SELECT 语句。 本文重点介绍了您将遇到的一些陷阱，并提供了避免这些陷阱的建议。

标量表达式中的NULL值

通常，任何涉及 null 的标量表达式的结果就是 null。 清单 2 中的第一个查询针对 代码清单 1 中的数据，通过生成一个报告来演示这一点，该报告显示将所有工资提高 1,000 美元的影响。 您可以在清单 2 中立即看到 Adams 和 Morle 的新薪水都没有任何值。

代码清单 1：我们的示例架构

TABLE: DEPT_M

DEPTNO       DNAME         LOC
_______      _______       _______________
10           HQ            UTRECHT
20           SALES         MUNISING
30           MANUFACTURING NOVOSIBIRSK

TABLE: EMP_M

EMPNO       ENAME	     	JOB	     		MGR		SAL	 	COMM	  DEPTNO
_______     ____________    ____________    _____   _____    ______   ________                 
100	    	NORGAARD	 	PRESIDENT		        5000		  		10
122	    	LEWIS	     	SALESREP	    120		1100		
199	    	GENNICK				                	2200		  		10
111	    	DE HAAN	     	CLERK	      	110		2000		
112	    	MILLSAP	     	SALESREP	  	110		1250	 1400		20
110	    	ADAMS	     	MANAGER	      	100				 1700		20
120	    	KOLK	     	MANAGER	      	100		2450		  		10
113	    	MCDONALD	 	SALESREP	  	110		1500		  		20
121	    	WOOD	     	CLERK	      	120		1300		  		10
130	    	MORLE	     	CLERK	      	100				  			10		

从数据库服务器的角度来看，空值只能具有一种与上下文和数据类型无关的含义：“信息缺失”。 对空值的任何进一步解释可能都是非常人为和主观的，数据库服务器以相同的方式处理所有空值，无论它们来自何处。 数据库服务器无法将 1,000 美元与 null相加，而必须简单地返回 null 作为结果。

然而，人类确实需要处理空值，并且他们有能力提出数据库服务器无法提出的问题。 零工资意味着什么？ 是不是意味着工资不适用？ 或者，这是否意味着员工“没有工资”，因为该员工的工资为 0 美元？ 或者这是否意味着某个值适用，但我们根本不知道该值？ 或者这是否意味着上述任何一种情况，具体取决于我们正在谈论的员工？ 尽管有时这是必要的，但您可以开始看到人类对空值的解释可能非常危险。

也许最重要的问题是，从商业角度来看，零工资增加 1,000 美元意味着什么。 仅在技术真空中处理空值是不够的。 您必须退后一步，提出正确的业务问题。 只有在了解了底层数据模型和当前查询的业务意图之后，您才准备好如何处理查询中的空值。

处理标量表达式中的空值的一种方法是在可能出现空值时替换实值。 为此，COALESCE 会非常有帮助。 如果您确定除了将所有工资提高 1,000 美元之外，企业还希望向目前没有工资的人员发放 1,000 美元的工资，则可以使用 COALESCE 函数将 null 视为零。 清单 2 中的第二个查询将两个参数（即 SAL 列中的值和零）传递给 COALESCE。 该函数返回第一个非空参数作为其结果。

代码清单 2：Null in、null out 以及 COALESCE 的结果

SELECT EMPNO, ENAME, SAL, SAL + 1000 FROM EMP_M;

EMPNO	   ENAME		SAL		SAL+1000
_______    _________    _____   ___________         
112	   		MILLSAP		1250	2250
110	   		ADAMS
120	   		KOLK	    2450	3450
130	   		MORLE
  ...

SELECT EMPNO, ENAME, SAL, 
        COALESCE(SAL,0) + 1000 FROM EMP_M;

EMPNO	  ENAME	      	SAL    	COALESCE(SAL,0)+1000
_______   _________   	_____   _________________________
112	  		MILLSAP     1250	2250
110	  		ADAMS		        1000
120	  		KOLK	    2450	3450

Oracle 数据库支持多种与 COALESCE 类似的功能。 其中包括 NVL2、NULLIF 和 NVL。 我们建议使用 COALESCE 而不是 NVL，因为 COALESCE 不仅可以处理两个参数，而且它是 SQL 标准的一部分。 当 COALESCE 不够时，您可以在 CASE 表达式中寻求庇护，Oracle SQL 参考中也介绍了这些表达式。

布尔表达式中的NULL值

空值在布尔表达式中以特别微妙的方式表现出来，例如您可能为查询的 WHERE 子句编写的表达式。 布尔表达式通常结果为 TRUE 或 FALSE，但 null 引入了布尔表达式的第三种可能结果：UNKNOWN。 请注意，NULL 与 UNKNOWN 不同：

• SAL + NULL 结果为 NULL。 （这是一个标量表达式。）
• SAL < NULL 结果为 UNKNOWN。 （这是一个布尔表达式。）

代码清单 3 显示了三值逻辑的最终结果。 即使 COMM 与其自身进行比较，数据库也会以上下文无关的方式处理任何空值：与空值的任何比较都会导致 UNKNOWN，并且查询仅返回 WHERE 子句计算结果为 TRUE 的那些行。

代码清单 3：WHERE COMM = COMM 不相等

SELECT * FROM EMP_M WHERE COMM = COMM;

EMPNO	  ENAME	       JOB	     	MGR      SAL     	COMM	DEPTNO
_______   __________   __________    _____   _____   	______  ________ 
112	  		MILLSAP      SALESREP	   110   1250    	1400	20
110	  		ADAMS	     MANAGER	   100	     		1700	20

您通常可以在布尔表达式中使用 IS NULL 或 IS NOT NULL 以避免出现 UNKNOWN 结果。 考虑列出佣金低于 1,500 美元的员工的问题。 你可以这样写：

SELECT * FROM EMP_M
WHERE COMM < 1500;

您很快就会发现（我们希望）诺加德和刘易斯等没有佣金的员工将从名单中删除。 假设您想将空佣金解释为“无佣金”，您可以通过添加 IS NULL 条件来扩展 WHERE 子句：

SELECT * FROM EMP_M
WHERE (COMM < 1500)
   OR (COMM IS NULL);

这里要小心！ 零佣金是否意味着“无佣金”，或者可能意味着“佣金未知”？ 答案取决于您的应用程序，并且可能根本没有明确的答案。 不要自动将可空列上的 IS NULL 谓词添加到查询中。 这样做肯定会导致错误，就像一开始就没有考虑空值的可能性一样。 在我们的示例中是否包含 IS NULL 条件实际上是一项业务决策，而不是技术决策。

在编写 WHERE 子句时，通常使用 AND、OR 和 NOT 运算符来链接多个谓词，就像我们刚刚所做的那样。 图 1 中的真值表显示了这些运算符如何处理 TRUE、FALSE 和 UNKNOWN 操作数的不同组合。

这个表需要这么看。黑条中的是Operator（操作符），如NOT。黑条下面第一行，和最左第一列是Operand（操作数）。两个Operand的相交处是结果。例如：

FALSE AND UNKNOWN = FALSE

CHECK 约束中的NULL值

当您评估 WHERE 子句时，UNKNOWN 会导致与 FALSE 相同的最终结果 — 该行被拒绝。 然而，在 CHECK 约束中，UNKNOWN 会导致与 TRUE 相同的最终结果 — 该行被接受。 这是因为仅当约束的布尔表达式计算结果为 FALSE 时才会引发违规。 这就是为什么以下约束定义允许 DEPTNO 列中存在空值，尽管它可能另有建议：

CHECK (DEPTNO 
   IN (10, 20, 30))

这是思考这一切的好方法。 WHERE 和 HAVING 子句的作用是传递表达式计算结果为 TRUE 的那些行。 CHECK 约束的作用是拒绝表达式为 FALSE 的行。 在所有情况下，对于 UNKNOWN 情况，不会采取任何行动，无论是通过还是拒绝。

NULL和连接

清单 4 演示了一种情况，其中数据库引擎将即时为您生成空值，即使您的数据库一开始并不包含单个空值。 部门 30 的行是通过外连接操作生成的，该行中的员工列被初始化为 null。

代码清单 4：外连接生成空值

SELECT 	E.EMPNO, E.ENAME, E.DEPTNO, D.DEPTNO, D.DNAME
FROM EMP_M E RIGHT OUTER JOIN DEPT_M D ON E.DEPTNO = D.DEPTNO;

EMPNO	  ENAME	        DEPTNO	  DEPTNO	 DNAME
_______   ___________   ________  ________   __________________   
100	  		NORGAARD	10	      10	 		HQ
...		
130	  		MORLE		10		  10	        HQ
                          	      30	 		MANUFACTURING

空值和外连接的另一个微妙问题是从哪个表返回连接列很重要。 请注意，清单 4 中最后一行的两个 DEPTNO 值之一为 null。 空 DEPTNO 位于员工表 (EMP_M) 的 DEPTNO 列中，而非空 DEPTNO 值来自部门表 (DEPT_M)。 编写外连接时，请仔细考虑从中检索连接列的表。

汇总数据中的空值

如果任何操作数为 null，标量表达式将生成 null，但聚合函数会忽略 null。 这种行为是由 SQL 标准指定的，但它仍然可能导致一些非常令人惊讶和不直观的查询结果。 请看 代码清单 5。显然，如果您想要计算包含数值的两列或多列的总和，那么先水平相加再垂直相加与先垂直相加是有区别的。

代码清单 5：SUM(A+B) 与 SUM(A)+SUM(B) 不同

SELECT SUM(SAL+COMM), SUM(SAL)+SUM(COMM) FROM EMP_M;

SUM(SAL+COMM)	         SUM(SAL)+SUM(COMM)
__________________       ________________________
2650	                 19900

为什么有区别？ 这是因为仅对于 SAL 和 COMM 都非空的那些行，SAL + COMM 的结果才非空。 因此，只有那些行对 SUM(SAL+COMM) 的结果有贡献。 另一方面，SUM(SAL)+SUM(COMM) 的结果设法包含两列中的所有非空值。

我们希望能够为处理汇总数据中的空值提供一些好的、简洁的建议。 然而，我们最多可以告诉您的是，仔细考虑空值的可能性，并有意识地决定您想要在摘要中得到的结果。

“NOT IN” 与 “NOT EXISTS”

比较清单 6 中的两个查询（及其结果）。在每个查询中，我们尝试检索没有下属的所有员工。 显然，结果是不同的——所以它们不可能都是正确的。 一个有趣的问题是：哪个查询是正确的？ 根据我们的 EMP_M 演示表，您期望得到什么结果？ 您可能期望第二个结果，因此您可能会指出第一个查询是错误的，对吧？ 也许您知道如何“修复”第一个查询以使其返回“正确”结果。

代码清单6：谁没有下属？

SELECT E1.ENAME FROM EMP_M E1
WHERE E1.EMPNO NOT IN
      (SELECT E2.MGR FROM EMP_M E2);

No rows selected.

SELECT E1.ENAME FROM EMP_M E1
WHERE NOT EXISTS
      (SELECT E2.* FROM EMP_M E2 
       WHERE E2.MGR = E1.EMPNO);

ENAME
____________ 
DE HAAN
WOOD
MILLSAP
LEWIS
MCDONALD
GENNICK
MORLE

但等一下！ 我们有一个令人信服的论据来证明第一个查询是正确的，第二个查询是错误的！ 刘易斯、根尼克、德哈恩、米尔萨普、麦克唐纳、伍德、莫尔——他们中的任何一个都可以成为诺加德的主教练。 因此，我们不能真正确定他们中没有任何一个下属。

清单 6 的微妙之处在于，程序员可能没有有意识地打算对空值应用任何类型的业务解释。 NOT IN 和 NOT EXISTS 通常被认为是可以互换的，但当涉及到空值时，它们实际上不是（不完全是）。 清单 6 第一个示例中的子查询的中间结果包含 null。 数据库引擎无法确定 E1.EMPNO 不等于 null，因此没有行计算为 TRUE。 第二个示例测试是否存在具有特定 MGR 值的行。 甚至不考虑 MGR 为空的行。 在编写 NOT IN 条件时，请务必花时间考虑 X NOT IN (…,NULL,…) 情况。 当编写 NOT EXISTS 时，请考虑业务是否真的需要从 NOT IN 版本的查询中获得的结果。 请记住，选择走哪条路是一项商业决策。 鉴于清单 6 中提出的场景，我们将返回到我们的业务客户来讨论这两种不同解决方案的影响。

注意空集！

一旦您在 SQL 中使用聚合函数，您就必须意识到空集可能会发挥作用。 清单 7 显示了聚合函数如何对空集做出反应。 显然，COUNT 返回零，而 AVG、SUM、MAX 和 MIN 返回 null。 这种行为确实有一定的道理。 如果您没有可计算的值，那么可以公平地说您的值为零，但如果没有至少一个可供选择的值，则您实际上无法得出最大值。 我们可以提出一个合理的论点，即 SUM 应该返回零而不是 null，即没有值的总和为零，但 Oracle 对我们在此描述的行为的实现完全符合 SQL 标准。

代码清单 7：聚合函数和空集

SELECT COUNT(EMPNO),AVG(EMPNO),SUM(EMPNO),MAX(EMPNO),MIN(EMPNO)
FROM EMP_M
WHERE 1 = 2;

COUNT(EMPNO)	     AVG(EMPNO)	        SUM(EMPNO)	  MAX(EMPNO)	    MIN(EMPNO)
________________     _____________      _____________     _____________     _____________ 
0

清单 8 显示了针对列出工资高于部门 10 中任何销售代表的员工问题的两种解决方案。这两个结果怎么可能不同呢？ 第一个查询（在子查询中）检索部门 10 中所有销售代表的最高工资，然后将所有员工的工资与该最高工资逐一进行比较。 第二个查询执行几乎相同的操作，唯一的区别是外部查询中的每个员工工资现在与部门 10 的所有工资进行比较，而不是仅与最高工资进行比较。

代码清单8：谁的薪水更高？

SELECT E1.ENAME FROM EMP_M E1
  WHERE E1.SAL >
        (SELECT MAX(E2.SAL) FROM EMP_M E2
         WHERE E2.DEPTNO = 10 AND E2.JOB = 'SALESREP');

No rows selected.

SELECT E1.ENAME FROM EMP_M E1
  WHERE E1.SAL > ALL
        (SELECT E2.SAL FROM EMP_M E2
         WHERE E2.DEPTNO = 10 AND E2.JOB = 'SALESREP');

ENAME
------------------
NORGAARD
LEWIS
ADAMS
...
MORLE

10 rows select

空集在这里起着重要作用，因为如果您仔细查看我们的 EMP_M 表，您会发现部门 10 没有销售代表。 因此，第一个查询的 MAX 函数返回 null，主查询的 WHERE 子句为所有员工生成 UNKNOWN 值。 另一方面，第二个查询返回所有员工 - 因为任何工资都大于空集中的所有工资。 请注意，即使 Adams 和 Morle 也出现在结果中，即使他们的工资均为空。 我们必须赶紧说这不是一个错误，而是完全符合 SQL 标准的预期行为。 这里要吸取的教训是，总是问自己这个问题：“如果将聚合函数应用于空集会怎样？”

小心什么都没有

编写和处理 SQL 语句时要警惕可能出现的空值。 请记住可帮助您处理空值的工具，包括 CASE、COALESCE、IS NULL、IS NOT NULL、NULLIF、NVL 和 NVL2。 在布尔表达式中使用空值时，请注意第三个值（未知），并记住您的企业可以以不同的方式查看空值。

Lex de Haan (lex.de.haan@naturaljoin.nl) 是一位作家和讲师。 他在荷兰代尔夫特技术大学学习应用数学； 1990年至2004年就职于Oracle； 并且是 OakTable 网络的成员。 Jonathan Gennick (Jonathan@Gennick.com) 是一位经验丰富的 Oracle 专业人士，喜欢撰写有关 SQL 主题的文章。 他编写了 O’Reilly Media 的《SQL Pocket Guide》和《Oracle SQL*Plus Pocket Reference》。