空值语义

描述

一个表由一组行组成，每行包含一组列。列与数据类型相关，表示实体的一个特定属性（例如， age 是一个名为 person 的实体的列）。有时，在行存在时，特定于该行的列的值是未知的。在 SQL 中，这种值表示为 NULL 。本节详细说明了 NULL 值在各种操作符、表达式和其他 SQL 结构中的处理语义。

比较运算符中的空值处理
逻辑运算符中的空值处理
表达式中的空值处理
在WHERE, HAVING和JOIN条件中的空值处理
在GROUP BY和DISTINCT中的空值处理
在ORDER BY中的空值处理
在UNION, INTERSECT, EXCEPT中的空值处理
在EXISTS和NOT EXISTS子查询中的空值处理
在IN和NOT IN子查询中的空值处理

以下展示了一个名为 person 的表的模式布局和数据。数据在 age 列中包含 NULL 值，并且该表将在下面的各个例子中使用。
表： person

编号	姓名	年龄
100	Joe	30
200	Marry	NULL
300	Mike	18
400	Fred	50
500	Albert	NULL
600	Michelle	30
700	Dan	50

比较运算符

Apache spark 支持标准的比较运算符，如‘>’, ‘>=’, ‘=’, ‘<’ 和 ‘<=’. 当运算数中的一个或两个都是未知的或 NULL 时，这些运算符的结果是未知的或 NULL 。为了比较 NULL 值的相等性，Spark 提供了一个 null-safe 等于运算符 (‘<=>’)，当其中一个运算数为 NULL 时返回 False ，当两个运算数都是 NULL 时返回‘True 。以下表格说明了当一个或两个运算数为 NULL 时比较运算符的行为：

左操作数	右操作数	>	>=	=	<	<=	<=>
NULL	任意值	NULL	NULL	NULL	NULL	NULL	False
任意值	NULL	NULL	NULL	NULL	NULL	NULL	False
NULL	NULL	NULL	NULL	NULL	NULL	NULL	True

示例

      -- 普通比较运算符在其中一个操作数为 `NULL` 时返回 `NULL`。
SELECT 5 > null AS expression_output;
+-----------------+
|expression_output|
+-----------------+
| null|
+-----------------+
-- 普通比较运算符在两个操作数都是 `NULL` 时返回 `NULL`。
SELECT null = null AS expression_output;
+-----------------+
|expression_output|
+-----------------+
| null|
+-----------------+
-- 空安全相等运算符在其中一个操作数为 `NULL` 时返回 `False`
SELECT 5 <=> null AS expression_output;
+-----------------+
|expression_output|
+-----------------+
| false|
+-----------------+
-- 空安全相等运算符在两个操作数都是 `NULL` 时返回 `True`
SELECT NULL <=> NULL;
+-----------------+
|expression_output|
+-----------------+
| true|
+-----------------+

     

逻辑运算符

Spark 支持标准的逻辑运算符，例如 AND 、 OR 和 NOT 。这些运算符接受 Boolean 表达式作为参数，并返回一个 Boolean 值。

以下表格说明了逻辑运算符在一个或两个操作数为 NULL 时的行为。

左操作数	右操作数	或	与
真	空	真	空
假	空	空	假
空	真	真	空
空	假	空	假
空	空	空	空

操作数	非
空	空

示例

      -- 普通比较运算符在其中一个操作数为 `NULL` 时返回 `NULL`。
SELECT (true OR null) AS expression_output;
+-----------------+
|expression_output|
+-----------------+
| true|
+-----------------+
-- 普通比较运算符在两个操作数都是 `NULL` 时返回 `NULL`。
SELECT (null OR false) AS expression_output
+-----------------+
|expression_output|
+-----------------+
| null|
+-----------------+
-- 安全的相等运算符在其中一个操作数为 `NULL` 时返回 `False`
SELECT NOT(null) AS expression_output;
+-----------------+
|expression_output|
+-----------------+
| null|
+-----------------+

     

表达式

比较运算符和逻辑运算符在Spark中被视为表达式。除了这两种表达式外，Spark还支持其他形式的表达式，例如函数表达式、类型转换表达式等。Spark中的表达式可以大致分为：

不宽容空值的表达式
能够处理 NULL 值操作数的表达式
- 这些表达式的结果取决于表达式本身。

非空容忍表达式

对NULL不容忍的表达式在表达式的一个或多个参数为 NULL 时返回 NULL ，大多数表达式都属于这一类别。

示例

      SELECT concat('John', null) AS expression_output;
