NULL 語意

發行項
07/05/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時，比較運算符的行為：

左運算元	右運算元	`>`	`>=`	`=`	`<`	`<=`	`<=>`
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時，邏輯運算符的行為。

左運算元	右運算元	OR	且
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 不容許表達式

當表達式的一或多個自變數是 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 會 true 傳回 null 輸入和 false 非 Null 輸入上的，其中當函 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

、 `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 ORDER BY 會根據 Null 排序規格，先或最後放置所有 NULL 值來處理子句。根據預設，所有 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`，， `EXCEPTINTERSECT`）

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值而言，可以從比較運算元（）和邏輯運算符（=OR）中的值處理推斷NULL語意。總結來說，以下是計算表達式結果 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` 子句）

範例

Set 運算子（`UNION`，， `EXCEPTINTERSECT`）

範例

`EXISTS` 和 `NOT EXISTS` 子查詢

範例

`IN` 和 `NOT IN` 子查詢

範例

意見反應

其他資源

共用方式為

NULL 語意

比較運算子

範例

邏輯運算子

範例

運算式

Null 不容許表達式

範例

可以處理 Null 值操作數的表達式

範例

內建匯總表達式

範例

、 HAVING和 JOIN 子句中的WHERE條件表達式

範例

匯總運算子 （GROUP BY， DISTINCT）

範例

排序運算子 （ORDER BY 子句）

範例

Set 運算子 （UNION， ， EXCEPTINTERSECT）

範例

EXISTS 和 NOT EXISTS 子查詢

範例

IN 和 NOT IN 子查詢

範例

意見反應

其他資源

、 `HAVING`和 `JOIN` 子句中的`WHERE`條件表達式

匯總運算子（`GROUP BY`， `DISTINCT`）

排序運算子（`ORDER BY` 子句）

Set 運算子（`UNION`，， `EXCEPTINTERSECT`）

`EXISTS` 和 `NOT EXISTS` 子查詢

`IN` 和 `NOT IN` 子查詢