NULL-Semantik

Artikel
10/10/2023

Gilt für: durch Häkchen mit „Ja“ markiert Databricks SQL Databricks Runtime

Eine Tabelle besteht aus einer Menge von Zeilen, die jeweils eine Menge von Spalten enthalten. Eine Spalte ist mit einem Datentyp verknüpft und stellt ein bestimmtes Attribut einer Entität dar (z. B. ist age eine Spalte einer Entität namens person). Mitunter ist der Wert einer Spalte, die für eine Zeile spezifisch ist, zum Zeitpunkt des Entstehens der Zeile nicht bekannt. In SQL werden solche Werte als NULL dargestellt. In diesem Abschnitt wird die Semantik von NULL-Werten in verschiedenen Operatoren, Ausdrücken und anderen SQL-Konstrukten erläutert.

Die folgende Abbildung veranschaulicht das Layout des Schemas und die Daten einer Tabelle mit dem Namen person. Die Daten enthalten NULL-Werte in der Spalte age. Diese Tabelle wird in verschiedenen Beispielen in den folgenden Abschnitten verwendet.

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

Vergleichsoperatoren

Azure Databricks unterstützt Standardvergleichsoperatoren wie >, >=, =, < und <=. Das Ergebnis dieser Operatoren ist unbekannt oder NULL, wenn einer oder beide Operanden unbekannt oder NULL sind. Um die NULL-Werte auf Gleichheit zu vergleichen, bietet Azure Databricks den NULL-sicheren Gleichheitsoperator (<=>), der False zurückgibt, wenn einer der Operanden NULL ist, und True, wenn beide Operanden NULL sind. Die folgende Tabelle veranschaulicht das Verhalten von Vergleichsoperatoren, wenn ein oder beide Operanden NULL sind:

Linker Operand	Rechter Operand	`>`	`>=`	`=`	`<`	`<=`	`<=>`
NULL	Beliebiger Wert	NULL	NULL	NULL	NULL	NULL	False
Beliebiger Wert	NULL	NULL	NULL	NULL	NULL	NULL	False
NULL	NULL	NULL	NULL	NULL	NULL	NULL	True

Beispiele

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

Logische Operatoren

Azure Databricks unterstützt logische Standardoperatoren wie AND, OR und NOT. Diese Operatoren verwenden Boolean-Ausdrücke als Argumente und geben einen Boolean-Wert zurück.

Die folgenden Tabellen veranschaulichen das Verhalten logischer Operatoren, wenn ein oder beide Operanden NULL sind.

Linker Operand	Rechter Operand	OR	AND
True	NULL	True	NULL
False	NULL	NULL	False
NULL	True	True	NULL
NULL	False	NULL	False
NULL	NULL	NULL	NULL

Operand	NOT
NULL	NULL

Beispiele

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

Ausdrücke

Die Vergleichs- und logischen Operatoren werden in Azure Databricks als Ausdrücke behandelt. Azure Databricks unterstützt auch andere Ausdrucksformen, die grob klassifiziert werden können als:

NULL-intolerante Ausdrücke
Ausdrücke, die Wertoperanden des Typs NULL verarbeiten können
- Das Ergebnis dieser Ausdrücke hängt vom Ausdruck selbst ab.

NULL-intolerante Ausdrücke

NULL-intolerante Ausdrücke geben NULL zurück, wenn ein oder mehrere Argumente des Ausdrucks NULL sind, wobei die meisten Ausdrücke zu dieser Kategorie gehören.

Beispiele

> 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

Ausdrücke, die Operanden mit dem Wert NULL verarbeiten können

Diese Ausdrucksklasse ist für die Verarbeitung von NULL-Werten gedacht. Das Ergebnis der Ausdrücke hängt vom Ausdruck selbst ab. Ein Beispiel: Der Funktionsausdruck isnull gibt bei einer NULL-Eingabe true und bei einer Nicht-NULL-Eingabe false zurück, während die Funktion coalesce den ersten Werte ungleich NULL in ihrer Operandenliste zurückgibt. coalesce gibt jedoch NULL zurück, wenn alle seine Operanden NULL sind. Es folgt eine unvollständige Liste mit Ausdrücken dieser Kategorie.

