Sql wenn leer dann

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

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

Benutzer können den <Ausdruck> is null2-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 <Ausdruck> is null2-Werten in SQL keinen Fehler oder Abbruch – <Ausdruck> is null2-Werte propagieren einfach durch Ausdrücke hindurch.

Inhalt:

1. Vergleiche mit <Ausdruck> is null2
2. <Ausdruck> is null2 in nicht-<Ausdruck> is null2 Werte wandeln und umgekehrt
3. <Ausdruck> is null2 breitet sich durch Ausdrücke aus
4. <Ausdruck> is null2 in Aggregatfunktionen (count, sum, …)
5. <Ausdruck> is null2 in <Ausdruck1> IS NOT DISTINCT FROM <Ausdruck2>4, <Ausdruck1> IS NOT DISTINCT FROM <Ausdruck2>5, <Ausdruck1> IS NOT DISTINCT FROM <Ausdruck2>6, <Ausdruck1> IS NOT DISTINCT FROM <Ausdruck2>7, …
6. <Ausdruck> is null2 in <Ausdruck1> IS NOT DISTINCT FROM <Ausdruck2>9
7. <Ausdruck> is null2 in Unique Constraints
8. Kompatibilität

Vergleiche mit <Ausdruck> is null2

Vergleiche (COALESCE(<Ausdruck>, 0)2, COALESCE(<Ausdruck>, 0)3, COALESCE(<Ausdruck>, 0)4, …) mit <Ausdruck> is null2 liefern weder true (wahr) noch false (falsch) sondern den dritten logischen Wert von SQL: unknown (unbekannt). Da die COALESCE(<Ausdruck>, 0)6-Klausel unknown wie false behandelt, entfernt sie im folgenden Beispiel alle Zeilen – selbst jene, bei denen COALESCE(<Ausdruck>, 0)7 tatsächlich <Ausdruck> is null2 ist.

WHERE col = null

Warnung

Die SQL Server-Einstellung COALESCE(<Ausdruck>, 0)9 ändert dieses Verhalten.

Auf <Ausdruck> is null2 testen: NULLIF(<Ausdruck>, <Ausdruck>)1

Das SQL-Prädikat NULLIF(<Ausdruck>, <Ausdruck>)1 testet, ob der Wert eines Ausdruckes <Ausdruck> is null2 oder nicht <Ausdruck> is null2 ist.

<Ausdruck> is null

Dieses Beispiel liefert true, wenn der Wert des Ausdruckes <Ausdruck> is null2 ist oder false, wenn nicht. Das optionale NULLIF(<Ausdruck>, <Ausdruck>)6 negiert das Ergebnis.

<Ausdruck> is null2-sicherer Vergleich: NULLIF(<Ausdruck>, <Ausdruck>)8

SQL kennt zwei unterschiedliche Kriterien, um zu bestimmen ob zwei Werte „gleich“ sind: Gleichheit (COALESCE(<Ausdruck>, 0)4) und Unterscheidbarkeit (distinct). Der Unterschied ist, dass das beim Gleichheitszeichen (=) vergleiche mit <Ausdruck> is null2 unentscheidbar sind – daher liefert CASE WHEN <Ausdruck> IN (…) THEN null ELSE <Ausdruck> END1 als Ergebnis unknown. Bei einem Test auf Unterscheidbarkeit werden <Ausdruck> is null2-Werte aber wie normale Werte behandelt. Daher sind zwei <Ausdruck> is null2-Werte ununterscheidbar („gleich“), während ein nicht-<Ausdruck> is null2-Wert von einem <Ausdruck> is null2-wert sehr wohl unterscheidbar ist.

Ursprünglich wurde das Unterscheidbarkeitskriterium im SQL-Standard nur intern genutzt, um das Verhalten von CASE WHEN <Ausdruck> IN (…) THEN null ELSE <Ausdruck> END6, CASE WHEN <Ausdruck> IN (…) THEN null ELSE <Ausdruck> END7, CASE WHEN <Ausdruck> IN (…) THEN null ELSE <Ausdruck> END8 und dergleichen zu definieren. SQL:1999 und SQL:2003 haben dann das CASE WHEN <Ausdruck> IN (…) THEN null ELSE <Ausdruck> END9-Prädikat eingeführt, damit der Test auf Unterscheidbarkeit auch von SQL-Programmieren genutzt werden kann.

<Ausdruck1> IS NOT DISTINCT FROM <Ausdruck2>

