Sémantika NULL
Platí pro: , kontrola Databricks SQL Databricks Runtime
Tabulka se skládá ze sady řádků a každý řádek obsahuje sadu sloupců.
Sloupec je přidružený k datovému typu a představuje konkrétní atribut entity (například age
sloupec entity s názvem person
). V některých případech není v okamžiku, kdy řádek vznikne, známa hodnota sloupce specifického pro daný řádek.
V SQL
souboru jsou tyto hodnoty reprezentovány jako NULL
. Tento oddíl podrobně popisuje sémantiku NULL
zpracování hodnot v různých operátorech, výrazech a dalších SQL
konstruktorech.
Následující obrázek znázorňuje rozložení schématu a data tabulky s názvem person
. Data obsahují NULL
hodnoty ve sloupci age
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 je neznámý jeden z operandů nebo oba operandy nebo NULL
. Aby bylo možné porovnat NULL
hodnoty rovnosti, Azure Databricks poskytuje operátor rovná se bezpečným hodnotám null (<=>
), který se vrátí False
, když je NULL
jeden z operandů, a vrátí True
, když oba operandy jsou NULL
. Následující tabulka ukazuje chování relačních operátorů, když je jeden nebo oba operandy NULL
:
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 | Pravda |
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 AND
jsou , OR
a NOT
.
Tyto operátory přebírají Boolean
výrazy jako argumenty a vracejí Boolean
hodnotu.
Následující tabulky ilustrují chování logických operátorů, když je jeden nebo oba operandy NULL
.
Levý operand | Pravý operand | NEBO | A |
---|---|---|---|
Pravda | NULL | Pravda | NULL |
False | NULL | NULL | False |
NULL | Pravda | Pravda | NULL |
NULL | False | NULL | False |
NULL | NULL | NULL | NULL |
Operand | NE |
---|---|
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
Relační operátory a logické operátory se v Azure Databricks považují za výrazy. Azure Databricks podporuje také další formy výrazů, které lze obecně klasifikovat jako:
- Nelerantní výrazy s hodnotou null
- Výrazy, které můžou zpracovávat
NULL
operandy hodnot- Výsledek těchto výrazů závisí na samotném výrazu.
Nelerantní výrazy s hodnotou null
Nelerantní výrazy s hodnotou null se vrátí NULL
, když 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é můžou zpracovávat operandy s hodnotou null
Tato třída výrazů je určená ke zpracování NULL
hodnot. Výsledek výrazů závisí na samotném výrazu. Například výraz isnull
funkce vrátí true
hodnotu on null input and false
on non null input, kde jako funkce coalesce
vrátí první hodnotu mimo NULL
její seznam operandů. Vrátí však hodnotu NULL
, coalesce
pokud jsou NULL
všechny jeho operandy . Níže je uveden 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 počítají jeden výsledek zpracováním sady vstupních řádků. Níže jsou uvedena pravidla způsobu zpracování NULL
hodnot agregačními funkcemi.
NULL
všechny agregační funkce při zpracování hodnoty ignorují.- Jedinou výjimkou tohoto pravidla je funkce COUNT(*).
- Některé agregační funkce se vrátí,
NULL
když jsouNULL
všechny vstupní hodnoty nebo je vstupní datová sada prázdná. Seznam těchto funkcí: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ínek v WHERE
klauzulích , HAVING
a JOIN
WHERE
operátory HAVING
filtruje řádky na základě podmínky zadané uživatelem.
Operátor JOIN
se používá 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, který může vrátit True
, False
nebo Unknown (NULL)
. Jsou "splněny", pokud je True
výsledek 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 tématu Operátory porovnání, dvě NULL
hodnoty nejsou stejné. Pro účely seskupení a jedinečného zpracování jsou však dvě nebo více hodnot seskupené NULL data
do stejného kbelíku. Toto chování odpovídá standardu SQL a dalším systémům pro správu podnikových 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
Operátor Sort (ORDER BY
klauzule)
Azure Databricks podporuje specifikaci řazení s hodnotou null v ORDER BY
klauzuli . Azure Databricks zpracuje klauzuli ORDER BY
tak, že všechny hodnoty umístí NULL
na první nebo nakonec v závislosti na specifikaci řazení s hodnotou null. Ve výchozím nastavení se všechny NULL
hodnoty umístí na první místo.
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
Set operátory (UNION
, INTERSECT
, EXCEPT
)
NULL
Hodnoty se porovnávají způsobem bezpečným pro hodnoty null z hlediska rovnosti v kontextu operací sady. To znamená, že při porovnávání řádků se dvě NULL
hodnoty považují za stejné na rozdíl od normálního EqualTo
operá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
poddotazů
V Azure Databricks EXISTS
NOT EXISTS
jsou výrazy povolené uvnitř klauzule WHERE
.
Jedná se o logické výrazy, které vrací nebo TRUE
FALSE
. Jinými slovy, je podmínka členství a vrátíTRUE
, EXISTS
když poddotaz, na který odkazuje, vrátí jeden nebo více řádků. Podobně platí, že NOT EXISTS je podmínka ne členství a vrátí TRUE
se, když se z poddotaz nevrátí žádné řádky nebo nula řádků.
Tyto dva výrazy nejsou ovlivněny přítomností hodnoty NULL ve výsledku poddotazu. Obvykle jsou rychlejší, protože se dají převést na poloviční a anti-poloviční 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
poddotazů
V Azure Databricks IN
NOT IN
jsou výrazy povolené uvnitř WHERE
klauzule dotazu. Na rozdíl od výrazu EXISTS
IN
může výraz vrátit TRUE
hodnotu , FALSE
nebo UNKNOWN (NULL)
. Výraz je koncepčně IN
sémanticky ekvivalentní sadě podmínek rovnosti oddělených disjunktivním operátorem (OR
).
Například c1 IN (1, 2, 3) je sémanticky ekvivalentní k (C1 = 1 OR c1 = 2 OR c1 = 3)
.
Pokud jde o zpracování NULL
hodnot, sémantika může být odvozena ze NULL
zpracování hodnot v relačních operátorech(=
) a logických operátorech(OR
).
Abychom to shrnuli, níže jsou uvedena pravidla pro výpočet výsledku výrazu IN
.
TRUE
se vrátí, když se v seznamu najde dotčená hodnota, která není NULL.FALSE
se vrátí, pokud se v seznamu nenajde jiná hodnota než NULL a seznam neobsahuje hodnoty NULL.UNKNOWN
se vrátí, pokud jeNULL
hodnota , nebo se v seznamu nenajde jiná hodnota než NULL a seznam obsahuje alespoň jednuNULL
hodnotu.
NOT IN
Pokud seznam obsahuje NULL
, vrátí vždy hodnotu UNKNOWN (neznámý), a to bez ohledu na vstupní hodnotu.
Důvodem je to, že IN
vrátí UNKNOWN
hodnotu, pokud hodnota není v seznamu obsahujícím NULL
hodnotu , a protože NOT UNKNOWN
je znovu UNKNOWN
hodnota .
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
---- ---