+-----------------+
|expression_output|
+-----------------+
| null|
+-----------------+
SELECT positive(null) AS expression_output;
+-----------------+
|expression_output|
+-----------------+
| null|
+-----------------+
SELECT to_date(null) AS expression_output;
+-----------------+
|expression_output|
+-----------------+
| null|
+-----------------+

     

可以处理空值操作数的表达式

这一类表达式旨在处理 NULL 值。表达式的结果取决于表达式本身。例如，函数表达式 isnull 在输入为 null 时返回 true ，在输入为非 null 时返回 false ，而函数 coalesce 返回其操作数列表中的第一个非 NULL 值。然而，当所有操作数都为 NULL 时， coalesce 返回 NULL 。下面是这一类别表达式的不完整列表。

COALESCE
NULLIF
IFNULL
NVL
NVL2
ISNAN
NANVL
ISNULL
ISNOTNULL
ATLEASTNNONNULLS
在

示例

      SELECT isnull(null) AS expression_output;
+-----------------+
|expression_output|
+-----------------+
| true|
+-----------------+
-- 返回非 `NULL` 值的第一次出现。
SELECT coalesce(null, null, 3, null) AS expression_output;
+-----------------+
|expression_output|
+-----------------+
| 3|
+-----------------+
-- 由于所有操作数都是 `NULL`，因此返回 `NULL`。
SELECT coalesce(null, null, null, null) AS expression_output;
+-----------------+
|expression_output|
+-----------------+
| null|
+-----------------+
SELECT isnan(null) AS expression_output;
+-----------------+
|expression_output|
+-----------------+
| false|
+-----------------+

     

内置聚合表达式

聚合函数通过处理一组输入行来计算单个结果。以下是聚合函数如何处理 NULL 值的规则。

NULL 值在所有聚合函数的处理过程中被忽略。
- 这个规则唯一的例外是 COUNT(*) 函数。
当所有输入值为 NULL 或输入数据集为空时，一些聚合函数返回 NULL 。
这些函数的列表是：
- MAX
- MIN
- SUM
- AVG
- EVERY
- ANY
- SOME

示例

      -- `count(*)` 不会跳过 `NULL` 值。
SELECT count(*) FROM person;
+--------+
|count(1)|
+--------+
| 7|
+--------+
-- 列 `age` 中的 `NULL` 值在处理时会被跳过。
SELECT count(age) FROM person;
+----------+
|count(age)|
+----------+
| 5|
+----------+
-- 在空输入集上使用 `count(*)` 返回 0。这与其他
-- 聚合函数不同，例如 `max`，它返回 `NULL`。
SELECT count(*) FROM person where 1 = 0;
+--------+
|count(1)|
+--------+
| 0|
+--------+
-- `NULL` 值在最大值计算中被排除。
SELECT max(age) FROM person;
+--------+
|max(age)|
+--------+
| 50|
+--------+
-- 在空输入集上，`max` 返回 `NULL`。
SELECT max(age) FROM person where 1 = 0;
+--------+
|max(age)|
+--------+
| null|
+--------+

     

WHERE、HAVING和JOIN子句中的条件表达式

WHERE ， HAVING 操作符根据用户指定的条件过滤行。一个 JOIN 操作符用于根据连接条件组合两个表的行。对于这三个操作符，条件表达式是布尔表达式，并可以返回 True, False or Unknown (NULL) 。当条件的结果为 True 时，它们被“满足”。

示例

      -- 年龄未知（`NULL`）的人被从结果集中筛选出去。
