Sql wenn leer dann

Question

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

Inhaltsverzeichnis Show

Vergleiche mit <Ausdruck> is null2
Auf <Ausdruck> is null2 testen: NULLIF(<Ausdruck>, <Ausdruck>)1
<Ausdruck> is null2-sicherer Vergleich: NULLIF(<Ausdruck>, <Ausdruck>)8
<Ausdruck> is null2 in nicht-<Ausdruck> is null2 Werte wandeln und umgekehrt
1 + NULL8 — <Ausdruck> is null2 durch einen anderen Wert ersetzen
'foo ' || NULL || 'bar'8 — Einen einzelnen Wert durch <Ausdruck> is null2 ersetzen
1 + NULL7 — Mehrere Werte durch <Ausdruck> is null2 ersetzen
<Ausdruck> is null2 breitet sich durch Ausdrücke aus
<Ausdruck> is null2 in Aggregatfunktionen (count, sum, …)
<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, …
<Ausdruck> is null2 in <Ausdruck1> IS NOT DISTINCT FROM <Ausdruck2>9
<Ausdruck> is null2 in Unique Constraints
Zukunftsmusik: <Ausdruck> is null81
Kompatibilität

Der

<Ausdruck> is null

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

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

2 ist.

Benutzer können den

<Ausdruck> is null

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

2-Werten in SQL keinen Fehler oder Abbruch –

<Ausdruck> is null

2-Werte propagieren einfach durch Ausdrücke hindurch.

Inhalt:

Vergleiche mit
```
<Ausdruck> is null
```
2
```
<Ausdruck> is null
```
2 in nicht-
```
<Ausdruck> is null
```
2 Werte wandeln und umgekehrt
```
<Ausdruck> is null
```
2 breitet sich durch Ausdrücke aus
```
<Ausdruck> is null
```
2 in Aggregatfunktionen (count, sum, …)

<Ausdruck> is null

2 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, …

<Ausdruck> is null

2 in

<Ausdruck1> IS NOT DISTINCT FROM <Ausdruck2>

9

```
<Ausdruck> is null
```
2 in Unique Constraints
Kompatibilität

Vergleiche mit <Ausdruck> is null2

Vergleiche (

COALESCE(<Ausdruck>, 0)

2,

COALESCE(<Ausdruck>, 0)

3,

COALESCE(<Ausdruck>, 0)

4, …) mit

<Ausdruck> is null

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

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

2 oder nicht

<Ausdruck> is null

2 ist.

<Ausdruck> is null

Dieses Beispiel liefert true, wenn der Wert des Ausdruckes

<Ausdruck> is null

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

2 unentscheidbar sind – daher liefert

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

1 als Ergebnis unknown. Bei einem Test auf Unterscheidbarkeit werden

<Ausdruck> is null

2-Werte aber wie normale Werte behandelt. Daher sind zwei

<Ausdruck> is null

2-Werte ununterscheidbar („gleich“), während ein nicht-

<Ausdruck> is null

2-Wert von einem

<Ausdruck> is null

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

6,

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

7,

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

8 und dergleichen zu definieren. SQL:1999 und SQL:2003 haben dann das

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

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

9-Prädikat ist ein optionales Feature, das nicht von allen Datenbanken unterstützt wird. Siehe „

1 + NULL

1-sichere Vergleiche:

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

9“ 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 null

2-Wert zu ersetzen oder umgekehrt,

<Ausdruck> is null

2 durch einen anderen Wert zu ersetzen. Der

1 + NULL

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

2 durch einen anderen Wert.

COALESCE(<Ausdruck>, 0)

Das Beispiel liefert den Wert des Ausdrucks, außer es ist der

<Ausdruck> is null

2-Wert. Dann ist das Ergebnis die Zahl

'foo ' || NULL || 'bar'

3.

1 + NULL

8 akzeptiert beliebig viele Argumente und liefert den ersten nicht-

<Ausdruck> is null

2-Wert oder

<Ausdruck> is null

2, wenn alle Argumente

<Ausdruck> is null

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

2. Wenn die beiden Argumente gleich sind (

COALESCE(<Ausdruck>, 0)

4), ist das Ergebnis von

'foo ' || NULL || 'bar'

8

<Ausdruck> is null

2. Andernfalls wird das erste Argument als Ergebnis geliefert.

NULLIF(<Ausdruck>, <Ausdruck>)

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

Mit dem allgemeinen

1 + NULL

7-Ausdruck kann man natürlich andere Fälle abdecken – z. B. mehrere Werte in einem Schritt durch

<Ausdruck> is null

2 ersetzen:

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

Natürlich kann man im

1 + NULL

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

2-Wert verarbeiten, liefern grundsätzlich

<Ausdruck> is null

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

2:

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 null

2 und umgekehrt. Einerseits ist

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

8 true, andererseits wird

<Ausdruck> is null

2 beim Zusammenfügen von Zeichenketten (

<Ausdruck> is null

00) wie ein Leerstring behandelt. Das Ergebnis des zweiten Beispiels ist bei der Oracle-Datenbank daher

<Ausdruck> is null

01.

SQL Server bietet die überholte (deprecated) Einstellung

<Ausdruck> is null

02 an, um

<Ausdruck> is null

2 beim Zusammenfügen von Zeichenketten mittels

<Ausdruck> is null

04 wie einen Leerstring zu behandeln. Beachte: die Funktion

<Ausdruck> is null

05 behandelt

<Ausdruck> is null

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

2 ausbreitet.

