NULL Semantics

説明

テーブルは一連の行で構成され、各行は一連の列が含まれます。列はデータ型に関連付けられ、エンティティの特定の属性を表します(例えば、age は personと呼ばれるエンティティの列です)。行が存在する時に、行に固有の列が未知の場合があります。SQL では、そのような値は NULL と表されます。このセクションでは、様々な演算子、表現、そのほかの SQL 構造で処理される NULL 値のセマンティクスについて詳しく説明します。

比較演算子での Null 処理
論理演算子での Null 処理
式での Null 処理
WHERE、HAVING、JOIN 条件での null 処理
GROUP BY および DISTINCT での null 処理
ORDER BY での null 処理
UNION、INTERSECT、EXCEPT での null 処理
EXISTS、NOT EXISTS サブクエリでの null 処理
IN、NOT IN サブクエリでの null 処理

以下は、person という名前のテーブルのスキーマレイアウトとデータを示しています。age 列には NULL 値が含まれ、このテーブルは以下のセクションの様々な例で使われます。TABLE: person

Id	名前	Age
100	Joe	30
200	Marry	NULL
300	Mike	18
400	Fred	50
500	Albert	NULL
600	Michelle	30
700	Dan	50

比較演算子

Apache spark は、‘>’、‘>=’、‘=’、‘<’、‘<=’ のような標準的な比較演算子をサポートします。これらの演算子の結果は、オペランドのうちの1つあるいは両方のオペランドが未知あるいは NULL の場合、未知あるいは NULL です。NULLの値が等しいかどうかを比較するために、Sparkはnullセーフのイコール演算子 (‘<=>’) を提供します。これは、オペランドの1つがNULLの場合にFalseを返し、両方のオペランドが NULLの場合にTrueを返します。以下の表は、一方または両方のオペランドが`NULL`の時の比較オペレータの動作を示しています:

Left Operand	Right Operand	>	>=	=	<	<=	<=>
NULL	Any value	NULL	NULL	NULL	NULL	NULL	False
Any value	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 は、AND、OR、NOT のような標準的な論理演算子をサポートします。これらの演算子は引数として Boolean 式を取り、Boolean 値を返します。

以下の表は、一方または両方のオペランドが NULL の時に、論理演算子の動作を示しています。

Left Operand	Right Operand	OR	AND
True	NULL	True	NULL
False	NULL	NULL	False
NULL	True	True	NULL
NULL	False	NULL	False
NULL	NULL	NULL	NULL

operand	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 では式として扱われます。これらの2種類の式以外に、Spark は関数式、キャスト式などの他の形式の式をサポートします。Spark での式は大きく以下のように分類できます:

Null 不寛容式
NULL 値のオペランドを処理できる式
- これらの式の結果は、式自身に依存します。

Null 不寛容式

Null 不寛容式は、式の1つ以上の引数が 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 以外の値を返します。ただし、coalesce は全てのオペランドが NULL の場合、NULL を返します。以下はこのカテゴリの式の不完全なリストです。

COALESCE
NULLIF
IFNULL
NVL
NVL2
ISNAN
NANVL
ISNULL
ISNOTNULL
ATLEASTNNONNULLS
IN

例

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 値が集約関数によって処理される方法のルールです。

NULL 値は全ての集約関数による処理から無視されます。
- このルールの唯一の例外は、COUNT(*) 関数です。
一部の集約関数は、全ての入力値が NULL であるか、入力データセットが空の場合に、NULL を返します。
これらの関数のリストは以下の通りです:
- MAX
- MIN
- SUM
- AVG
- EVERY
- ANY
- SOME

例

-- `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 演算子は、結合条件に基づいて2つのテーブルの行を結合するために使われます。3つの演算子すべてについて、条件式はブール式であり、True、False、未知 (NULL) を返すことができます。条件の結果が True の場合、これらは “satisfied” です。

例

-- 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 * 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)

前のセクション比較演算子で説明したように、2つの NULL 値は等しくありません。ただし、グループ化と個別の処理のために、NULL データの2つ以上の値は同じバケットにグループ化されます。この動作は、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|
+----+--------+

Set オペレータ (UNION、INTERSECT、EXCEPT)

NULL 値は、set 操作のコンテキストで等しいかどうかについて null セーフな方法で比較されます。つまり、行を比較する時、通常の EqualTo(=) 演算子と異なり、2つの NULL 値は等しいと見なされます。

例

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 では、EXISTS と NOT EXISTS 式は WHERE 句の中で使用できます。これらは、TRUE または FALSE のどちらかを返すブール式です。つまり、EXISTS はメンバーシップ条件であり、それが参照するサブクエリが1つ以上の行を返す場合、TRUE を返します。同様に、NOT EXISTS は非メンバーシップ条件であり、サブクエリから行が返されなかったり、0行の場合に、TRUE を返します。

これら2つの式は、サブクエリの結果に NULL が存在しても影響を受けません。null の認識のための特別な準備無しに、semijoin / anti-semijoin に変換できるため、通常は高速です。

例

-- 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 では、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) と意味的に同等です。

NULL 値の処理に関する限り、セマンティクスは、比較演算子 (=) および論理演算子 (OR) での NULL 値の処理から推測できます。要約のために、IN 式の結果を計算するためのルールを以下に示します。

リストで no-NULL 値が見つかった場合、TRUE が返されます。
リストに no-NULL 値が見つからず、リストが NULL 値を含まない場合は、FALSE が返されます。
値が NULL の場合、またはリストに non-NULL が見つからず、リストに少なくとも1つの NULL 値が含まれている場合、UNKNOWN が返されます。

NOT IN は、リストに NULL が含まれる場合、入力値に関係なく、常に 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|
+----+---+
+----+---+

Spark SQL ガイド