SELECT * FROM person WHERE age > 0;
+--------+---+
| name|age|
+--------+---+
|Michelle| 30|
| Fred| 50|
| Mike| 18|
| Dan| 50|
| Joe| 30|
+--------+---+
-- `IS NULL` 表达式用于选择记录未知（`NULL`）的人。
-- 拥有未知(`NULL`)记录的人。
SELECT * FROM person WHERE age > 0 OR age IS NULL;
+--------+----+
| name| age|
+--------+----+
| Albert|null|
|Michelle| 30|
| Fred| 50|
| Mike| 18|
| Dan| 50|
| Marry|null|
| Joe| 30|
+--------+----+
-- 年龄未知(`NULL`)的人在处理时被跳过。
SELECT age, count(*) FROM person GROUP BY age HAVING max(age) > 18;
+---+--------+
|age|count(1)|
+---+--------+
| 50| 2|
| 30| 2|
+---+--------+
-- 自连接案例，连接条件为 `p1.age = p2.age AND p1.name = p2.name`。
-- 拥有未知年龄（`NULL`）的人通过连接操作被筛选出去。
SELECT * FROM person p1, person p2
WHERE p1.age = p2.age
AND p1.name = p2.name;
+--------+---+--------+---+
| name|age| name|age|
+--------+---+--------+---+
|Michelle| 30|Michelle| 30|
| Fred| 50| Fred| 50|
| Mike| 18| Mike| 18|
| Dan| 50| Dan| 50|
| Joe| 30| Joe| 30|
+--------+---+--------+---+
-- 连接的两个部分的年龄列使用安全空比较，这就是为什么拥有未知年龄（`NULL`）的人通过连接符合条件。
SELECT * FROM person p1, person p2
WHERE p1.age <=> p2.age
AND p1.name = p2.name;
+--------+----+--------+----+
| name| age| name| age|
+--------+----+--------+----+
| Albert|null| Albert|null|
|Michelle| 30|Michelle| 30|
| Fred| 50| Fred| 50|
| Mike| 18| Mike| 18|
| Dan| 50| Dan| 50|
| Marry|null| Marry|null|
| Joe| 30| Joe| 30|
+--------+----+--------+----+

     

聚合操作符 (GROUP BY, DISTINCT)

在上一节中讨论的比较运算符中，两个 NULL 值不相等。然而，为了分组和去重处理，两个或多个 NULL 数据 值会被分到同一个桶中。这个行为符合SQL标准以及其他企业数据库管理系统。

示例

      -- `NULL` 值在 `GROUP BY` 处理中被放在一个组中。
SELECT age, count(*) FROM person GROUP BY age;
+----+--------+
| age|count(1)|
+----+--------+
|null| 2|
| 50| 2|
| 30| 2|
| 18| 1|
+----+--------+
-- 所有 `NULL` 年龄在 `DISTINCT` 处理中被视为一个不同的值。
SELECT DISTINCT age FROM person;
+----+
| age|
+----+
|null|
| 50|
| 30|
| 18|
+----+

     

排序运算符（ORDER BY 子句）

Spark SQL 支持在 ORDER BY 子句中进行空值排序规范。Spark 处理 ORDER BY 子句时，依据空值排序规范将所有 NULL 值放在最前面或最后面。默认情况下，所有 NULL 值被放置在最前面。

示例

      -- `NULL` 值首先显示，其他值
-- 按升序排列。
SELECT age, name FROM person ORDER BY age;
+----+--------+
| age| name|
+----+--------+
|null| Marry|
|null| Albert|
| 18| Mike|
| 30|Michelle|
| 30| Joe|
| 50| Fred|
| 50| Dan|
+----+--------+
-- 除了 `NULL` 的列值按升序
-- 排列，`NULL` 值显示在最后。
SELECT age, name FROM person ORDER BY age NULLS LAST;
+----+--------+
| age| name|
+----+--------+
| 18| Mike|
| 30|Michelle|
| 30| Joe|
| 50| Dan|
| 50| Fred|
|null| Marry|
|null| Albert|
+----+--------+
-- 除了 `NULL` 值的列按降序
-- 排列，`NULL` 值显示在最后。
SELECT age, name FROM person ORDER BY age DESC NULLS LAST;
+----+--------+
| age| name|
+----+--------+
| 50| Fred|
| 50| Dan|
| 30|Michelle|
| 30| Joe|
| 18| Mike|
|null| Marry|
|null| Albert|
+----+--------+

     

集合运算符（UNION, INTERSECT, EXCEPT）

NULL 值在集合操作的上下文中以安全空值的方式进行相等比较。这意味着在比较行时，两个 NULL 值被视为相等，这与常规的 EqualTo ( = ) 操作符不同。

示例

      CREATE VIEW unknown_age SELECT * FROM person WHERE age IS NULL;
-- 只有两个 `INTERSECT` 的共同行在结果集中。
-- 行的列之间的比较是以安全的方式进行的。
SELECT name, age FROM person
INTERSECT
SELECT name, age from unknown_age;
+------+----+
| name| age|
+------+----+
|Albert|null|
| Marry|null|
+------+----+
-- `EXCEPT` 的两个部分中的 `NULL` 值不在输出中。
-- 这基本上表明比较以安全的方式进行。
SELECT age, name FROM person
EXCEPT
SELECT age FROM unknown_age;
+---+--------+
|age| name|
+---+--------+
| 30| Joe|
| 50| Fred|
| 30|Michelle|
| 18| Mike|
| 50| Dan|
+---+--------+
-- 在两组数据之间执行 `UNION` 操作。
-- 行之间的列比较以安全方式进行。
SELECT name, age FROM person
UNION
SELECT name, age FROM unknown_age;
+--------+----+
| name| age|
+--------+----+
| Albert|null|
| Joe| 30|
|Michelle| 30|
| Marry|null|
| Fred| 50|
| Mike| 18|
| Dan| 50|
+--------+----+

     