COALESCE
NULLIF
IFNULL
NVL
NVL2
ISNAN
NANVL
ISNULL
ISNOTNULL
ATLEASTNNONNULLS
IN

Beispiele

> 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

Integrierte Aggregatausdrücke

Aggregatfunktionen berechnen ein einzelnes Ergebnis, indem eine Menge von Eingabezeilen verarbeitet wird. Es folgen die Regeln für die Verarbeitung von NULL-Werten durch Aggregatfunktionen.

NULL-Werte werden von allen Aggregatfunktionen bei der Verarbeitung ignoriert.
- Die einzige Ausnahme dieser Regel ist die Funktion COUNT(*).
Einige Aggregatfunktionen geben NULL zurück, wenn alle Eingabewerte NULL sind oder das Dataset leer ist. Es folgt eine Liste dieser Funktionen:
- MAX
- MIN
- SUM
- AVG
- EVERY
- ANY
- SOME

Beispiele

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

Bedingungsausdrücke in den Klauseln `WHERE`, `HAVING`, und `JOIN`

Die Operatoren WHERE und HAVING filtern Zeilen auf Grundlage der vom Benutzer angegebenen Bedingung. Ein JOIN-Operator dient zum Kombinieren von Zeilen aus zwei Tabellen auf Grundlage einer Join-Bedingung. Für alle drei Operatoren ist ein Bedingungsausdruck ein boolescher Ausdruck, der True, False oder Unknown (NULL) zurückgeben kann. Sie sind „erfüllt“, wenn das Ergebnis der Bedingung True ist.

Beispiele

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

Aggregatoperatoren (`GROUP BY`, `DISTINCT`)

Wie unter Vergleichsoperatoren beschrieben, sind zwei NULL-Werte nicht gleich. Zum Zwecke der Gruppierung und getrennten Verarbeitung werden die zwei oder mehr Werte mit NULL data jedoch in einem Bucket zusammengefasst. Dieses Verhalten entspricht dem SQL-Standard und anderen Datenbank-Managementsystemen für Unternehmen.

Beispiele

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

Sortieroperator (`ORDER BY`-Klausel)

Azure Databricks unterstützt die Angabe der NULL-Reihenfolge in der ORDER BY-Klausel. Azure Databricks verarbeitet die ORDER BY-Klausel, indem alle NULL-Werte am Anfang oder Ende platziert werden, und zwar abhängig von der Angabe der NULL-Reihenfolge. Standardmäßig werden alle NULL-Werte am Anfang platziert.

Beispiele

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

Mengenoperatoren (`UNION`, `INTERSECT`, `EXCEPT`)

NULL Werte werden im Rahmen von Mengenoperationen auf NULL-sichere Weise auf Gleichheit verglichen. Das bedeutet, dass beim Vergleichen von Zeilen zwei NULL-Werte als gleich angesehen werden, im Gegensatz zum regulären EqualTo(=)-Operator.

Beispiele

> 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

Unterabfragen `EXISTS` und `NOT EXISTS`

In Azure Databricks sind EXISTS- und NOT EXISTS-Ausdrücke innerhalb einer WHERE-Klausel zulässig. Dabei handelt sich um boolesche Ausdrücke, die entweder TRUE oder FALSE zurückgeben. Mit anderen Worten: EXISTS ist eine Bedingung für Zugehörigkeit und gibt TRUE zurück, wenn die Unterabfrage, auf die sie verweist, eine oder mehrere Zeilen zurückgibt. Ebenso ist NOT EXISTS eine Bedingung für Nichtzugehörigkeit und gibt TRUE zurück, wenn keine bzw. 0 Zeilen von der Unterabfrage zurückgegeben werden.

Diese beiden Ausdrücke werden durch das Vorhandensein von NULL im Ergebnis der Unterabfrage nicht beeinflusst. Sie sind in der Regel schneller, weil sie ohne besondere Vorkehrungen bezüglich Berücksichtigung von NULL in Semijoins und Anti-Semijoins konvertiert werden können.

