Semantica NULL

Articolo
12/27/2024

Si applica a: segno di spunta sì Databricks SQL Databricks Runtime

Un table è costituito da una set di righe e ogni riga contiene un set di columns. Un column è associato a un tipo di dati e rappresenta un attributo specifico di un'entità, ad esempio age è un column di un'entità denominata person). In alcuni casi, il valore di un column specifico di una riga non è noto al momento in cui la riga viene creata. In SQL, tali values sono rappresentati come NULL. Questa sezione descrive in dettaglio la semantica della gestione di NULLvalues in vari operatori, espressioni e altri costrutti di SQL.

Di seguito vengono illustrati i dati e il layout schema di un table denominato person. I dati comprendono NULLvalues nel agecolumn e questo table è utilizzato in diversi 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 è sconosciuto o NULL. Per confrontare il NULLvalues per l'uguaglianza, Azure Databricks fornisce un operatore di uguaglianza indipendente dai valori nulli (<=>), che restituisce False quando uno degli operandi è NULL e restituisce True quando entrambi gli operandi sono NULL. Il seguente table 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	Falso
Qualsiasi valore	NULL	NULL	NULL	NULL	NULL	NULL	Falso
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, ad ANDesempio , OR e NOT. Questi operatori accettano Boolean espressioni come argomenti e restituiscono un Boolean valore.

Il seguente tables illustra il comportamento degli operatori logici quando uno o entrambi gli operandi sono NULL.

Operando sinistro	Operando destro	OPPURE	E
Vero	NULL	Vero	NULL
Falso	NULL	NULL	Falso
NULL	Vero	Vero	NULL
NULL	Falso	NULL	Falso
NULL	NULL	NULL	NULL

operando	NOT
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 di 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 di valori Null

Questa classe di espressioni è progettata per gestire NULLvalues. Il risultato delle espressioni dipende dall'espressione stessa. Ad esempio, l'espressione di funzione isnull restituisce un true su input Null e false in where di input non Null come funzione coalesce restituisce il primo valore non NULL nel relativo list di operandi. Restituisce coalesceNULL tuttavia quando tutti gli operandi sono NULL. Di seguito è riportato un list incompleto di espressioni di questa categoria.

COALESCE
NULLIF
IFNULL
NVL
NVL2
ISNAN
NANVL
ISNULL
ISNOTNULL
ATLEASTNNONNULLS
IN

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 una set di righe di input. Di seguito sono riportate le regole su come NULLevalues sono gestiti dalle funzioni di aggregazione.

NULL values 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 tutte le values di input sono NULL o i dati di input set sono vuoti. Il list 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 operatore JOIN è utilizzato per combinare righe da due tables basandosi su una condizione di join. Per tutti e tre gli operatori, un'espressione condizione è un'espressione booleana e può restituire True, False o 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 NULLvalues non sono uguali. Tuttavia, ai fini del raggruppamento e dell'elaborazione distinta, i due o più values con NULL datavengono raggruppati nello stesso bucket. Questo comportamento è conforme allo standard SQL e ad altri sistemi di gestione dei 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 clausola ORDER BY inserendo tutti i NULLvalues all'inizio o alla fine, in base alla specifica dell'ordinamento dei valori null. Per impostazione predefinita, tutte le NULLvalues vengono posizionate per prime.

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 values vengono confrontati in modo a prova di null per l'uguaglianza nel contesto delle operazioni di set. Ciò significa che quando si confrontano righe, due NULLvalues vengono considerati uguali a differenza dell'operatore EqualTonormale (=).

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 INvalore o TRUEFALSE . UNKNOWN (NULL) Concettualmente un'espressione IN è semanticamente equivalente a un set di condizione di uguaglianza separata da un operatore disgiuntivo (OR). Ad esempio, c1 IN (1, 2, 3) è semanticamente equivalente a (C1 = 1 OR c1 = 2 OR c1 = 3).

La semantica della gestione delle NULLe dellevalues può essere dedotta dalla gestione dei valori NULL sia negli operatori di confronto (=) sia 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 nel list
FALSE viene restituito quando il valore non NULL non viene trovato nel list e il list non contiene null values
UNKNOWN viene restituito quando il valore è NULLo il valore non NULL non viene trovato nel list e il list contiene almeno un valore NULL

NOT IN restituisce sempre UNKNOWN quando il list contiene NULL, indipendentemente dal valore di input. Ciò è dovuto al fatto che IN restituisce UNKNOWN se il valore non si trova nel list contenente NULLe perché NOT UNKNOWN viene nuovamente UNKNOWN.

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

Condividi tramite

Semantica NULL

Operatori di confronto

Esempi

Operatori logici

Esempi

Espressioni

Espressioni intolleranti Null

Esempi

Espressioni che possono elaborare operandi di valori Null

Esempi

Espressioni di aggregazione predefinite

Esempi

Espressioni di condizione nelle `WHERE`clausole , `HAVING`e `JOIN`

Esempi

Operatori di aggregazione (`GROUP BY`, `DISTINCT`)

Esempi

Operatore Sort (`ORDER BY` clausola)

Esempi

operatori Set (`UNION`, `INTERSECT`, `EXCEPT`)

Esempi

`EXISTS` e `NOT EXISTS` sottoquery

Esempi

`IN` e `NOT IN` sottoquery

Esempi

Commenti e suggerimenti

Risorse aggiuntive

Condividi tramite

Semantica NULL

Operatori di confronto

Esempi

Operatori logici

Esempi

Espressioni

Espressioni intolleranti Null

Esempi

Espressioni che possono elaborare operandi di valori Null

Esempi

Espressioni di aggregazione predefinite

Esempi

Espressioni di condizione nelle WHEREclausole , HAVINGe JOIN

Esempi

Operatori di aggregazione (GROUP BY, DISTINCT)

Esempi

Operatore Sort (ORDER BY clausola)

Esempi

operatori Set (UNION, INTERSECT, EXCEPT)

Esempi

EXISTS e NOT EXISTS sottoquery

Esempi

IN e NOT IN sottoquery

Esempi

Commenti e suggerimenti

Risorse aggiuntive

Espressioni di condizione nelle `WHERE`clausole , `HAVING`e `JOIN`

Operatori di aggregazione (`GROUP BY`, `DISTINCT`)

Operatore Sort (`ORDER BY` clausola)

operatori Set (`UNION`, `INTERSECT`, `EXCEPT`)

`EXISTS` e `NOT EXISTS` sottoquery

`IN` e `NOT IN` sottoquery