Sémantika NULL

Článek
07/04/2024

Platí pro: zaškrtnutí označeného ano Databricks SQL Databricks Runtime

Tabulka se skládá ze sady řádků a každého řádku obsahuje sadu sloupců. Sloupec je přidružen k datovému typu a představuje konkrétní atribut entity (například age sloupec entity volaného person). Někdy není hodnota sloupce specifického pro řádek známa v době, kdy řádek přichází do existence. V SQL, takové hodnoty jsou reprezentovány jako NULL. Tato část podrobně popisuje sémantiku NULL hodnot zpracovávaných v různých operátorech, výrazech a dalších SQL konstruktorech.

Následující příklad znázorňuje rozložení schématu a data tabulky s názvem person. Data obsahují NULL hodnoty ve age sloupci a tato tabulka se používá v různých příkladech v následujících částech.

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

Operátory porovnání

Azure Databricks podporuje standardní relační operátory, jako >jsou , >=, =< a <=. Výsledek těchto operátorů je neznámý nebo NULL pokud jeden z operandů nebo oba operandy jsou neznámé nebo NULL. Aby bylo možné porovnat NULL hodnoty rovnosti, Azure Databricks poskytuje operátor<=> rovná se hodnotě null, který se vrátí False , když je NULL jeden z operandů a vrátí True , když jsou NULLoba operandy . Následující tabulka ukazuje chování relačních operátorů, pokud jsou NULLjeden nebo oba operandy:

Levý operand	Pravý operand	`>`	`>=`	`=`	`<`	`<=`	`<=>`
NULL	Libovolná hodnota	NULL	NULL	NULL	NULL	NULL	False
Libovolná hodnota	NULL	NULL	NULL	NULL	NULL	NULL	False
NULL	NULL	NULL	NULL	NULL	NULL	NULL	True

Příklady

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

Logické operátory

Azure Databricks podporuje standardní logické operátory, jako ANDOR NOTjsou a . Tyto operátory přebírají Boolean výrazy jako argumenty a vrací Boolean hodnotu.

Následující tabulky znázorňují chování logických operátorů, pokud jsou NULLjeden nebo oba operandy .

Levý operand	Pravý operand	NEBO	A
True	NULL	True	NULL
False	NULL	NULL	False
NULL	True	True	NULL
NULL	False	NULL	False
NULL	NULL	NULL	NULL

operand	NOT
NULL	NULL

Příklady

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

Výrazy

Operátory porovnání a logické operátory se v Azure Databricks považují za výrazy. Azure Databricks podporuje také další formy výrazů, které je možné obecně klasifikovat jako:

Intolerantní výrazy s hodnotou Null
Výrazy, které mohou zpracovávat NULL operandy hodnot
- Výsledek těchto výrazů závisí na samotném výrazu.

Intolerantní výrazy s hodnotou Null

Výrazy s intolerantem s hodnotou Null se vrátí NULL , pokud je NULL jeden nebo více argumentů výrazů a většina výrazů spadá do této kategorie.

Příklady

> 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

Výrazy, které mohou zpracovat operandy s hodnotou null

Tato třída výrazů je navržena pro zpracování NULL hodnot. Výsledek výrazů závisí na samotném výrazu. Například výraz isnull funkce vrátí vstup s true hodnotou null a false nenulový vstup, kde jako funkce coalesce vrátí první hodnotu, která není NULL v seznamu operandů. Nicméně, vrátíNULL, coalesce pokud jsou všechny jeho operandy NULL. Níže je neúplný seznam výrazů této kategorie.

COALESCE
NULLIF
IFNULL
NVL
NVL2
ISNAN
NANVL
ISNULL
ISNOTNULL
ATLEASTNNONNULLS
IN

Příklady

> 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

Předdefinované agregační výrazy

Agregační funkce vypočítá jeden výsledek zpracováním sady vstupních řádků. Níže jsou uvedena pravidla způsobu NULL zpracování hodnot agregačními funkcemi.

NULL hodnoty jsou ignorovány ze zpracování všemi agregačními funkcemi.
- Jedinou výjimkou tohoto pravidla je funkce COUNT(*).
Některé agregační funkce se vrátí NULL , pokud jsou NULL všechny vstupní hodnoty nebo je vstupní sada dat prázdná. Seznam těchtofunkcích
- MAX
- MIN
- SUM
- AVG
- EVERY
- ANY
- SOME

Příklady

-- `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

Výrazy podmínky v `WHERE`klauzulích , `HAVING`a `JOIN` klauzulích

WHERE– HAVING operátory filtruje řádky na základě zadané podmínky uživatele. Operátor JOIN slouží ke kombinování řádků ze dvou tabulek na základě podmínky spojení. Pro všechny tři operátory je výraz podmínky logický výraz a může vrátit True, False nebo Unknown (NULL). Jsou "spokojeny", pokud je Truevýsledkem podmínky .

Příklady

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

Agregační operátory (`GROUP BY`, `DISTINCT`)

Jak je popsáno v relačních operátorech, dvě NULL hodnoty nejsou stejné. Pro účely seskupování a odlišného zpracování se však dvě nebo více hodnot NULL dataseskupí do stejného kontejneru. Toto chování odpovídá standardu SQL a dalším podnikovým systémům pro správu databází.

Příklady

-- `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

