NULL 語意

發行項
01/23/2025

適用於： 核取記號為「是」 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 支援標準比較運算符，例如 >、>=、 =< 和 <=。這些運算子的結果未知，或當其中一個操作數或 NULL 兩個操作數都未知或 NULL時。為了比較 NULL 值是否相等，Azure Databricks 提供了一個 null-safe 相等運算符（<=>）。當其中一個操作數是 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和 ORNOT。這些運算符會採用 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 在輸入為 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 運算子可用來根據聯結條件結合兩個數據表中的數據列。對於這三個運算符，條件表達式是布爾運算式，而且可以傳回 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子句內NOT EXISTS允許和WHERE表達式。這些是會傳回 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查詢子句內NOT IN允許和WHERE運算式。 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 的規則。

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

無論輸入值為何，當清單包含 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` 子查詢