Semantica NULL
Si applica a: controllo SQL databricks Databricks Runtime
Una tabella è costituita da un set di righe e ogni riga contiene un set di colonne.
Una colonna è associata a un tipo di dati e rappresenta un attributo specifico di un'entità, ad esempio age
una colonna di un'entità denominata person
. A volte, il valore di una colonna specifica per una riga non è noto al momento dell'esistenza della riga.
In SQL
, tali valori vengono rappresentati come NULL
. In questa sezione viene descritta la semantica dei NULL
valori che gestiscono in vari operatori, espressioni e altri SQL
costrutti.
Di seguito viene illustrato il layout dello schema e i dati di una tabella denominata person
. I dati contengono NULL
valori nella age
colonna e questa tabella viene usata in vari esempi nelle sezioni seguenti.
Id Name Age
--- -------- ----
100 Joe 30
200 Marry NULL
300 Mike 18
400 Fred 50
500 Albert NULL
600 Michelle 30
700 Dan 50
Operatori di confronto
Azure Databricks supporta gli operatori di confronto standard, >
ad esempio , >=
, =
<
e <=
.
Il risultato di questi operatori è sconosciuto o NULL
quando uno degli operandi o entrambi gli operandi sono sconosciuti o NULL
. Per confrontare i NULL
valori per l'uguaglianza, Azure Databricks fornisce un operatore uguale null-safe (<=>
), che restituisce quando uno degli operandi è NULL
e restituisce False
True
quando entrambi gli operandi sono NULL
. La tabella seguente illustra il comportamento degli operatori di confronto quando uno o entrambi gli operandi sono NULL
:
Operando sinistro | Operando destro | > |
>= |
= |
< |
<= |
<=> |
---|---|---|---|---|---|---|---|
NULL | Qualsiasi valore | NULL | NULL | NULL | NULL | NULL | False |
Qualsiasi valore | NULL | NULL | NULL | NULL | NULL | NULL | False |
NULL | NULL | NULL | NULL | NULL | NULL | NULL | Vero |
Esempi
-- 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
-----------------
Operatori logici
Azure Databricks supporta operatori logici standard, AND
ad esempio , OR
e NOT
.
Questi operatori accettano Boolean
espressioni come argomenti e restituiscono un Boolean
valore.
Le tabelle seguenti illustrano il comportamento degli operatori logici quando uno o entrambi gli operandi sono NULL
.
Operando sinistro | Operando destro | O | E |
---|---|---|---|
Vero | NULL | Vero | NULL |
False | NULL | NULL | False |
NULL | Vero | Vero | NULL |
NULL | False | NULL | False |
NULL | NULL | NULL | NULL |
Operando | NON |
---|---|
NULL | NULL |
Esempi
-- 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
Espressioni
Gli operatori di confronto e gli operatori logici vengono considerati espressioni in Azure Databricks. Azure Databricks supporta anche altre forme di espressioni, che possono essere classificate in modo generale come:
- Espressioni intolleranti Null
- Espressioni che possono elaborare
NULL
operandi di valore- Il risultato di queste espressioni dipende dall'espressione stessa.
Espressioni intolleranti Null
Le espressioni intolleranti Null restituiscono NULL
quando uno o più argomenti dell'espressione sono NULL
e la maggior parte delle espressioni rientrano in questa categoria.
Esempi
> 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
Espressioni che possono elaborare operandi valori Null
Questa classe di espressioni è progettata per gestire NULL
i valori. Il risultato delle espressioni dipende dall'espressione stessa. Come esempio, l'espressione isnull
di funzione restituisce un oggetto su true
input Null e false
su input non Null in cui come funzione coalesce
restituisce il primo NULL
valore non nell'elenco di operandi. Restituisce NULL
tuttavia coalesce
quando tutti gli operandi sono NULL
. Di seguito è riportato un elenco incompleto di espressioni di questa categoria.
- COALESCE
- NULLIF
- IFNULL
- NVL
- NVL2
- ISNAN
- NANVL
- ISNULL
- ISNOTNULL
- ATLEASTNNONNULLS
- POLLICI
Esempi
> 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
Espressioni di aggregazione predefinite
Le funzioni di aggregazione calcolano un singolo risultato elaborando un set di righe di input. Di seguito sono riportate le regole di gestione NULL
dei valori da parte delle funzioni di aggregazione.
NULL
i valori vengono ignorati dall'elaborazione da tutte le funzioni di aggregazione.- Solo l'eccezione a questa regola è la funzione COUNT(*).
- Alcune funzioni di aggregazione restituiscono
NULL
quando tutti i valori di input sonoNULL
o il set di dati di input è vuoto. L'elenco di queste funzioni è:MAX
MIN
SUM
AVG
EVERY
ANY
SOME
Esempi
-- `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
Espressioni di condizione nelle WHERE
clausole , HAVING
e JOIN
WHERE
, HAVING
gli operatori filtrano le righe in base alla condizione specificata dall'utente.
Un JOIN
operatore viene usato per combinare le righe di due tabelle in base a una condizione di join.
Per tutti e tre gli operatori, un'espressione condizione è un'espressione booleana e può restituire True
o False
Unknown (NULL)
. Sono "soddisfatti" se il risultato della condizione è True
.
Esempi
-- 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
Operatori di aggregazione (GROUP BY
, DISTINCT
)
Come illustrato in Operatori di confronto, due NULL
valori non sono uguali. Tuttavia, ai fini del raggruppamento e dell'elaborazione distinta, i due o più valori con NULL data
vengono raggruppati nello stesso bucket. Questo comportamento è conforme allo standard SQL e ad altri sistemi di gestione di database aziendali.
Esempi
-- `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
Operatore Sort (ORDER BY
clausola)
Azure Databricks supporta la specifica di ordinamento Null nella ORDER BY
clausola . Azure Databricks elabora la ORDER BY
clausola inserendo tutti i valori all'inizio NULL
o infine in base alla specifica di ordinamento Null. Per impostazione predefinita, tutti i NULL
valori vengono inseriti inizialmente.
Esempi
-- `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
Operatori set (UNION
, INTERSECT
, EXCEPT
)
NULL
i valori vengono confrontati in modo null-safe per l'uguaglianza nel contesto delle operazioni set. Ciò significa che quando si confrontano le righe, due NULL
valori vengono considerati uguali a differenza dell'operatore regolare EqualTo
(=
).
Esempi
> 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
e NOT EXISTS
sottoquery
In Azure Databricks EXISTS
e NOT EXISTS
le espressioni sono consentite all'interno di una WHERE
clausola .
Si tratta di espressioni booleane che restituiscono TRUE
o FALSE
. In altre parole, EXISTS
è una condizione di appartenenza e restituisce TRUE
quando la sottoquery fa riferimento a restituisce una o più righe. Analogamente, NOT EXISTS è una condizione di non appartenenza e restituisce TRUE
quando non vengono restituite righe o zero righe dalla sottoquery.
Queste due espressioni non sono interessate dalla presenza di NULL nel risultato della sottoquery. Normalmente sono più veloci perché possono essere convertiti in semijoins e anti-semijoins senza disposizioni speciali per la consapevolezza null.
Esempi
-- 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
e NOT IN
sottoquery
In Azure Databricks IN
e NOT IN
le espressioni sono consentite all'interno di una WHERE
clausola di una query. A differenza dell'espressione, l'espressione EXISTS
può restituire un TRUE
valore o FALSE
UNKNOWN (NULL)
. IN
Concettualmente un'espressione IN
è semanticamente equivalente a un set di condizioni di uguaglianza separate da un operatore disgiuntivo (OR
).
Ad esempio, c1 IN (1, 2, 3) è semanticamente equivalente a (C1 = 1 OR c1 = 2 OR c1 = 3)
.
Per quanto riguarda la gestione dei NULL
valori, la semantica può essere dedotta dalla gestione dei NULL
valori negli operatori di confronto(=
) e negli operatori logici(OR
).
Di seguito sono riportate le regole per calcolare il risultato di un'espressione IN
.
TRUE
viene restituito quando il valore non NULL in questione viene trovato nell'elencoFALSE
viene restituito quando il valore non NULL non viene trovato nell'elenco e l'elenco non contiene valori NULLUNKNOWN
viene restituito quando il valore èNULL
o il valore non NULL non viene trovato nell'elenco e l'elenco contiene almeno unNULL
valore
NOT IN
restituisce sempre UNKNOWN quando l'elenco contiene NULL
, indipendentemente dal valore di input.
Ciò è dovuto al fatto che restituisce UNKNOWN
se il valore non si trova nell'elenco contenente NULL
e perché NOT UNKNOWN
è di nuovo UNKNOWN
.IN
Esempi
-- 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
---- ---