NULL in SQL: Platzhalter für fehlende Daten

„Jeder [SQL] Datentyp beinhaltet einen speziellen Wert, genannt der Null-Wert,“0 „der genutzt wird, um das Fehlen eines Datenwertes anzuzeigen“.1

Der Null-Wert zeigt nicht an, warum ein Wert fehlt – er markiert bloß Stellen, für die es keinen Wert gibt. SQL selbst verwendet den Null-Wert unter anderem im Ergebnis eines outer joins.2 Obwohl es Ausnahmen gibt,3 kann man im Allgemeinen nicht sagen, warum ein Wert Null ist.

Benutzer können den Null-Wert für beliebige Zwecke einsetzen. Die vermutlich häufigste Anwendung ist, optionale Attribute ohne zusätzlicher Tabelle umzusetzen. Die Fehlerbehandlung ist ein anderer, wichtiger Anwendungsfall: anders als bei anderen Programmiersprachen verursacht die Verarbeitung von Null-Werten in SQL keinen Fehler oder Abbruch – Null-Werte propagieren einfach durch Ausdrücke hindurch.

Inhalt:

1. Vergleiche mit Null
2. Null in nicht-Null Werte wandeln und umgekehrt
3. Null breitet sich durch Ausdrücke aus
4. Null in Aggregatfunktionen (count, sum, …)
5. Null in Distinct, Group by, Partition by, Union, …
6. Null in Order By
7. Null in Unique Constraints
8. Kompatibilität

Vergleiche mit Null

Vergleiche (<, >, =, …) mit Null liefern weder true (wahr) noch false (falsch) sondern den dritten logischen Wert von SQL: unknown (unbekannt). Da die Where-Klausel unknown wie false behandelt, entfernt sie im folgenden Beispiel alle Zeilen – selbst jene, bei denen col tatsächlich Null ist.

WHERE col = null

Auf Null testen: is [not] null

Das SQL-Prädikat is [not] null testet, ob der Wert eines Ausdruckes Null oder nicht Null ist.

<Ausdruck> is null

Dieses Beispiel liefert true, wenn der Wert des Ausdruckes Null ist oder false, wenn nicht. Das optionale not negiert das Ergebnis.

Null-sicherer Vergleich: is not distinct from

SQL kennt zwei unterschiedliche Kriterien, um zu bestimmen ob zwei Werte „gleich“ sind: Gleichheit (=) und Unterscheidbarkeit (distinct). Der Unterschied ist, dass das beim Gleichheitszeichen (=) vergleiche mit Null unentscheidbar sind – daher liefert col = null als Ergebnis unknown. Bei einem Test auf Unterscheidbarkeit werden Null-Werte aber wie normale Werte behandelt. Daher sind zwei Null-Werte ununterscheidbar („gleich“), während ein nicht-Null-Wert von einem Null-wert sehr wohl unterscheidbar ist.

Ursprünglich wurde das Unterscheidbarkeitskriterium im SQL-Standard nur intern genutzt, um das Verhalten von group by, select distinct, count(distinct …) und dergleichen zu definieren. SQL:1999 und SQL:2003 haben dann das is [not] distinct from-Prädikat eingeführt, damit der Test auf Unterscheidbarkeit auch von SQL-Programmieren genutzt werden kann.

<Ausdruck1> IS NOT DISTINCT FROM <Ausdruck2>

Das is [not] distinct from-Prädikat ist ein optionales Feature, das nicht von allen Datenbanken unterstützt wird. Siehe „NULL-sichere Vergleiche: is [not] distinct from“ für Alternativen.

Null in nicht-Null Werte wandeln und umgekehrt

Manchmal ist es nötig, einen speziellen Wert durch den Null-Wert zu ersetzen oder umgekehrt, Null durch einen anderen Wert zu ersetzen. Der Case-Ausdruck bietet dafür sogar zwei Kurzformen an.

Coalesce — Null durch einen anderen Wert ersetzen

SQL’s coalesce ersetzt Null durch einen anderen Wert.

COALESCE(<Ausdruck>, 0)

Das Beispiel liefert den Wert des Ausdrucks, außer es ist der Null-Wert. Dann ist das Ergebnis die Zahl 0.

Coalesce akzeptiert beliebig viele Argumente und liefert den ersten nicht-Null-Wert oder Null, wenn alle Argumente Null sind.

Nullif — Einen einzelnen Wert durch Null ersetzen

SQL’s Nullif ersetzt einen einzelnen Wert durch Null. Wenn die beiden Argumente gleich sind (=), ist das Ergebnis von Nullif Null. Andernfalls wird das erste Argument als Ergebnis geliefert.

NULLIF(<Ausdruck>, <Ausdruck>)

Case — Mehrere Werte durch Null ersetzen

Mit dem allgemeinen Case-Ausdruck kann man natürlich andere Fälle abdecken – z. B. mehrere Werte in einem Schritt durch Null ersetzen:

CASE WHEN <Ausdruck> IN (…)
     THEN null
     ELSE <Ausdruck>
 END

Natürlich kann man im Case-Ausdruck auch andere Vergleiche durchführen: >, <, between und so weiter.

Null breitet sich durch Ausdrücke aus

Ausdrücke und Funktionen, die einen Null-Wert verarbeiten, liefern grundsätzlich Null als Ergebnis.4 Nennenswerte Ausnahmen sind Aggegatfunktionen und – aufgrund der dreiwertigen Logik von SQL – zwei logische Operationen.5

Das Ergebnis der folgenden Ausdrücke ist daher immer Null:

1 + NULL

'foo ' || NULL || 'bar'