Beispiele

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

Unterabfragen `IN` und `NOT IN`

In Azure Databricks sind IN- und NOT IN-Ausdrücke innerhalb einer WHERE-Klausel zulässig. Anders als der Ausdruck EXISTS kann der Ausdruck IN den Wert TRUE, FALSE oder UNKNOWN (NULL) zurückgeben. Konzeptionell entspricht der Ausdruck IN semantisch einer Menge von Gleichheitsbedingungen, die durch einen disjunktiven Operator (OR) getrennt sind. Beispielsweise entspricht c1 IN (1, 2, 3) semantisch (C1 = 1 OR c1 = 2 OR c1 = 3).

Was den Umgang mit NULL-Werten betrifft, so kann die Semantik aus dem Umgang mit NULL-Werten bei Vergleichsoperatoren(=) und logischen Operatoren(OR) abgeleitet werden. Es folgen die Regeln für die Berechnung des Ergebnisses des Ausdrucks IN.

TRUE wird zurückgegeben, wenn der betreffende Nicht-NULL-Wert in der Liste enthalten ist.
FALSE wird zurückgegeben, wenn der Nicht-NULL-Wert nicht in der Liste enthalten ist und die Liste keine NULL-Werte enthält.
UNKNOWN wird zurückgegeben, wenn der Wert NULL ist, oder der Nicht-NULL-Wert nicht in der Liste enthalten ist und die Liste mindestens einen NULL-Wert enthält.

NOT IN gibt stets UNKNOWN zurück, wenn die Liste NULL enthält, und zwar unabhängig vom Eingabewert. Das liegt daran, dass UNKNOWN für IN zurückgegeben wird, wenn der Wert nicht in der Liste mit NULL enthalten ist, und weil NOT UNKNOWN wiederum UNKNOWN ist.

Beispiele

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

Freigeben über

NULL-Semantik

Vergleichsoperatoren

Beispiele

Logische Operatoren

Beispiele

Ausdrücke

NULL-intolerante Ausdrücke

Beispiele

Ausdrücke, die Operanden mit dem Wert NULL verarbeiten können

Beispiele

Integrierte Aggregatausdrücke

Beispiele

Bedingungsausdrücke in den Klauseln `WHERE`, `HAVING`, und `JOIN`

Beispiele

Aggregatoperatoren (`GROUP BY`, `DISTINCT`)

Beispiele

Sortieroperator (`ORDER BY`-Klausel)

Beispiele

Mengenoperatoren (`UNION`, `INTERSECT`, `EXCEPT`)

Beispiele

Unterabfragen `EXISTS` und `NOT EXISTS`

Beispiele

Unterabfragen `IN` und `NOT IN`

Beispiele

Feedback

Zusätzliche Ressourcen

Freigeben über

NULL-Semantik

Vergleichsoperatoren

Beispiele

Logische Operatoren

Beispiele

Ausdrücke

NULL-intolerante Ausdrücke

Beispiele

Ausdrücke, die Operanden mit dem Wert NULL verarbeiten können

Beispiele

Integrierte Aggregatausdrücke

Beispiele

Bedingungsausdrücke in den Klauseln WHERE, HAVING, und JOIN

Beispiele

Aggregatoperatoren (GROUP BY, DISTINCT)

Beispiele

Sortieroperator (ORDER BY-Klausel)

Beispiele

Mengenoperatoren (UNION, INTERSECT, EXCEPT)

Beispiele

Unterabfragen EXISTS und NOT EXISTS

Beispiele

Unterabfragen IN und NOT IN

Beispiele

Feedback

Zusätzliche Ressourcen

Bedingungsausdrücke in den Klauseln `WHERE`, `HAVING`, und `JOIN`

Aggregatoperatoren (`GROUP BY`, `DISTINCT`)

Sortieroperator (`ORDER BY`-Klausel)

Mengenoperatoren (`UNION`, `INTERSECT`, `EXCEPT`)

Unterabfragen `EXISTS` und `NOT EXISTS`

Unterabfragen `IN` und `NOT IN`