企业官网建设流程全解析

Apache Spark SQL 的 NULL 语义详解：比较、逻辑、聚合、排序与子查询的完整行为指南

【免费下载链接】sparkApache Spark - A unified analytics engine for large-scale data processing项目地址: https://gitcode.com/gh_mirrors/sp/spark

导读

NULL是 SQL 中表示"未知值"的特殊标记，它的出现让比较、逻辑运算、聚合、排序乃至子查询的行为都变得与直觉不同。本文以 Apache Spark SQL（本仓库docs/sql-ref-null-semantics.md）为核心依据，结合sql/catalyst模块的表达式与排序实现源码，系统讲解 Spark SQL 中NULL值在比较运算符、逻辑运算符、各类表达式、聚合函数、WHERE/HAVING/JOIN、GROUP BY/DISTINCT、ORDER BY、集合运算符（UNION/INTERSECT/EXCEPT）以及EXISTS/IN子查询中的完整语义。读完本文，你将能够准确预测任意含NULL的 SQL 查询结果，并掌握<=>、IS NULL、NULLS FIRST/LAST等关键工具的实际用法。

本文所有示例均基于一张名为person的表，其结构与数据如下，后续各节示例全部复用该数据：

TABLE: person

比较运算符中的 NULL：三值逻辑的基石

Spark SQL 支持标准比较运算符>、>=、=、<、<=。当其中一个操作数或两个操作数均为NULL（未知）时，这些运算的结果同样是未知的，即返回NULL。

为了在等值比较中显式处理NULL，Spark 提供了空安全等值运算符<=>（null-safe equal）：当一个操作数为NULL、另一个非NULL时返回False；当两个操作数均为NULL时返回True。

下表总结了比较运算符在操作数含NULL时的行为：

示例

-- 常规比较运算符在任一操作数为 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| +-----------------+

从源码实现看，EqualTo（对应=）在 predicates.scala 中被标记为nullIntolerant: Boolean = true，即任一输入为NULL时整个表达式结果为NULL；而EqualNullSafe（对应<=>）则专门实现了"双NULL判等、单NULL判不等"的语义。<=>因此在 JOIN 条件、去重、集合运算等需要把NULL视为可比较值的场景中扮演关键角色。

逻辑运算符中的 NULL：AND / OR / NOT 的真值表

Spark 支持标准逻辑运算符AND、OR、NOT，它们以Boolean表达式为参数并返回Boolean值。由于NULL代表未知，逻辑运算遵循 SQL 三值逻辑（3VL）真值表。

下表展示OR与AND在操作数含NULL时的行为：

NOT的真值表：

示例

-- OR：一侧为 True，另一侧为 NULL，结果为 True SELECT (true OR null) AS expression_output; +-----------------+ |expression_output| +-----------------+ | true| +-----------------+ -- OR：一侧为 False，另一侧为 NULL，结果为 NULL（未知） SELECT (null OR false) AS expression_output; +-----------------+ |expression_output| +-----------------+ | null| +-----------------+ -- NOT：对 NULL 取反仍为 NULL SELECT NOT(null) AS expression_output; +-----------------+ |expression_output| +-----------------+ | null| +-----------------+

牢记这张真值表非常有用：例如age > 0 OR age IS NULL能筛选出"确定大于 0 或年龄未知"的行，而age > 0 OR age = NULL永远无法命中NULL行——因为age = NULL的结果是NULL，而True OR NULL = True、False OR NULL = NULL，后者被过滤条件丢弃。

表达式中的 NULL：两类截然不同的行为

比较与逻辑运算符在 Spark 中本质上也是表达式。除此之外，Spark 还支持函数表达式、CAST 表达式等多种形式。从NULL处理角度，表达式可大致分为两类：

• NULL 不容忍表达式（Null Intolerant Expressions）：只要有一个或多个参数为NULL，结果即为NULL，大多数表达式属于此类。
• 可处理 NULL 操作数的表达式：其结果取决于表达式自身，例如isnull对NULL输入返回true，coalesce返回操作数列表中第一个非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| +-----------------+

在源码层面，这类表达式普遍带有nullIntolerant = true标记（如前述EqualTo），Catalyst 优化器据此可以在优化阶段做空值传播与算子下推。

可处理 NULL 操作数的表达式

这类表达式专为NULL场景设计，典型成员包括（以下为非完整清单）：