Das CASE WHEN <Ausdruck> IN (…) THEN null ELSE <Ausdruck> END9-Prädikat ist ein optionales Feature, das nicht von allen Datenbanken unterstützt wird. Siehe „1 + NULL1-sichere Vergleiche: CASE WHEN <Ausdruck> IN (…) THEN null ELSE <Ausdruck> END9“ für Alternativen.

<Ausdruck> is null2 in nicht-<Ausdruck> is null2 Werte wandeln und umgekehrt

Manchmal ist es nötig, einen speziellen Wert durch den <Ausdruck> is null2-Wert zu ersetzen oder umgekehrt, <Ausdruck> is null2 durch einen anderen Wert zu ersetzen. Der 1 + NULL7-Ausdruck bietet dafür sogar zwei Kurzformen an.

1 + NULL8 — <Ausdruck> is null2 durch einen anderen Wert ersetzen

SQL’s 'foo ' || NULL || 'bar'0 ersetzt <Ausdruck> is null2 durch einen anderen Wert.

COALESCE(<Ausdruck>, 0)

Das Beispiel liefert den Wert des Ausdrucks, außer es ist der <Ausdruck> is null2-Wert. Dann ist das Ergebnis die Zahl 'foo ' || NULL || 'bar'3.

1 + NULL8 akzeptiert beliebig viele Argumente und liefert den ersten nicht-<Ausdruck> is null2-Wert oder <Ausdruck> is null2, wenn alle Argumente <Ausdruck> is null2 sind.

'foo ' || NULL || 'bar'8 — Einen einzelnen Wert durch <Ausdruck> is null2 ersetzen

SQL’s 'foo ' || NULL || 'bar'8 ersetzt einen einzelnen Wert durch <Ausdruck> is null2. Wenn die beiden Argumente gleich sind (COALESCE(<Ausdruck>, 0)4), ist das Ergebnis von 'foo ' || NULL || 'bar'8 <Ausdruck> is null2. Andernfalls wird das erste Argument als Ergebnis geliefert.

NULLIF(<Ausdruck>, <Ausdruck>)

1 + NULL7 — Mehrere Werte durch <Ausdruck> is null2 ersetzen

Mit dem allgemeinen 1 + NULL7-Ausdruck kann man natürlich andere Fälle abdecken – z. B. mehrere Werte in einem Schritt durch <Ausdruck> is null2 ersetzen:

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

Natürlich kann man im 1 + NULL7-Ausdruck auch andere Vergleiche durchführen: COALESCE(<Ausdruck>, 0)3, COALESCE(<Ausdruck>, 0)2, SUM(a+b) SUM(a) + SUM(b)2 und so weiter.

<Ausdruck> is null2 breitet sich durch Ausdrücke aus

Ausdrücke und Funktionen, die einen <Ausdruck> is null2-Wert verarbeiten, liefern grundsätzlich <Ausdruck> is null2 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 <Ausdruck> is null2:

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 <Ausdruck> is null2 und umgekehrt. Einerseits ist SUM(a+b) SUM(a) + SUM(b)8 true, andererseits wird <Ausdruck> is null2 beim Zusammenfügen von Zeichenketten (<Ausdruck> is null00) wie ein Leerstring behandelt. Das Ergebnis des zweiten Beispiels ist bei der Oracle-Datenbank daher <Ausdruck> is null01.

SQL Server bietet die überholte (deprecated) Einstellung <Ausdruck> is null02 an, um <Ausdruck> is null2 beim Zusammenfügen von Zeichenketten mittels <Ausdruck> is null04 wie einen Leerstring zu behandeln. Beachte: die Funktion <Ausdruck> is null05 behandelt <Ausdruck> is null2 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 <Ausdruck> is null2 ausbreitet.

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

In logischen Ausdrücken (<Ausdruck> is null13, <Ausdruck> is null14, NULLIF(<Ausdruck>, <Ausdruck>)6) ist <Ausdruck> is null2 gleichbedeutend mit unknown.6 <Ausdruck> is null2 (unknown) pflanzt sich nur durch logische Ausdrücke fort, wenn das Ergebnis durch einen <Ausdruck> is null2-Wert unentscheidbar wird. Daher gibt es zwei Fälle, bei denen das Ergebnis trotz eines <Ausdruck> is null2-Operanden nicht <Ausdruck> is null2 ist: <Ausdruck> is null21 ist false, weil die logische Konjunktion (<Ausdruck> is null13) false ist, sobald ein Argument false ist. Analog liefert <Ausdruck> is null23 das Ergebnis true.