Sort – operátor (`ORDER BY` klauzule)

Azure Databricks podporuje specifikaci řazení null v ORDER BY klauzuli. Azure Databricks zpracuje ORDER BY klauzuli tak, že umístí všechny NULL hodnoty na první nebo poslední v závislosti na specifikaci řazení null. Ve výchozím nastavení jsou všechny NULL hodnoty umístěny jako první.

Příklady

-- `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

Operátory set (`UNION`, `INTERSECT`, `EXCEPT`)

NULL hodnoty se porovnávají bezpečným způsobem s hodnotou null pro rovnost v kontextu operací sady. To znamená, že při porovnávání řádků jsou dvě NULL hodnoty považovány za stejné na rozdíl od běžného EqualTooperátoru (=).

Příklady

> 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` a `NOT EXISTS` poddotazy

V Azure Databricks EXISTS a NOT EXISTS výrazy jsou povoleny uvnitř WHERE klauzule. Jedná se o logické výrazy, které vrací buď TRUE nebo FALSE. Jinými slovy, jedná se o podmínku členství a vrátí TRUE se, EXISTS když poddotaz odkazuje na jeden nebo více řádků. Podobně neexistuje podmínka bez členství a vrátí se TRUE , pokud se z poddotazu nevracejí žádné řádky nebo nulové řádky.

Tyto dva výrazy nejsou ovlivněny přítomností hodnoty NULL ve výsledku poddotazu. Obvykle jsou rychlejší, protože je možné je převést na středníky a anti-středníky bez zvláštních ustanovení pro povědomí o hodnotě null.

Příklady

-- 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` a `NOT IN` poddotazy

V Azure Databricks IN jsou NOT IN výrazy povolené uvnitř WHERE klauzule dotazu. Na rozdíl od výrazu EXISTS IN může výraz vrátit TRUEFALSE hodnotu nebo UNKNOWN (NULL) hodnotu. Koncepčně IN je výraz sémanticky ekvivalentní sadě podmínek rovnosti oddělených operátorem disjunktivního operátoru (OR). Například c1 IN (1, 2, 3) je sémanticky ekvivalentní (C1 = 1 OR c1 = 2 OR c1 = 3).

Pokud jde o zpracování NULL hodnot, lze sémantika odvodit z NULL zpracování hodnot v relačních operátorech (=) a logických operátorech(OR). Níže jsou uvedena pravidla pro výpočet výsledku výrazu IN .

TRUE je vrácena, pokud je v seznamu nalezena hodnota, která není null.
FALSE je vrácena, pokud není v seznamu nalezena hodnota, která není null a seznam neobsahuje hodnoty NULL.
UNKNOWN je vrácena, pokud je NULLhodnota , nebo hodnota, která není null nalezena v seznamu a seznam obsahuje alespoň jednu NULL hodnotu.

NOT IN vždy vrátí funkce UNKNOWN, pokud seznam obsahuje NULL, bez ohledu na vstupní hodnotu. Je to proto, že IN vrátí UNKNOWN , pokud hodnota není v seznamu obsahujícím NULL, a protože NOT UNKNOWN je opět UNKNOWN.

Příklady

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

Sdílet prostřednictvím

Sémantika NULL

Operátory porovnání

Příklady

Logické operátory

Příklady

Výrazy

Intolerantní výrazy s hodnotou Null

Příklady

Výrazy, které mohou zpracovat operandy s hodnotou null

Příklady

Předdefinované agregační výrazy

Příklady

Výrazy podmínky v `WHERE`klauzulích , `HAVING`a `JOIN` klauzulích

Příklady

Agregační operátory (`GROUP BY`, `DISTINCT`)

Příklady

Sort – operátor (`ORDER BY` klauzule)

Příklady

Operátory set (`UNION`, `INTERSECT`, `EXCEPT`)

Příklady

`EXISTS` a `NOT EXISTS` poddotazy

Příklady

`IN` a `NOT IN` poddotazy

Příklady

Váš názor

Další materiály

Sdílet prostřednictvím

Sémantika NULL

Operátory porovnání

Příklady

Logické operátory

Příklady

Výrazy

Intolerantní výrazy s hodnotou Null

Příklady

Výrazy, které mohou zpracovat operandy s hodnotou null

Příklady

Předdefinované agregační výrazy

Příklady

Výrazy podmínky v WHEREklauzulích , HAVINGa JOIN klauzulích

Příklady

Agregační operátory (GROUP BY, DISTINCT)

Příklady

Sort – operátor (ORDER BY klauzule)

Příklady

Operátory set (UNION, INTERSECT, EXCEPT)

Příklady

EXISTS a NOT EXISTS poddotazy

Příklady

IN a NOT IN poddotazy

Příklady

Váš názor

Další materiály

Výrazy podmínky v `WHERE`klauzulích , `HAVING`a `JOIN` klauzulích

Agregační operátory (`GROUP BY`, `DISTINCT`)

Sort – operátor (`ORDER BY` klauzule)

Operátory set (`UNION`, `INTERSECT`, `EXCEPT`)

`EXISTS` a `NOT EXISTS` poddotazy

`IN` a `NOT IN` poddotazy