SparkSQL 子查詢 IN/NOT IN 對 NULL 值的處理

官網：https://spark.apache.org/docs/4.0.0/sql-ref-functions.html

https://spark.apache.org/docs/4.0.0/sql-ref-null-semantics.html#innot-in-subquery

Unlike the EXISTS expression, IN expression can return a TRUE, FALSE or UNKNOWN (NULL) value.
與 EXISTS 不同，IN 表達式可能返回三種布爾狀態：

• TRUE：當前值在集合中；
• FALSE：當前值不在集合中；
• UNKNOWN（即 NULL）：當表達式中涉及了 NULL 值時，無法確定真假。

---

Conceptually a IN expression is semantically equivalent to a set of equality condition separated by a disjunctive operator (OR).For example, c1 IN (1, 2, 3) is semantically equivalent to (c1 = 1 OR c1 = 2 OR c1 = 3).

從語義上講，IN 表達式等價于多個等于條件用 OR 連接起來。

c1 IN (1, 2, 3)

相當于：

c1 = 1 OR c1 = 2 OR c1 = 3

---

As far as handling NULL values are concerned, the semantics can be deduced from the NULL value handling in comparison operators(=) and logical operators(OR).

對于 NULL 值的處理方式，可以基于比較運算符（如 =）和邏輯運算符（如 OR）的行為來推導。 也就是說，IN 的行為是建立在底層 SQL 對 NULL 處理規則之上的。

---

To summarize, below are the rules for computing the result of an IN expression.

• TRUE is returned when the non-NULL value in question is found in the list
• FALSE is returned when the non-NULL value is not found in the list and the list does not contain NULL values
• UNKNOWN is returned when the value is NULL, or the non-NULL value is not found in the list and the list contains at least one NULL value

IN 表達式的計算規則如下：

情況	結果
當前值不為 NULL，并且存在于列表中	TRUE
當前值不為 NULL，但不在列表中，且列表中沒有 NULL值	FALSE
當前值為 NULL，或者列表中有 NULL值但當前值不在其中	UNKNOWN

只要列表中包含 NULL，即使當前值不在列表中，也不能簡單地返回 FALSE，而是返回 UNKNOWN。

---

IN Demo：

---

1：子查詢結果只有 NULL

%sql
WITH person AS (SELECT * FROM VALUES('a', 25),('b', 30),('c', 35),('d', 40),('e', 50),('d', 50)AS person(name, age)
)
SELECT * FROM person
WHERE age IN (SELECT null);

空表?

• IN (NULL) 返回的是 UNKNOWN，不會匹配任何行。

---

2：子查詢包含 NULL 和有效值

%sql
WITH person AS (SELECT * FROM VALUES('a', 25),('b', 30),('c', 35),('d', 40),('e', 50),('f', 50)AS person(name, age)
)
SELECT * FROM person
WHERE age IN (SELECT age FROM VALUES(50), (NULL)AS sub(age)
);
-- 雖然子查詢里有 NULL，但只要匹配到具體值就會返回；

只有 age = 50 的記錄被選中。

---

3：子查詢包含 NULL 和多個值

%sql
WITH person AS (SELECT * FROM VALUES('a', 25),('b', 30),('c', 35),('d', 40),('e', 50),('f', 50)AS person(name, age)
)
SELECT * FROM person
WHERE age IN (SELECT age FROM VALUES(25), (30), (NULL)AS sub(age)
);
-- 雖然子查詢里有 NULL，但只要匹配到具體值就會返回；

---

4: 主表中存在 NULL 值，同時子查詢結果也包含 NULL

條件	是否被選中	原因
age = 25	? 是	匹配列表中的值
age = NULL	? 否	NULL IN (…) → UNKNOWN
age = 35/40/50	? 否	不匹配列表中的非 NULL 值，且列表中有 NULL → UNKNOWN

---

NOT IN Demo：

只要 NOT IN 后面的子查詢包含 NULL，整個條件就會變成 UNKNOWN， 沒有任何行被返回。

避免這個問題，需要在子查詢中加上 WHERE age IS NOT NULL。

1：子查詢結果只有 NULL

• NOT IN （只要有null）不返回任何結果

%sql
WITH person AS (SELECT * FROM VALUES('a', 25),('b', 30),('c', 35),('d', 40),('e', 50),('d', 50)AS person(name, age)
)
SELECT * FROM person
WHERE age NOT IN (SELECT null);

子查詢中包含 NULL，NOT IN 整體返回 UNKNOWN，SQL 不會將其視為 TRUE，所以沒有行滿足條件。

---

2: 子查詢包含 NULL 和有效值

• NOT IN （只要有null）不返回任何結果

%sql
WITH person AS (SELECT * FROM VALUES('a', 25),('b', 30),('c', 35),('d', 40),('e', 50),('f', 50)AS person(name, age)
)
SELECT * FROM person
WHERE age NOT IN (SELECT age FROM VALUES(50), (NULL)AS sub(age)
);

---

3: 主表中存在 NULL 值，同時子查詢結果也包含 NULL

• NOT IN （只要有null）不返回任何結果

%sql
WITH person AS (SELECT * FROM VALUES('a', 25),('b', 30),('c', null),('d', 40),('e', 50),('f', 50)AS person(name, age)
)
SELECT * FROM person
WHERE age NOT IN (SELECT age FROM VALUES(50), (NULL)AS sub(age)
);

---

Spark官方對于各種函數處理null值的說明：

https://spark.apache.org/docs/4.0.0/sql-ref-null-semantics.html