在 Hive 中NULL的理解

在 Hive 中，NULL 是一个特殊的值，表示“未知”或“缺失”。任何与 NULL 的比较操作（如 =, >, <, >=, <=, <>）都会返回 NULL，而不是 TRUE 或 FALSE。

---

1. NULL 的比较规则

在 Hive（以及大多数 SQL 数据库）中，NULL 的比较遵循 三值逻辑（Three-Valued Logic）：

• TRUE
• FALSE
• UNKNOWN（即 NULL）

任何与 NULL 的比较结果都是 UNKNOWN（即 NULL）。

---

2. 具体示例

假设有一个表 test：

value
5
NULL
3

2.1 NULL > 1 返回什么？

SELECT value, value > 1 AS result FROM test;

结果：

value	result
5	true
NULL	NULL
3	true

• NULL > 1 → NULL（未知）
• NULL < 1 → NULL
• NULL = 1 → NULL
• NULL <> 1 → NULL

2.2 NULL = NULL 返回什么？

SELECT NULL = NULL AS result;

结果： NULL（不是 true！）

• 在 SQL 中，NULL 不等于任何值，包括它自己。
• 要判断两个值是否都为 NULL，必须用 IS NULL。

2.3 IS NULL 和 IS NOT NULL

SELECT value, value IS NULL AS is_null FROM test;

结果：

value	is_null
5	false
NULL	true
3	false

• IS NULL 是唯一能正确判断 NULL 的操作。

---

3. WHERE 子句中的 NULL 行为

SELECT * FROM test WHERE value > 1;

结果：

value
5
3

• NULL 被过滤掉了！因为 NULL > 1 返回 NULL，而 WHERE 只保留 TRUE 的行。

如果你想保留 NULL，必须显式判断：

SELECT * FROM test WHERE value > 1 OR value IS NULL;

---

4. NULL 的逻辑运算

• TRUE AND NULL → NULL
• FALSE AND NULL → FALSE
• TRUE OR NULL → TRUE
• FALSE OR NULL → NULL
• NOT NULL → NULL

---

5. 如何正确处理 NULL

5.1 使用 IS NULL / IS NOT NULL

SELECT * FROM test WHERE value IS NULL;

5.2 使用 COALESCE 提供默认值

SELECT COALESCE(value, 0) AS safe_value FROM test;

5.3 使用 NVL（Hive 特有）

SELECT NVL(value, 0) AS safe_value FROM test;

5.4 使用 CASE WHEN

SELECT 
    CASE 
        WHEN value IS NULL THEN 'Missing'
        WHEN value > 1 THEN 'Large'
        ELSE 'Small'
    END AS category
FROM test;

---

6. 总结

操作	结果
NULL > 1	NULL
NULL = NULL	NULL
value IS NULL	TRUE/FALSE
WHERE value > 1	过滤掉 NULL
COALESCE(value, default)	提供默认值

核心原则：

• NULL 不等于任何值，包括它自己。
• 任何与 NULL 的比较都返回 NULL。
• 必须用 IS NULL 判断空值。

如需处理复杂空值逻辑，建议结合 COALESCE、CASE WHEN 等函数。