NULL-semantik

Artikel
07/05/2024

Gäller för: markerad ja Databricks SQL Databricks Runtime

En tabell består av en uppsättning rader och varje rad innehåller en uppsättning kolumner. En kolumn är associerad med en datatyp och representerar ett specifikt attribut för en entitet (till exempel age en kolumn i en entitet med namnet person). Ibland är värdet för en kolumn som är specifik för en rad inte känt när raden kommer till. I SQLrepresenteras sådana värden som NULL. Det här avsnittet beskriver semantiken för hantering av NULL värden i olika operatorer, uttryck och andra SQL konstruktioner.

Följande illustrerar schemalayouten och data i en tabell med namnet person. Data innehåller NULL värden i age kolumnen och den här tabellen används i olika exempel i avsnitten nedan.

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

Jämförelseoperatorer

Azure Databricks stöder standardjämförelseoperatorer som >, >=, =< och <=. Resultatet av dessa operatorer är okänt eller NULL när någon av operanderna eller båda operanderna är okända eller NULL. För att jämföra NULL värdena för likhet tillhandahåller Azure Databricks en null-säker likhetsoperator (<=>), som returnerar False när en av operanderna är NULL och returnerar True när båda operanderna är NULL. I följande tabell visas hur jämförelseoperatorer fungerar när en eller båda operanderna är NULL:

Vänster operand	Höger operand	`>`	`>=`	`=`	`<`	`<=`	`<=>`
NULL	Valfritt värde	NULL	NULL	NULL	NULL	NULL	Falsk
Valfritt värde	NULL	NULL	NULL	NULL	NULL	NULL	Falsk
NULL	NULL	NULL	NULL	NULL	NULL	NULL	Sant

Exempel

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

Logiska operatorer

Azure Databricks stöder logiska standardoperatorer som AND, OR och NOT. Dessa operatorer tar Boolean uttryck som argument och returnerar ett Boolean värde.

Följande tabeller illustrerar beteendet för logiska operatorer när en eller båda operanderna är NULL.

Vänster operand	Höger operand	ELLER	OCH
Sant	NULL	Sant	NULL
Falsk	NULL	NULL	Falsk
NULL	Sant	Sant	NULL
NULL	Falsk	NULL	Falsk
NULL	NULL	NULL	NULL

operand	NOT
NULL	NULL

Exempel

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

Uttryck

Jämförelseoperatorerna och logiska operatorerna behandlas som uttryck i Azure Databricks. Azure Databricks har också stöd för andra uttrycksformer, som i stort sett kan klassificeras som:

Null intoleranta uttryck
Uttryck som kan bearbeta NULL värdeoperor
- Resultatet av dessa uttryck beror på själva uttrycket.

Null intoleranta uttryck

Null intoleranta uttryck returneras NULL när ett eller flera uttrycksargument är NULL och de flesta uttrycken hamnar i den här kategorin.

Exempel

> 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

Uttryck som kan bearbeta operander för null-värden

Den här klassen av uttryck är utformad för att hantera NULL värden. Resultatet av uttrycken beror på själva uttrycket. Funktionsuttrycket isnull returnerar till exempel en true på null-indata och false på indata som inte är null, där som funktion coalesce returnerar det första icke-värdet NULL i listan över operander. Returnerar NULL dock coalesce när alla dess operander är NULL. Nedan visas en ofullständig lista över uttryck i den här kategorin.

COALESCE
NULLIF
IFNULL
NVL
NVL2
ISNAN
NANVL
ISNULL
ISNOTNULL
ATLEASTNNONNULLS
IN

Exempel

> 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

Inbyggda aggregeringsuttryck

Aggregerade funktioner beräknar ett enda resultat genom att bearbeta en uppsättning indatarader. Nedan visas reglerna för hur NULL värden hanteras av aggregerade funktioner.

NULL värden ignoreras från bearbetning av alla aggregerade funktioner.
- Endast undantag till den här regeln är funktionen COUNT(*).
Vissa aggregeringsfunktioner returneras NULL när alla indatavärden är NULL eller indatauppsättningen är tom. Listan över dessa funktioner är:
- MAX
- MIN
- SUM
- AVG
- EVERY
- ANY
- SOME

Exempel

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

Villkorsuttryck i `WHERE`, `HAVING`och `JOIN` -satser

WHEREfiltrerar HAVING operatorerna rader baserat på användarens angivna villkor. En JOIN operator används för att kombinera rader från två tabeller baserat på ett kopplingsvillkor. För alla tre operatorerna är ett villkorsuttryck ett booleskt uttryck och kan returnera True, False eller Unknown (NULL). De är "uppfyllda" om resultatet av villkoret är True.

Exempel

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

Aggregerade operatorer (`GROUP BY`, `DISTINCT`)

Som beskrivs i Jämförelseoperatorer är två NULL värden inte lika med. För gruppering och distinkt bearbetning grupperas dock de två eller flera värdena med NULL datai samma bucket. Det här beteendet överensstämmer med SQL-standarden och med andra hanteringssystem för företagsdatabaser.