• COALESCE
• NULLIF
• IFNULL
• NVL
• NVL2
• ISNAN
• NANVL
• ISNULL
• ISNOTNULL
• ATLEASTNNONNULLS
• IN

其中Coalesce、IsNull、IsNotNull等均实现在 nullExpressions.scala：Coalesce按顺序求值子表达式，返回第一个非NULL结果，其nullable属性为"所有子表达式均可空"时才为真；IsNull/IsNotNull的结果则恒为非空布尔值。此外还有一个面向内部优化用的谓词AtLeastNNonNulls（对应 SQL 层ATLEASTNNONNULLS），判断子表达式中非NULL且非NaN的值是否至少达到n个。

示例

SELECT isnull(null) AS expression_output; +-----------------+ |expression_output| +-----------------+ | true| +-----------------+ -- 返回第一个非 NULL 值 SELECT coalesce(null, null, 3, null) AS expression_output; +-----------------+ |expression_output| +-----------------+ | 3| +-----------------+ -- 所有操作数均为 NULL，coalesce 返回 NULL SELECT coalesce(null, null, null, null) AS expression_output; +-----------------+ |expression_output| +-----------------+ | null| +-----------------+ SELECT isnan(null) AS expression_output; +-----------------+ |expression_output| +-----------------+ | false| +-----------------+

注意isnan只对非NULL输入有意义：NULL输入时返回false（因为它不是数字，自然不是 NaN），对Double.NaN输入才返回true。

内置聚合函数对 NULL 的规则

聚合函数通过对一组输入行计算得到单一结果，其NULL处理规则如下：

• 除COUNT(*)外，所有聚合函数都会忽略NULL值，不参与计算。
• 当所有输入值均为NULL或输入数据集为空时，以下聚合函数返回NULL：
  • MAX、MIN、SUM、AVG、EVERY、ANY、SOME

示例

