NULL 语义

描述

表由一组行组成,每行包含一组列。列与数据类型关联,并表示实体的特定属性(例如,age 是一个名为 person 的实体的列)。有时,在行创建时,特定于行的列的值是未知的。在 SQL 中,这些值表示为 NULL。本节详细介绍了各种运算符、表达式和其他 SQL 构造中处理 NULL 值的语义。

  1. 比较运算符中的 Null 处理
  2. 逻辑运算符中的 Null 处理
  3. 表达式中的 Null 处理
    1. Null 不容忍表达式中的 Null 处理
    2. 可以处理 null 值操作数的表达式中的 Null 处理
    3. 内置聚合表达式中的 Null 处理
  4. WHERE、HAVING 和 JOIN 条件中的 Null 处理
  5. GROUP BY 和 DISTINCT 中的 Null 处理
  6. ORDER BY 中的 Null 处理
  7. UNION、INTERSECT、EXCEPT 中的 Null 处理
  8. EXISTS 和 NOT EXISTS 子查询中的 Null 处理
  9. IN 和 NOT IN 子查询中的 Null 处理

以下示例说明了名为 person 的表的模式布局和数据。 数据在 age 列中包含 NULL 值,此表将用于以下各节中的各种示例。
表:person

Id Name Age
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 安全的相等运算符 (‘<=>’),当其中一个操作数为 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

示例

-- Normal comparison operators return `NULL` when one of the operand is `NULL`.
SELECT 5 > null AS expression_output;
+-----------------+
|expression_output|
+-----------------+
|             null|
+-----------------+

-- Normal comparison operators return `NULL` when both the operands are `NULL`.
SELECT null = null AS expression_output;
+-----------------+
|expression_output|
+-----------------+
|             null|
+-----------------+

-- Null-safe equal operator return `False` when one of the operand is `NULL`
SELECT 5 <=> null AS expression_output;
+-----------------+
|expression_output|
+-----------------+
|            false|
+-----------------+

-- Null-safe equal operator return `True` when one of the operand is `NULL`
SELECT NULL <=> NULL;
+-----------------+
|expression_output|
+-----------------+
|             true|
+-----------------+

逻辑运算符

Spark 支持标准逻辑运算符,例如 ANDORNOT。 这些运算符将 Boolean 表达式作为参数,并返回一个 Boolean 值。

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

左操作数 右操作数 OR AND
True NULL True NULL
False NULL NULL False
NULL True True NULL
NULL False NULL False
NULL NULL NULL NULL
操作数 NOT
NULL NULL

示例

-- Normal comparison operators return `NULL` when one of the operands is `NULL`.
SELECT (true OR null) AS expression_output;
+-----------------+
|expression_output|
+-----------------+
|             true|
+-----------------+

-- Normal comparison operators return `NULL` when both the operands are `NULL`.
SELECT (null OR false) AS expression_output
+-----------------+
|expression_output|
+-----------------+
|             null|
+-----------------+

-- Null-safe equal operator returns `False` when one of the operands is `NULL`
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 值操作数的表达式

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

示例
SELECT isnull(null) AS expression_output;
+-----------------+
|expression_output|
+-----------------+
|             true|
+-----------------+

-- Returns the first occurrence of non `NULL` value.
SELECT coalesce(null, null, 3, null) AS expression_output;
+-----------------+
|expression_output|
+-----------------+
|                3|
+-----------------+

-- Returns `NULL` as all its operands are `NULL`. 
SELECT coalesce(null, null, null, null) AS expression_output;
+-----------------+
|expression_output|
+-----------------+
|             null|
+-----------------+

SELECT isnan(null) AS expression_output;
+-----------------+
|expression_output|
+-----------------+
|            false|
+-----------------+

内置聚合表达式

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

示例

-- `count(*)` does not skip `NULL` values.
SELECT count(*) FROM person;
+--------+
|count(1)|
+--------+
|       7|
+--------+

-- `NULL` values in column `age` are skipped from processing.
SELECT count(age) FROM person;
+----------+
|count(age)|
+----------+
|         5|
+----------+

-- `count(*)` on an empty input set returns 0. This is unlike the other
-- aggregate functions, such as `max`, which return `NULL`.
SELECT count(*) FROM person where 1 = 0;
+--------+
|count(1)|
+--------+
|       0|
+--------+

-- `NULL` values are excluded from computation of maximum value.
SELECT max(age) FROM person;
+--------+
|max(age)|
+--------+
|      50|
+--------+

-- `max` returns `NULL` on an empty input set.
SELECT max(age) FROM person where 1 = 0;
+--------+
|max(age)|
+--------+
|    null|
+--------+

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

WHERE, HAVING 运算符根据用户指定的条件过滤行。 JOIN 运算符用于根据连接条件组合来自两个表的行。 对于所有这三个运算符,条件表达式是一个布尔表达式,可以返回 True、False 或 Unknown (NULL)。 如果条件的结果为 True,则它们被“满足”。

示例

-- Persons whose age is unknown (`NULL`) are filtered out from the result set.
SELECT * FROM person WHERE age > 0;
+--------+---+
|    name|age|
+--------+---+
|Michelle| 30|
|    Fred| 50|
|    Mike| 18|
|     Dan| 50|
|     Joe| 30|
+--------+---+

-- `IS NULL` expression is used in disjunction to select the persons
-- with unknown (`NULL`) records.
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|
+--------+----+