Exempel

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

Sorteringsoperator (`ORDER BY` sats)

Azure Databricks har stöd för null-beställningsspecifikation i ORDER BY -sats. Azure Databricks bearbetar ORDER BY satsen genom att placera alla NULL värden först eller till sist beroende på specifikationen för null-beställning. Som standard placeras alla NULL värden först.

Exempel

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

Ange operatorer (`UNION`, `INTERSECT`, `EXCEPT`)

NULL värden jämförs på ett null-säkert sätt för likhet i kontexten för angivna åtgärder. Det innebär att när du jämför rader anses två NULL värden vara lika med den vanliga EqualTooperatorn (=).

Exempel

> 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` och `NOT EXISTS` underfrågor

I Azure Databricks tillåts EXISTS NOT EXISTS uttryck i en WHERE sats. Det här är booleska uttryck som returnerar antingen TRUE eller FALSE. Med andra ord EXISTS är ett medlemskapsvillkor och returnerar TRUE när underfrågan som den refererar till returnerar en eller flera rader. På samma sätt är NOT EXISTS ett villkor som inte är medlemskap och returnerar TRUE när inga rader eller noll rader returneras från underfrågan.

Dessa två uttryck påverkas inte av förekomsten av NULL i resultatet av underfrågan. De är normalt snabbare eftersom de kan konverteras till semijoins och anti-semijoins utan särskilda bestämmelser för null-medvetenhet.

Exempel

-- 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` och `NOT IN` underfrågor

I Azure Databricks tillåts IN NOT IN uttryck i en WHERE sats i en fråga. EXISTS Till skillnad från uttrycket IN kan uttrycket returnera ett TRUE, FALSE eller UNKNOWN (NULL) -värde. Konceptuellt är ett IN uttryck semantiskt likvärdigt med en uppsättning likhetsvillkor avgränsade med en disjunctive operator (OR). C1 IN (1, 2, 3) motsvarar till (C1 = 1 OR c1 = 2 OR c1 = 3)exempel semantiskt .

När det gäller hanteringsvärden NULL kan semantiken härledas från NULL värdehanteringen i jämförelseoperatorer(=) och logiska operatorer(OR). Nedan sammanfattas reglerna för beräkning av resultatet av ett IN uttryck.

TRUE returneras när det aktuella icke-NULL-värdet hittas i listan
FALSE returneras när icke-NULL-värdet inte hittas i listan och listan inte innehåller NULL-värden
UNKNOWN returneras när värdet är NULL, eller om värdet som inte är NULL inte hittas i listan och listan innehåller minst ett NULL värde

NOT IN returnerar alltid UNKNOWN när listan innehåller NULL, oavsett indatavärde. Detta beror på att IN returnerar UNKNOWN om värdet inte finns i listan som innehåller NULL, och eftersom NOT UNKNOWN är igen UNKNOWN.

Exempel

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

Dela via

NULL-semantik

Jämförelseoperatorer

Exempel

Logiska operatorer

Exempel

Uttryck

Null intoleranta uttryck

Exempel

Uttryck som kan bearbeta operander för null-värden

Exempel

Inbyggda aggregeringsuttryck

Exempel

Villkorsuttryck i `WHERE`, `HAVING`och `JOIN` -satser

Exempel

Aggregerade operatorer (`GROUP BY`, `DISTINCT`)

Exempel

Sorteringsoperator (`ORDER BY` sats)

Exempel

Ange operatorer (`UNION`, `INTERSECT`, `EXCEPT`)

Exempel

`EXISTS` och `NOT EXISTS` underfrågor

Exempel

`IN` och `NOT IN` underfrågor

Exempel

Feedback

Ytterligare resurser

Dela via

NULL-semantik

Jämförelseoperatorer

Exempel

Logiska operatorer

Exempel

Uttryck

Null intoleranta uttryck

Exempel

Uttryck som kan bearbeta operander för null-värden

Exempel

Inbyggda aggregeringsuttryck

Exempel

Villkorsuttryck i WHERE, HAVINGoch JOIN -satser

Exempel

Aggregerade operatorer (GROUP BY, DISTINCT)

Exempel

Sorteringsoperator (ORDER BY sats)

Exempel

Ange operatorer (UNION, INTERSECT, EXCEPT)

Exempel

EXISTS och NOT EXISTS underfrågor

Exempel

IN och NOT IN underfrågor

Exempel

Feedback

Ytterligare resurser

Villkorsuttryck i `WHERE`, `HAVING`och `JOIN` -satser

Aggregerade operatorer (`GROUP BY`, `DISTINCT`)

Sorteringsoperator (`ORDER BY` sats)

Ange operatorer (`UNION`, `INTERSECT`, `EXCEPT`)

`EXISTS` och `NOT EXISTS` underfrågor

`IN` och `NOT IN` underfrågor