-- `count(*)` 不跳过 NULL 值，统计全部 7 行 SELECT count(*) FROM person; +--------+ |count(1)| +--------+ | 7| +--------+ -- `count(age)` 跳过 age 列的 NULL 值，仅统计 5 行 SELECT count(age) FROM person; +----------+ |count(age)| +----------+ | 5| +----------+ -- `count(DISTINCT age)` 同样跳过 NULL。这与 GROUP BY / SELECT DISTINCT 不同—— -- 后者会把所有 NULL 放在同一个分组（桶）里 SELECT count(DISTINCT age) FROM person; +-------------------+ |count(DISTINCT age)| +-------------------+ | 3| +-------------------+ -- 空输入集上 `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| +--------+

实践中，count(column)与count(*)的结果差异、以及空表上sum/avg返回NULL的"坑"，都是数据分析中高频出现的问题，务必依据上述规则预先判断。

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

WHERE、HAVING依据用户给定的条件过滤行，JOIN依据连接条件合并两个表的行。对三者而言，条件表达式都是布尔表达式，可能返回True、False或未知（NULL），并且只有当条件结果为True时该行才被"满足"——NULL和False一样会导致行被过滤掉。

示例

-- 年龄未知（NULL）的行被过滤出结果集 SELECT * FROM person WHERE age > 0; +--------+---+ | name|age| +--------+---+ |Michelle| 30| | Fred| 50| | Mike| 18| | Dan| 50| | Joe| 30| +--------+---+ -- 用 `IS NULL` 表达式配合 OR 选出年龄未知（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）的行被 HAVING 过滤 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| +--------+---+--------+---+ -- 连接两端的 age 列改用空安全等值（<=>）比较， -- 因此年龄未知（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| +--------+----+--------+----+

可以看到：age = p2.age这种普通等值连接会静默丢掉NULL行，而age <=> p2.age能把NULL与NULL匹配起来。这在处理外键可能为空的维度表关联时尤为关键。

聚合运算符（GROUP BY / DISTINCT）中的 NULL

如 比较运算符 一节所述，两个NULL值彼此"不相等"。但在分组与去重处理中，两个或多个值为NULL的数据会被归入同一个桶（分组）。该行为符合 SQL 标准，也与主流企业级数据库管理系统一致。

COUNT(DISTINCT expr)会忽略NULL值，只统计不同的非空值。

示例

-- GROUP BY 处理中，NULL 值被放入同一个桶 SELECT age, count(*) FROM person GROUP BY age; +----+--------+ | age|count(1)| +----+--------+ |null| 2| | 50| 2| | 30| 2| | 18| 1| +----+--------+ -- DISTINCT 处理中，所有 NULL 年龄被视为同一个去重值 SELECT DISTINCT age FROM person; +----+ | age| +----+ |null| | 50| | 30| | 18| +----+

这与前文count(DISTINCT age)返回 3（只统计非空去重值）形成鲜明对比：GROUP BY下 NULL 单独成组，DISTINCT下 NULL 只出现一次，但count(DISTINCT ...)直接把 NULL 全部忽略。

排序运算符（ORDER BY）中的 NULL

Spark SQL 在ORDER BY子句中支持空值排序规格（null ordering specification）。Spark 处理ORDER BY时，根据空值排序规格把所有NULL值放在最前或最后。默认情况下，所有NULL值放在最前。

从 SortOrder.scala 的实现看，Ascending方向的defaultNullOrdering是NullsFirst，Descending方向的是NullsLast——也就是说 Spark 对升序默认NULLS FIRST、降序默认NULLS LAST，这也解释了为什么ORDER BY age会把NULL排在最前面。SortOrder通过direction与nullOrdering两个维度完整刻画排序语义（如child.sql + " " + direction.sql + " " + nullOrdering.sql）。

示例

-- 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| +----+--------+

排序结论速记：升序时若想 NULL 在后，写NULLS LAST；降序时若想 NULL 在前，写NULLS FIRST。不写任何修饰时，升序默认 NULL 在前、降序默认 NULL 在后。

集合运算符（UNION / INTERSECT / EXCEPT）中的 NULL

在集合运算的上下文中，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 值行不出现在输出中，这正说明比较以空安全方式进行 -- （即两边的 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| +--------+----+

INTERSECT示例中两条含NULL的行（Albert、Marry）能成功匹配，正是因为集合运算内部采用空安全比较——如果用普通=做 JOIN，这两行会被丢弃（见前面 JOIN 示例）。

EXISTS / NOT EXISTS 子查询

在 Spark 中，EXISTS和NOT EXISTS表达式允许出现在WHERE子句中，二者都是返回TRUE或FALSE的布尔表达式。EXISTS是成员条件（membership condition），当子查询返回一行或多行时为TRUE；NOT EXISTS是非成员条件，当子查询返回零行时为TRUE。

这两个表达式不受子查询结果中 NULL 的影响。它们通常执行更快，因为可以被转换为半连接（semijoin）/ 反半连接（anti-semijoin），且无需为空感知做特殊处理。

示例

-- 即使子查询产生的是含 NULL 值的行，只要产生了 1 行， -- EXISTS 表达式就求值为 TRUE 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)。

因此，IN处理NULL的语义可以从前述比较运算符（=）和逻辑运算符（OR）的NULL行为推导出来。归纳如下：

• 当列表中找到该非NULL值时，返回TRUE；
• 当列表中未找到该非NULL值、且列表不包含NULL值时，返回FALSE；
• 当该值本身是NULL，或非NULL值未在列表中找到但列表至少含一个NULL值时，返回UNKNOWN。

当列表包含NULL时，NOT IN无论输入值是什么都返回UNKNOWN。原因是：如果值不在含NULL的列表中，IN返回UNKNOWN，而NOT UNKNOWN仍是UNKNOWN。

示例

-- 子查询结果集只有 NULL 值，因此 IN 谓词的结果是 UNKNOWN， -- 没有任何行满足条件 SELECT * FROM person WHERE age IN (SELECT null); +----+---+ |name|age| +----+---+ +----+---+ -- 子查询结果集中既有 NULL 也有合法值 50。 -- 年龄为 50 的行被返回（Fred、Dan）；其他行因匹配结果 -- 为 UNKNOWN 或 FALSE 而被过滤 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| +----+---+ +----+---+

最后一个示例是实际开发中最容易踩的"坑"：只要NOT IN右侧列表出现任何NULL，整个谓词恒为UNKNOWN，导致查询结果为空。需要排除某集合时，更稳妥的做法是改用NOT EXISTS或先过滤掉列表中的NULL。

小结：NULL 语义速查表

理解并熟练运用上述语义，是写出正确、可预测的 Spark SQL 查询的前提。更多相关 SQL 语义与语法说明，可继续查阅本仓库的 SQL 参考文档 及其子章节（如 sql-ref-ansi-compliance.md），并结合 sql/catalyst 的表达式实现源码深入验证每条规则。

【免费下载链接】sparkApache Spark - A unified analytics engine for large-scale data processing项目地址: https://gitcode.com/gh_mirrors/sp/spark