Null 語意

發行項
03/01/2024

適用于： 核取標示為是 Databricks SQL 檢查標示為是 Databricks Runtime

資料表包含一組資料列，而每個資料列都包含一組資料行。資料行與資料類型相關聯，而且代表實體的特定屬性 (例如， age 是稱為 person) 實體的資料行。有時候，資料列存在時，不知道資料列特定的資料行值。在中 SQL ，這類值會表示為 NULL 。本節詳細說明各種運算子、運算式和其他 SQL 建構中值處理的語 NULL 意。

以下說明名為 person 之資料表的架構配置和資料。資料包含 NULL 資料行中的 age 值，而下表用於下列各節的各種範例中。

 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 支援標準比較運算子，例如 > 、 >= 、 =< 和 <= 。這些運算子的結果未知，或是其中一個運算元或 NULL 兩個運算元都未知或 NULL 時。為了比較 NULL 相等的值，Azure Databricks 會提供 null 安全相等運算子 (<=>) ，這會傳回 False 其中一個運算元為 NULL ，並在兩個運算元都是 NULL 時傳回 True 。下表說明當一或兩個運算元為 NULL 時，比較運算子的行為：

左運算元	右運算元	`>`	`>=`	`=`	`<`	`<=`	`<=>`
空	任何值	空	空	空	空	空	假
任何值	空	空	空	空	空	空	假
空	空	空	空	空	空	空	真

例子

-- 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 時，邏輯運算子的行為。

左運算元	右運算元	或	和
真	空	真	空
假	空	空	假
空	真	真	空
空	假	空	假
空	空	空	空

操作	不
空	空

例子

-- 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 容錯運算式

當運算式的一或多個引數是 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 輸入和 false 非 Null 輸入上傳回， true 其中函 coalesce 式會傳回其運算元清單中的第一個非 NULL 值。不過， coalesce 當其所有運算元都是 NULL 時，傳回 NULL 。以下是此類別的運算式不完整清單。

合併
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

、 `HAVING` 和 `JOIN` 子句中的 `WHERE` 條件運算式

WHERE， HAVING 運算子會根據使用者指定的條件篩選資料列。 JOIN運算子可用來根據聯結條件結合兩個數據表中的資料列。針對這三個運算子，條件運算式是布林運算式，而且可以傳回 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`)

如比較運算子中所述，兩 NULL 個值不相等。不過，為了進行分組和相異處理，具有 NULL data 的兩個或多個值會分組在相同的貯體中。此行為符合 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 安全的方式進行比較，以在集合作業的內容中相等。這表示比較資料列時，兩 NULL 個值會被視為相等，不同于一般 EqualTo (=) 運算子。

例子

> 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 中， EXISTS 在子句內 WHERE 允許運算式 NOT EXISTS 。這些是傳回 TRUE 或 FALSE 的布林運算式。換句話說，是成員資格條件， EXISTS 當其參考的子查詢傳回一或多個資料列時，就會傳回 TRUE 。同樣地，NOT EXISTS 是非成員資格條件，當子查詢未傳回任何資料列或零個數據列時，就會傳回 TRUE 。

這兩個運算式不會受到子查詢結果中 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 中， IN 查詢子句內 WHERE 允許運算式 NOT IN 和運算式。 EXISTS與運算式不同， IN expression 可以傳回 TRUE 、 FALSE 或 UNKNOWN (NULL) 值。在概念上， IN 運算式在語意上相當於以分離運算子分隔的一組相等條件， (OR) 。例如，c1 IN (1， 2， 3) 語意相當於 (C1 = 1 OR c1 = 2 OR c1 = 3) 。

就處理 NULL 值而言，可以從比較運算子中的值處理推斷 NULL 語意， () = 和邏輯運算子 () OR 。總而言之，以下是計算運算式結果 IN 的規則。

TRUE 在清單中找到有問題的非 Null 值時，會傳回
FALSE 在清單中找不到非 Null 值且清單不包含 Null 值時，會傳回
UNKNOWN 當值為 NULL 時傳回，或清單中找不到非 Null 值，且清單至少包含一個 NULL 值

NOT IN 不論輸入值為何，當清單包含 NULL 時，一律會傳回 UNKNOWN。這是因為 IN 如果值不在包含 NULL 的清單中，則傳回 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 值運算元的運算式

例子

內建匯總運算式

例子

、 HAVING 和 JOIN 子句中的 WHERE 條件運算式

例子

匯總運算子 (GROUP BY 、 DISTINCT)

例子

排序運算子 (ORDER BY 子句)

例子

設定運算子 (UNION 、 INTERSECT) EXCEPT

例子

EXISTS 和 NOT EXISTS 子查詢

例子

IN 和 NOT IN 子查詢

例子

其他資源

表達式