<Ausdruck> is null2 in Aggregatfunktionen (count, sum, …)

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

Denksport

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

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

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

<Ausdruck> is null34 und <Ausdruck> is null35 liefern niemals <Ausdruck> is null2. Bei einer effektiv leeren Eingangsmenge liefern diese Funktionen den numerischen Wert 'foo ' || NULL || 'bar'3.9

Hinweis in eigener Sache

Ich lebe von SQL-Schulungen, SQL-Tuning und Beratung sowie dem Verkauf meines Buches „SQL Performance Explained“. Mehr auf winand.at.

Aggregatfunktionen, die strukturierte Daten liefern (<Ausdruck> is null38, <Ausdruck> is null39, <Ausdruck> is null40), entfernen <Ausdruck> is null2-Werte nicht.10 Im Gegensatz zu <Ausdruck> is null39 verwendet <Ausdruck> is null43 die Standardeinstellung <Ausdruck> is null44 und entfernt daher <Ausdruck> is null2-Werte.11 Die meisten Produkte verwenden <Ausdruck> is null46 als Standardeinstellung für <Ausdruck> is null43.

<Ausdruck> is null2 in <Ausdruck1> IS NOT DISTINCT FROM <Ausdruck2>4, <Ausdruck1> IS NOT DISTINCT FROM <Ausdruck2>5, <Ausdruck1> IS NOT DISTINCT FROM <Ausdruck2>6, <Ausdruck1> IS NOT DISTINCT FROM <Ausdruck2>7, …

Gruppierungen werden aufgrund des Unterscheidbarkeitskriteriums NULLIF(<Ausdruck>, <Ausdruck>)8 durchgeführt.12 Daher werden alle <Ausdruck> is null2-Werte in eine Gruppe zusammengefasst.

Das betrifft auch andere Operationen, die auf Basis einer Gruppierung definiert sind: <Ausdruck> is null55 (in <Ausdruck> is null56 und Aggregatfunktionen), <Ausdruck> is null57, <Ausdruck> is null58 (ohne <Ausdruck> is null59), ….13

<Ausdruck> is null2 in <Ausdruck1> IS NOT DISTINCT FROM <Ausdruck2>9

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

SQL:2003 hat den <Ausdruck> is null66-Zusatz <Ausdruck> is null67 eingeführt, damit SQL-Entwickler die Sortierung von <Ausdruck> is null2-Werten steuern können. Dieser Zusatz wird derzeit nur von wenigen Datenbanken unterstützt (siehe Kompatibilität).

<Ausdruck> is null0

Der Effekt von <Ausdruck> is null67 kann mit einem 1 + NULL7-Ausdruck jedoch in allen Datenbanken erreicht werden. Das folgende Beispiel setzt <Ausdruck> is null71 auf diese Weise um:

<Ausdruck> is null1

Beachte, dass der 1 + NULL7-Ausdruck einen neuen Sortierschlüssel definiert, der nur dazu dient, die <Ausdruck> is null2- von den nicht-<Ausdruck> is null2-Werten zu trennen.

<Ausdruck> is null2 in Unique Constraints

In Unique-Constraints ist der <Ausdruck> is null2-Wert ungleich allem – d. h. ungleich <Ausdruck> is null2 und ungleich anderen Werten.15 Da dadurch jede <Ausdruck> is null2 eine andere <Ausdruck> is null2 ist, akzeptieren Uniuqe-Constraints mehrere <Ausdruck> is null2-Werte.

Zukunftsmusik: <Ausdruck> is null81

Ein Entwurf zum nächsten SQL-Standard führt den <Ausdruck> is null82-Zusatz ein um den Umgang von <Ausdruck> is null2-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 <Ausdruck> is null84 erlaubt, mehrere <Ausdruck> is null2-Werte im Unqiue-Constraint zu akzeptieren, wenn alle Spalten des Constratins den <Ausdruck> is null2-Wert beinhalten.

BigQueryaaDb2 (LUW)aaMariaDBMySQLOracle DBbcPostgreSQLSQL ServerSQLitedefault:nulls distinctdefault:nulls not distinct

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

Kompatibilität

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

Die explizite Sortierung von <Ausdruck> is null2-Werten (<Ausdruck> is null67) wurde mit SQL:2003 als Teil des optionalen Features T611, “Elementary OLAP operations”, eingeführt.