EXISTS/NOT EXISTS 子查询

在Spark中，EXISTS和NOT EXISTS表达式被允许在WHERE子句中使用。这些是布尔表达式，返回 TRUE 或 FALSE 。换句话说，EXISTS是一个成员条件，当它所引用的子查询返回一行或多行时返回 TRUE 。类似地，NOT EXISTS 是一个非成员条件，当子查询返回零行或没有行时返回 TRUE。

这两个表达式不受子查询结果中NULL存在的影响。由于它们可以在不需要特殊处理空值的情况下转换为半连接/反半连接，因此通常更快。

示例

      -- 即使子查询生成包含 `NULL` 值的行，`EXISTS` 表达式
-- 仍会评估为 `TRUE`，因为子查询生成了 1 行。
SELECT * FROM person WHERE EXISTS (SELECT null);
+--------+----+
| name| age|
+--------+----+
| Albert|null|
|Michelle| 30|
| Fred| 50|
| Mike| 18|
| Dan| 50|
| Marry|null|
| Joe| 30|
+--------+----+
-- `NOT EXISTS` 表达式返回 `FALSE`。只有当
-- 子查询不生成任何行时，它才返回 `TRUE`。在这种情况下，它返回 1 行。
SELECT * FROM person WHERE NOT EXISTS (SELECT null);
+----+---+
|name|age|
+----+---+
+----+---+
-- `NOT EXISTS` 表达式返回 `TRUE`。
SELECT * FROM person WHERE NOT EXISTS (SELECT 1 WHERE 1 = 0);
+--------+----+
| name| age|
+--------+----+
| Albert|null|
|Michelle| 30|
| Fred| 50|
| Mike| 18|
| Dan| 50|
| Marry|null|
| Joe| 30|
+--------+----+

     

IN/NOT IN 子查询

在Spark中， IN 和 NOT IN 表达式被允许在查询的WHERE子句中使用。与 EXISTS 表达式不同， IN 表达式可以返回 TRUE 、 FALSE 或 UNKNOWN (NULL) 值。从概念上讲， IN 表达式在语义上等同于通过析取运算符（ OR ）分隔的一组相等条件。例如，c1 IN (1, 2, 3) 在语义上等同于 (C1 = 1 OR c1 = 2 OR c1 = 3) 。

对于处理 NULL 值，语义可以从比较运算符( = ) 和逻辑运算符( OR ) 中的 NULL 值处理推导出来。总之，以下是计算 IN 表达式结果的规则。

当在列表中找到非NULL值时，返回TRUE
当在列表中找不到非NULL值且列表不包含NULL值时，返回FALSE
当值为 NULL ，或者在列表中找不到非NULL值并且列表中至少包含一个 NULL 值时，返回UNKNOWN

NOT IN 总是返回 UNKNOWN，当列表包含 NULL 时，不论输入值是什么。这是因为如果值不在包含 NULL 的列表中，IN 返回 UNKNOWN，并且 NOT UNKNOWN 再次是 UNKNOWN。

示例

      -- 子查询在其结果集中只有 `NULL` 值。因此，
-- `IN` 谓词的结果是 UNKNOWN。
SELECT * FROM person WHERE age IN (SELECT null);
+----+---+
|name|age|
+----+---+
+----+---+
-- 子查询在结果集中有 `NULL` 值以及一个有效的 
-- 值 `50`。 返回年龄为 50 的行。
SELECT * FROM person
WHERE age IN (SELECT age FROM VALUES (50), (null) sub(age));
+----+---+
|name|age|
+----+---+
|Fred| 50|
| Dan| 50|
+----+---+
-- 因为子查询在结果集中有 `NULL` 值，`NOT IN`
-- 谓词将返回 UNKNOWN。因此，没有行
-- 符合这个查询的条件。
SELECT * FROM person
WHERE age NOT IN (SELECT age FROM VALUES (50), (null) sub(age));
+----+---+
|name|age|
+----+---+
+----+---+

     

Spark SQL指南