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: personIdNameAge100Joe30200MarryNULL300Mike18400Fred50500AlbertNULL600Michelle30700Dan50比较运算符中的 NULL三值逻辑的基石Spark SQL 支持标准比较运算符、、、、。当其中一个操作数或两个操作数均为NULL未知时这些运算的结果同样是未知的即返回NULL。为了在等值比较中显式处理NULLSpark 提供了空安全等值运算符null-safe equal当一个操作数为NULL、另一个非NULL时返回False当两个操作数均为NULL时返回True。下表总结了比较运算符在操作数含NULL时的行为Left OperandRight OperandNULLAny valueNULLNULLNULLNULLNULLFalseAny valueNULLNULLNULLNULLNULLNULLFalseNULLNULLNULLNULLNULLNULLNULLTrue示例-- 常规比较运算符在任一操作数为 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视为可比较值的场景中扮演关键角色。逻辑运算符中的 NULLAND / OR / NOT 的真值表Spark 支持标准逻辑运算符AND、OR、NOT它们以Boolean表达式为参数并返回Boolean值。由于NULL代表未知逻辑运算遵循 SQL 三值逻辑3VL真值表。下表展示OR与AND在操作数含NULL时的行为Left OperandRight OperandORANDTrueNULLTrueNULLFalseNULLNULLFalseNULLTrueTrueNULLNULLFalseNULLFalseNULLNULLNULLNULLNOT的真值表operandNOTNULLNULL示例-- 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输入返回truecoalesce返回操作数列表中第一个非NULL值。NULL 不容忍表达式只要任一参数为NULL结果即为NULLSELECT 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标记如前述EqualToCatalyst 优化器据此可以在优化阶段做空值传播与算子下推。可处理 NULL 操作数的表达式这类表达式专为NULL场景设计典型成员包括以下为非完整清单COALESCENULLIFIFNULLNVLNVL2ISNANNANVLISNULLISNOTNULLATLEASTNNONNULLSIN其中Coalesce、IsNull、IsNotNull等均实现在 nullExpressions.scalaCoalesce按顺序求值子表达式返回第一个非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| ----------------- -- 所有操作数均为 NULLcoalesce 返回 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或输入数据集为空时以下聚合函数返回NULLMAX、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中的 NULLSpark SQL 在ORDER BY子句中支持空值排序规格null ordering specification。Spark 处理ORDER BY时根据空值排序规格把所有NULL值放在最前或最后。默认情况下所有NULL值放在最前。从 SortOrder.scala 的实现看Ascending方向的defaultNullOrdering是NullsFirstDescending方向的是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当子查询返回一行或多行时为TRUENOT 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 语义速查表场景NULL 的行为比较运算符、等任一操作数为 NULL 即返回 NULL空安全等值双 NULL 返回 TRUE单 NULL 返回 FALSEAND/OR/NOT遵循三值逻辑TRUE OR NULL TRUE、FALSE OR NULL NULL、NOT NULL NULLNULL 不容忍表达式任一参数为 NULL 即返回 NULLcoalesce/isnull等结果取决于表达式自身coalesce取首个非 NULLisnull(NULL)为 TRUE聚合函数忽略 NULL仅COUNT(*)例外全 NULL 或空集时MAX/MIN/SUM/AVG等返回 NULLWHERE/HAVING/JOIN条件只有结果为 TRUE 才满足NULL 与 FALSE 一样导致行被过滤GROUP BY/DISTINCT所有 NULL 归入同一桶/被视为同一个去重值COUNT(DISTINCT ...)忽略 NULLORDER BY升序默认 NULL 在前降序默认 NULL 在后可用NULLS FIRST/LAST显式指定UNION/INTERSECT/EXCEPT行内列按空安全方式比较两个 NULL 视为相等EXISTS/NOT EXISTS只关心子查询是否有行不受 NULL 影响IN/NOT ININ可返回 UNKNOWN列表含 NULL 时NOT IN恒为 UNKNOWN理解并熟练运用上述语义是写出正确、可预测的 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创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考