SUBSTRING('foo bar' FROM 4 FOR NULL)

In logischen Ausdrücken (and, or, not) ist Null gleichbedeutend mit unknown.6 Null (unknown) pflanzt sich nur durch logische Ausdrücke fort, wenn das Ergebnis durch einen Null-Wert unentscheidbar wird. Daher gibt es zwei Fälle, bei denen das Ergebnis trotz eines Null-Operanden nicht Null ist: Null and false ist false, weil die logische Konjunktion (and) false ist, sobald ein Argument false ist. Analog liefert null or true das Ergebnis true.

Null in Aggregatfunktionen (count, sum, …)

Grundsätzlich entfernen Aggregatfunktionen Null-Werte aus der Eingangsmenge bevor sie die Aggregierung durchführen.7 Das bedeutet, dass das Ergebnis einer Aggregatfunktion durch einen Null-Wert nicht automatisch Null wird. Dieses Verhalten wird oft zur Umsetzung von Pivot-Abfragen genutzt.

SUM(a+b)
SUM(a) + SUM(b)

Das Ergebnis einer Aggregatfunktion ist nur Null, wenn eine effektiv leere Eingangsmenge vorliegt. Das ist der Fall, wenn (1) alle Zeilen vor der Aggregierung entfernt werden (z. B. aufgrund einer Filter-Klausel oder weil es Null-Werte sind), oder (2) ein explizites oder implizites group by () auf einer tatsächlich leeren Menge durchgeführt wird.8

Count und regr_count liefern niemals Null. Bei einer effektiv leeren Eingangsmenge liefern diese Funktionen den numerischen Wert 0.9

Aggregatfunktionen, die strukturierte Daten liefern (array_agg, json_objectagg, xmlagg), entfernen Null-Werte nicht.10 Im Gegensatz zu json_objectagg verwendet json_arrayagg die Standardeinstellung absent on null und entfernt daher Null-Werte.11 Die meisten Produkte verwenden null on null als Standardeinstellung für json_arrayagg.

Null in Distinct, Group by, Partition by, Union, …

Gruppierungen werden aufgrund des Unterscheidbarkeitskriteriums is not distinct from durchgeführt.12 Daher werden alle Null-Werte in eine Gruppe zusammengefasst.

Das betrifft auch andere Operationen, die auf Basis einer Gruppierung definiert sind: distinct (in select und Aggregatfunktionen), partition by, union (ohne all), ….13

Null in Order By

Der SQL Standard überlässt die Sortierung von Null-Werte relativ zu nicht-Null-Werten den Herstellern:14 Null-Werte können entweder vor, oder nach den nicht-Null-Werten einsortiert werden (siehe Kompatibilität).

SQL:2003 hat den order by-Zusatz nulls (first|last) eingeführt, damit SQL-Entwickler die Sortierung von Null-Werten steuern können. Dieser Zusatz wird derzeit nur von wenigen Datenbanken unterstützt (siehe Kompatibilität).

ORDER BY <expression> [ASC|DESC] [NULLS (FIRST|LAST)]
     [ , <expression> [ASC|DESC] [NULLS (FIRST|LAST)] ]
       …

Der Effekt von nulls (first|last) kann mit einem Case-Ausdruck jedoch in allen Datenbanken erreicht werden. Das folgende Beispiel setzt order by c nulls first auf diese Weise um:

ORDER BY CASE WHEN c IS NULL
              THEN 0
              ELSE 1
          END
       , c

Beachte, dass der Case-Ausdruck einen neuen Sortierschlüssel definiert, der nur dazu dient, die Null- von den nicht-Null-Werten zu trennen.

Null in Unique Constraints

In Unique-Constraints ist der Null-Wert ungleich allem – d. h. ungleich Null und ungleich anderen Werten.15 Da dadurch jede Null eine andere Null ist, akzeptieren Unique-Constraints mehrere Null-Werte.

Zukunftsmusik: NULLS [NOT] DISTINCT

Ein Entwurf zum nächsten SQL-Standard führt den Nulls [not] distinct-Zusatz ein um den Umgang von Null-Werten in Unique-Constraints steuern zu können. Entgegen SQL:2016 ist das Default-Verhalten dann von der Implementierung definiert. Weiters ist es auch bei nulls not distinct erlaubt, mehrere Null-Werte im Unqiue-Constraint zu akzeptieren, wenn alle Spalten des Constratins den Null-Wert beinhalten.

1. Unterstützt keine Null-baren Spalten in Unique-Constraints (T591)
2. Wenn alle Spalten des Constraints Null sind
3. Wenn manche, aber nicht alle Spalten des Constraints Null sind

Kompatibilität

Null ist von Anfang an Teil des SQL-Standards. Unique-Constraints auf Spalten mit Null-Werten wurde von intermediate SQL-92 gefordert. Seit SQL:1999 ist das ein optionales Feature (T591).

Die explizite Sortierung von Null-Werten (nulls (first|last)) wurde mit SQL:2003 als Teil des optionalen Features T611, “Elementary OLAP operations”, eingeführt.

1. Leerstring is null. Beim Zusammenfügen wirkt Null wie ein Leerstring • concat() behandelt Null wie einen Leerstring
2. concat() behandelt Null wie einen Leerstring
3. Unterstützt keine Unique-Constrains
4. Unterstützt keine Unique-Constrains auf Spalten ohne not null-Constraint
5. Abweichend bei mehrspaltigen Constraints (siehe Oben)
6. Erlaubt nur einen Null-Wert
7. Voreinstellung: Sortierung als kleinster Wert
8. Voreinstellung: Sortierung als größter Wert