NULL セマンティクス

[アーティクル]
03/01/2024

適用対象: 「はい」のチェックマーク Databricks SQL Databricks Runtime

テーブルは一連の行で構成され、各行には一連の列が含まれています。列はデータ型に関連付けられており、エンティティの特定の属性を表します (たとえば、age は person という名前のエンティティの列です)。行に固有の列の値が、行が作成された時点では不明な場合があります。 SQL では、このような値は NULL として表されます。このセクションでは、さまざまな演算子、式、その他の SQL コンストラクトでの NULL 値処理のセマンティクスについて説明します。

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

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

比較演算子

Azure Databricks では、 >、>=、=、<、<= などの標準的な比較演算子がサポートされています。オペランドの 1 つ、または両方のオペランドが不明または NULL の場合、これらの演算子の結果は不明か NULL です。 NULL 値が等しいかどうかを比較するために、Azure Databricks には Null 安全等号演算子 (<=>) が用意されています。これは、オペランドの 1 つが NULL の場合は False を返し、両方のオペランドが NULL の場合は True を返します。次の表は、一方または両方のオペランドが NULL の場合の比較演算子の動作を示しています。

左オペランド	右オペランド	`>`	`>=`	`=`	`<`	`<=`	`<=>`
NULL	任意の値	NULL	NULL	NULL	NULL	NULL	False
任意の値	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
 -----------------

論理演算子

Azure Databricks では、AND、OR、NOT などの標準的な論理演算子がサポートされています。これらの演算子は Boolean 式を引数として取り、Boolean 値を返します。

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

左オペランド	右オペランド	または	AND
True	NULL	True	NULL
False	NULL	NULL	False
NULL	True	True	NULL
NULL	False	NULL	False
NULL	NULL	NULL	NULL

オペランド	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

式

比較演算子と論理演算子は、Azure Databricks では式として扱われます。また、Azure Databricks では、その他の形式の式もサポートされており、大きく次のように分類できます。

Null 非許容式
NULL 値オペランドを処理できる式
- これらの式の結果は、式自体によって異なります。

Null 非許容式

式の 1 つ以上の引数が 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

Null 値オペランドを処理できる式

このクラスの式は、NULL 値を処理するように設計されています。式の結果は、式自体によって異なります。たとえば、関数式 isnull は、null 入力の場合は true を返し、null 以外の入力では false を返します。これに対し関数 coalesce は、オペランドのリスト内の最初の非 NULL 値を返します。ただし、すべてのオペランドが NULL の場合、coalesce は 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

組み込み集計式

集計関数は、一連の入力行を処理して 1 つの結果を計算します。集計関数による 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、または Unknown (NULL) を返しす。条件の結果が True の場合、それらは "満たされます"。

例

-- 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 data を含む 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` 句)

Azure Databricks では、ORDER BY 句で Null の順序指定がサポートされています。 Azure Databricks は、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

設定演算子 (`UNION`、`INTERSECT`、`EXCEPT`)

NULL 値は、設定操作のコンテキストで Null 安全な方法で等価性が比較されます。つまり、行を比較するときは、通常の EqualTo (=) 演算子と異なり、2 つの NULL 値が等しいと見なされます。

例

> CREATE VIEW unknown_age AS 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` サブクエリ

Azure Databricks では、WHERE 句内で EXISTS と NOT EXISTS 式を使用できます。これらは、TRUE または FALSE のいずれかを返すブール式です。つまり EXISTS はメンバーシップ条件であり、参照するサブクエリが 1 つ以上の行を返す場合に TRUE を返します。同様に、NOT EXISTS は非メンバーシップ条件であり、サブクエリから行が返されない場合または 0 行が返される場合は TRUE を返します。

これら 2 つの式は、サブクエリの結果に NULL が存在していても影響を受けません。通常は、Null 認識のための特別な対策なしで準結合と非準結合に変換できるので、より高速です。

例

-- 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` サブクエリ

Azure Databricks では、クエリの WHERE 句内で IN と NOT IN 式を使用できます。 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 式の結果を計算する規則は次のようになります。

問題の非 NULL 値がリストにない場合は、TRUE が返されます
非 NULL 値がリストになく、リストに NULL 値が含まれていない場合は、FALSE が返されます
値が NULL の場合、またはリストに非 NULL 値がなく少なくとも 1 つの NULL 値が含まれている場合は、UNKNOWN が返されます

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

次の方法で共有

NULL セマンティクス

比較演算子

例

論理演算子

例

式

Null 非許容式

例

Null 値オペランドを処理できる式

例

組み込み集計式

例

`WHERE`、`HAVING`、および `JOIN` 句の条件式

例

集計演算子 (`GROUP BY`、`DISTINCT`)

例

並べ替え演算子 (`ORDER BY` 句)

例

設定演算子 (`UNION`、`INTERSECT`、`EXCEPT`)

例

`EXISTS` および `NOT EXISTS` サブクエリ

例

`IN` および `NOT IN` サブクエリ

例

フィードバック

その他のリソース

次の方法で共有

NULL セマンティクス

比較演算子

例

論理演算子

例

式

Null 非許容式

例

Null 値オペランドを処理できる式

例

組み込み集計式

例

WHERE、HAVING、および JOIN 句の条件式

例

集計演算子 (GROUP BY、DISTINCT)

例

並べ替え演算子 (ORDER BY 句)

例

設定演算子 (UNION、INTERSECT、EXCEPT)

例

EXISTS および NOT EXISTS サブクエリ

例

IN および NOT IN サブクエリ

例

フィードバック

その他のリソース

`WHERE`、`HAVING`、および `JOIN` 句の条件式

集計演算子 (`GROUP BY`、`DISTINCT`)

並べ替え演算子 (`ORDER BY` 句)

設定演算子 (`UNION`、`INTERSECT`、`EXCEPT`)

`EXISTS` および `NOT EXISTS` サブクエリ

`IN` および `NOT IN` サブクエリ