Die Funktion

<Ausdruck> is null

08 zum zusammenfügen von Strings ist ein wichtiges Beispiel, weil sie von sehr vielen Datenbanken angeboten wird. Die Behandlung von

<Ausdruck> is null

2 in den Eingangsdaten ist jedoch unterschiedlich: Db2, MySQL und MariaDB handeln im Sinne des SQL-Standard und liefern

<Ausdruck> is null

2 als Ergebnis. H2, Oracle, PostgreSQL und SQL Server behandeln

<Ausdruck> is null

2 in

<Ausdruck> is null

08 wie einen Leerstring.

In logischen Ausdrücken (

<Ausdruck> is null

13,

<Ausdruck> is null

14,

NULLIF(<Ausdruck>, <Ausdruck>)

6) ist

<Ausdruck> is null

2 gleichbedeutend mit unknown.6

<Ausdruck> is null

2 (unknown) pflanzt sich nur durch logische Ausdrücke fort, wenn das Ergebnis durch einen

<Ausdruck> is null

2-Wert unentscheidbar wird. Daher gibt es zwei Fälle, bei denen das Ergebnis trotz eines

<Ausdruck> is null

2-Operanden nicht

<Ausdruck> is null

2 ist:

<Ausdruck> is null

21 ist false, weil die logische Konjunktion (

<Ausdruck> is null

13) false ist, sobald ein Argument false ist. Analog liefert

<Ausdruck> is null

23 das Ergebnis true.

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

Grundsätzlich entfernen Aggregatfunktionen

<Ausdruck> is null

2-Werte aus der Eingangsmenge bevor sie die Aggregierung durchführen.7 Das bedeutet, dass das Ergebnis einer Aggregatfunktion durch einen

<Ausdruck> is null

2-Wert nicht automatisch

<Ausdruck> is null

2 wird. Dieses Verhalten wird oft zur Umsetzung von Pivot-Abfragen genutzt.

Denksport

Wie wirkt sich die Ausbreitung des

<Ausdruck> is null

2-Wertes durch Ausdrücke und das Entfernen von

<Ausdruck> is null

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

2, 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 null

31-Klausel oder weil es

<Ausdruck> is null

2-Werte sind), oder (2) ein explizites oder implizites

<Ausdruck> is null

33 auf einer tatsächlich leeren Menge durchgeführt wird.8

<Ausdruck> is null

34 und

<Ausdruck> is null

35 liefern niemals

<Ausdruck> is null

2. 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 null

38,

<Ausdruck> is null

39,

<Ausdruck> is null

40), entfernen

<Ausdruck> is null

2-Werte nicht.10 Im Gegensatz zu

<Ausdruck> is null

39 verwendet

<Ausdruck> is null

43 die Standardeinstellung

<Ausdruck> is null

44 und entfernt daher

<Ausdruck> is null

2-Werte.11 Die meisten Produkte verwenden

<Ausdruck> is null

46 als Standardeinstellung für

<Ausdruck> is null

43.

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

2-Werte in eine Gruppe zusammengefasst.

Das betrifft auch andere Operationen, die auf Basis einer Gruppierung definiert sind:

<Ausdruck> is null

55 (in

<Ausdruck> is null

56 und Aggregatfunktionen),

<Ausdruck> is null

57,

<Ausdruck> is null

58 (ohne

<Ausdruck> is null

59), ….13

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

Der SQL Standard überlässt die Sortierung von

<Ausdruck> is null

2-Werte relativ zu nicht-

<Ausdruck> is null

2-Werten den Herstellern:14

<Ausdruck> is null

2-Werte können entweder vor, oder nach den nicht-

<Ausdruck> is null

2-Werten einsortiert werden (siehe Kompatibilität).

SQL:2003 hat den

<Ausdruck> is null

66-Zusatz

<Ausdruck> is null

67 eingeführt, damit SQL-Entwickler die Sortierung von

<Ausdruck> is null

2-Werten steuern können. Dieser Zusatz wird derzeit nur von wenigen Datenbanken unterstützt (siehe Kompatibilität).

<Ausdruck> is null

0

Der Effekt von

<Ausdruck> is null

67 kann mit einem

1 + NULL

7-Ausdruck jedoch in allen Datenbanken erreicht werden. Das folgende Beispiel setzt

<Ausdruck> is null

71 auf diese Weise um:

<Ausdruck> is null

1

Beachte, dass der

1 + NULL

7-Ausdruck einen neuen Sortierschlüssel definiert, der nur dazu dient, die

<Ausdruck> is null

2- von den nicht-

<Ausdruck> is null

2-Werten zu trennen.

<Ausdruck> is null2 in Unique Constraints

In Unique-Constraints ist der

<Ausdruck> is null

2-Wert ungleich allem – d. h. ungleich

<Ausdruck> is null

2 und ungleich anderen Werten.15 Da dadurch jede

<Ausdruck> is null

2 eine andere

<Ausdruck> is null

2 ist, akzeptieren Uniuqe-Constraints mehrere

<Ausdruck> is null

2-Werte.

Zukunftsmusik: <Ausdruck> is null81

Ein Entwurf zum nächsten SQL-Standard führt den

<Ausdruck> is null

82-Zusatz ein um den Umgang von

<Ausdruck> is null

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

84 erlaubt, mehrere

<Ausdruck> is null

2-Werte im Unqiue-Constraint zu akzeptieren, wenn alle Spalten des Constratins den

<Ausdruck> is null

2-Wert beinhalten.

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

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