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 Unique Constraints
  7. Null in Order By
  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

Warnung

Die SQL Server-Einstellung SET ANSI_NULLS OFF ändert dieses Verhalten.

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.

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

Abweichungen: Oracle und SQL Server Datenbanken

Die Oracle-Datenbank behandelt den Leerstring wie null und umgekehrt. Einerseits ist '' IS NULL true, andererseits wird null beim Zusammenfügen von Zeichenketten (||) wie ein Leerstring behandelt. Das Ergebnis des zweiten Beispiels ist bei der Oracle-Datenbank daher 'foo bar'.

SQL Server bietet die überholte (deprecated) Einstellung SET CONCAT_NULL_YIELDS_NULL OFF an, um null beim Zusammenfügen von Zeichenketten mittels + wie einen Leerstring zu behandeln. Beachte: die Funktion concat behandelt null immer als Leerstring.

Hinweis: Proprietäre Funktionen

Die meisten Datenbanken unterstützten mehr Funktionen als vom SQL-Standard definiert. Diese Funktionen müssen nicht unbedingt der Idee folgen, dass sich null ausbreitet.

Die Funktion concat() zum zusammenfügen von Strings ist ein wichtiges Beispiel, weil sie von sehr vielen Datenbanken angeboten wird. Die Behandlung von null in den Eingangsdaten ist jedoch unterschiedlich: Db2, MySQL und MariaDB handeln im Sinne des SQL-Standard und liefern null als Ergebnis. H2, Oracle, PostgreSQL und SQL Server behandeln null in concat() wie einen Leerstring.

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.

Denksport

Wie wirkt sich die Ausbreitung des Null-Wertes durch Ausdrücke und das Entfernen von Null-Werten vor der Aggregation auf die folgenden Ausdrücke aus:

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

Hinweis in eigener Sache

Wenn Dir dieser Artikel gefällt, könnte mein Buch SQL Performance Explained oder mein Training auch etwas für Dich sein.

Aggregatfunktionen, die strukturierte Daten liefern (array_agg, json_objectagg, json_arrayagg, xmlagg), entfernen null-Werte nicht.10

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

Gruppierungen werden aufgrund des Unterscheidbarkeitskriteriums is not distinct from durchgeführt.11 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), ….12

Null in Unique Constraints

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

Abweichungen: SQL Server und die Oracle-Datenbank

SQL Server Unique-Constraints arbeiten auf Basis der Unterscheidbarkeit – daher wird pro Spalte nur ein null-Wert akzeptiert.

Die Oracle-Datenbank erlaubt in einem Unique-Constraint nur dann mehrere null-Werte, wenn alle Spalten des zugrunde liegenden Indexes null sind. Wenn eine Spalte nicht-null ist, wird in den restlichen Spalten nur ein null-Wert akzeptiert. Siehe „null in der Oracle-Datenbank indizieren“ auf Use The Index, Luke!

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.

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.

Über den Autor

Foto von Markus Winand

Markus Winand lehrt effizientes SQL – inhouse und online. Er minimiert die Entwicklungszeit durch modernes SQL und optimiert die Laufzeit durch schlaue Indizierung – dazu hat er auch das Buch SQL Performance Explained veröffentlicht.

Sein Buch bei Amazon kaufen

Titelbild von „SQL Performance Explained“: Eichhörnchen läuft durchs Grass

Die Essenz: SQL-Tuning auf 200 Seiten

Bei Amazon kaufen
(Taschenbuch)

Taschenbuch und PDF auch auf Markus' Webseite erhältlich.

Sein Training

Sein beliebtes Training stimmt Entwickler auf SQL Performance ein.

Erfahren Sie mehr»

Fußnoten

0

SQL:2011-1: §4.4.2

1

SQL:2011-1: §3.1.1.12

2

SQL:2016-2: §7.10 General Rule 3c und 4

3

Bei grouping sets, kann man mit der Grouping-Funktion unterscheiden, ob ein Null-Wert aus den Daten kommt, oder aufgrund der Gruppierung zustande gekommen ist.

4

Für numeric value Ausdrücke: SQL:2016-2: §6.29 General Rule 1

Für string value Ausdrücke: SQL:2016-2: §6.31 General Rule 1

Für datetime value Ausdrücke: SQL:2016-2: §6.35 General Rule 1

Für interval value Ausdrücke: SQL:2016-2: §6.38 General Rule 2

5

TRUE OR NULL ist zum Beispiel TRUE.

6

SQL:2016-2: §4.5.1

7

SQL:2016-2: §4.16.4

8

Beispiel: select min(c) from empty_table

SQL:2016-2: §7.16 Syntax Rule 16 (implizites GROUP BY ()), SQL:2016-2: §7.13 General Rule 2a.

9

SQL:2016-2: §10.9 General Rule 5 (count) und 8c (regr_count). Diese Ausnahme (0, nicht null) hat in frühen Datenbanken Fehler verursacht (“The COUNT bug”; Kiessling, W. 1984).

10

Für array_agg wird das in SQL:2016-2: §10.9, General Rule 12gii, note 510 explizit erwähnt.

11

SQL:2016-2: §4.1.5

12

Siehe SQL:2016-2: §9.12 für eine vollständige Liste.

Eigentlich sind die Set-Funktionen auf Basis des Begriffes „Duplikat” definiert (SQL:2016-2: §7.17 General Rule 3biii). Dieser Begriff ist jedoch durch das Unterscheidbarkeitskriterium definiert (SQL:2016-2: §3.1.6.11).

13

Eigentlich sind Unique-Constraints auf Basis des uniuqe-Prädikates definiert (SQL:2016-2: §11.7 Syntax Rule 6), welches wiederum auf Basis der Unterscheidbarkeit definiert ist, wenn ein nicht-null-Wert beteiligt ist (SQL:2016-2: §8.11 General Rule 2).

14

SQL:2016-2: §10.10 Syntax Rule 3

15

Die Oracle-Datenbank ist im Umgang mit null in Unique-Constraints nicht standard konform: die Oracle erfüllt die Anforderung auch ohne weiteres zutun.

16

MySQL und MariaDB bieten aber auch physisch gespeicherte generated columns an.

„modern SQL“ von Markus Winand ist unter einer Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License lizenziert.
Impressum | Kontakt | KEINE GEWÄHR | Handelsmarken | Datenschutz | CC-BY-NC-ND 3.0 Lizenz