-- Person with unknown(`NULL`) ages are skipped from processing.
SELECT age, count(*) FROM person GROUP BY age HAVING max(age) > 18;
+---+--------+
|age|count(1)|
+---+--------+
| 50|       2|
| 30|       2|
+---+--------+

-- A self join case with a join condition `p1.age = p2.age AND p1.name = p2.name`.
-- The persons with unknown age (`NULL`) are filtered out by the join operator.
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|
+--------+---+--------+---+

-- The age column from both legs of join are compared using null-safe equal which
-- is why the persons with unknown age (`NULL`) are qualified by the join.
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` values are put in one bucket in `GROUP BY` processing.
SELECT age, count(*) FROM person GROUP BY age;
+----+--------+
| age|count(1)|
+----+--------+
|null|       2|
|  50|       2|
|  30|       2|
|  18|       1|
+----+--------+

-- All `NULL` ages are considered one distinct value in `DISTINCT` processing.
SELECT DISTINCT age FROM person;
+----+
| age|
+----+
|null|
|  50|
|  30|
|  18|
+----+

排序运算符 (ORDER BY 子句)

Spark SQL 支持 ORDER BY 子句中的 null 排序规范。 Spark 通过根据 null 排序规范将所有 NULL 值放在开头或结尾来处理 ORDER BY 子句。 默认情况下,所有 NULL 值都放在开头。

示例

-- `NULL` values are shown at first and other values
-- are sorted in ascending way.
SELECT age, name FROM person ORDER BY age;
+----+--------+
| age|    name|
+----+--------+
|null|   Marry|
|null|  Albert|
|  18|    Mike|
|  30|Michelle|
|  30|     Joe|
|  50|    Fred|
|  50|     Dan|
+----+--------+

-- Column values other than `NULL` are sorted in ascending
-- way and `NULL` values are shown at the last.
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|
+----+--------+

-- Columns other than `NULL` values are sorted in descending
-- and `NULL` values are shown at the last.
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 安全的方式进行比较以确定相等性。 这意味着在比较行时,两个 NULL 值被认为是相等的,这与常规 EqualTo(=) 运算符不同。

示例

CREATE VIEW unknown_age SELECT * FROM person WHERE age IS NULL;

-- Only common rows between two legs of `INTERSECT` are in the 
-- result set. The comparison between columns of the row are done
-- in a null-safe manner.
SELECT name, age FROM person
    INTERSECT
    SELECT name, age from unknown_age;
+------+----+
|  name| age|
+------+----+
|Albert|null|
| Marry|null|
+------+----+

-- `NULL` values from two legs of the `EXCEPT` are not in output. 
-- This basically shows that the comparison happens in a null-safe manner.
SELECT age, name FROM person
    EXCEPT
    SELECT age FROM unknown_age;
+---+--------+
|age|    name|
+---+--------+
| 30|     Joe|
| 50|    Fred|
| 30|Michelle|
| 18|    Mike|
| 50|     Dan|
+---+--------+

-- Performs `UNION` operation between two sets of data. 
-- The comparison between columns of the row ae done in
-- null-safe manner.
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 中,允许在 WHERE 子句中使用 EXISTS 和 NOT EXISTS 表达式。 这些是布尔表达式,它们返回 TRUEFALSE。 换句话说,EXISTS 是一种成员资格条件,当它引用的子查询返回一行或多行时,返回 TRUE。 类似地,NOT EXISTS 是一种非成员资格条件,当没有行或零行从子查询返回时,返回 TRUE。

这两个表达式不受子查询结果中 NULL 的存在的影响。 它们通常更快,因为它们可以转换为半连接/反半连接,而无需为 null 感知提供特殊规定。

示例

-- Even if subquery produces rows with `NULL` values, the `EXISTS` expression
-- evaluates to `TRUE` as the subquery produces 1 row.
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` expression returns `FALSE`. It returns `TRUE` only when
-- subquery produces no rows. In this case, it returns 1 row.
SELECT * FROM person WHERE NOT EXISTS (SELECT null);
+----+---+
|name|age|
+----+---+
+----+---+

-- `NOT EXISTS` expression returns `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 中,INNOT IN 表达式允许在查询的 WHERE 子句中使用。与 EXISTS 表达式不同,IN 表达式可以返回 TRUEFALSEUNKNOWN (NULL) 值。从概念上讲,IN 表达式在语义上等同于一组由析取运算符(OR)分隔的相等条件。 例如,c1 IN (1, 2, 3) 在语义上等同于 (C1 = 1 OR c1 = 2 OR c1 = 3)

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

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

示例

-- The subquery has only `NULL` value in its result set. Therefore,
-- the result of `IN` predicate is UNKNOWN.
SELECT * FROM person WHERE age IN (SELECT null);
+----+---+
|name|age|
+----+---+
+----+---+

-- The subquery has `NULL` value in the result set as well as a valid 
-- value `50`. Rows with age = 50 are returned. 
SELECT * FROM person
    WHERE age IN (SELECT age FROM VALUES (50), (null) sub(age));
+----+---+
|name|age|
+----+---+
|Fred| 50|
| Dan| 50|
+----+---+

-- Since subquery has `NULL` value in the result set, the `NOT IN`
-- predicate would return UNKNOWN. Hence, no rows are
-- qualified for this query.
SELECT * FROM person
    WHERE age NOT IN (SELECT age FROM VALUES (50), (null) sub(age));
+----+---+
|name|age|
+----+---+
+----+---+