SQL verwendet eine dreiwertige Logik: neben true (wahr) und false (falsch) kann das Ergebnis eines logischen Ausdruckes auch unknown (unbekannt) sein. Die dreiwertige Logik von SQL wird durch die Unterstützung des Null
-Wertes als Platzhalter für fehlende Daten notwendig. Wenn ein Null
-Wert das Ergebnis eines logischen Ausdrucks beeinflusst, wird statt true oder false das Ergebnis unknown geliefert.
Die dreiwertige Logik ist ein integraler Bestandteil von Core SQL und wird von praktisch jeder SQL-Datenbank angewendet.
Inhalt:
- Vergleiche mit dem
Null-Wert
- Logische Operationen mit Unknown
- Grundregel:
Where
,Having
,When
, etc. - Ausnahme:
Check
-Constraints - Verwandte Funktionen
- Binäre Entscheidungen auf Basis dreiwertiger Ergebnisse
- Interaktiver
is [not] (true|false|unknown)
-Emulator - Kompatibilität
Vergleiche mit dem Null-Wert
Der SQL-Null
-Wert steht stellvertretend für „könnte alles sein“. Daher kann man nicht sagen, ob ein Vergleich mit dem Null
-Wert true oder false ist. Hier kommt der dritte logische Wert, unknown, ins Spiel. Unknown bedeutet „true oder false, abhängig davon, für welche Werte die Platzhalter (null
) stehen“.
Das Ergebnis der folgenden Vergleiche ist daher immer unknown:0
NULL = 1
NULL <> 1
NULL > 1
NULL = NULL
Nichts ist gleich Null
. Nicht einmal Null
ist gleich Null
, weil jede Null
für einen anderen Wert stehen kann.
Eselsbrücke
Bei Vergleichen ist jede Null
ist eine andere Null
. Bei group by
, partition by
und dergleichen ist das anders.
Daher hat SQL das is null
-Prädikat zum Prüfen, ob ein Wert Null
ist und das is not distinct from
-Prädikat um zwei Werte zu vergleichen und dabei alle Null
-Werte als gleich zu betrachten.
Logische Operationen mit Unknown
Unknown verhält sich bei logischen Verknüpfungen (and
, or
) analog zu Null
-Werten bei Vergleichen: Das Ergebnis ist unknown, wenn es von einem Operanden, der unknown ist, abhängt. Im Gegensatz zu Vergleichen führt dieses Prinzip bei logischen Ausdrücken mit einem Unknown-Operanden nicht automatisch zum Ergebnis unknown. Denn das Ergebnis einer logischen Verknüpfung ist nur dann unknown, wenn es tatsächlich von einem Unknown-Operanden abhängt.
Dazu das folgende Beispiel:
(NULL = 1) OR (1 = 1)
Obwohl der erste Operand der Or
-Verknüpfung durch den Vergleich mit null
den Wert unknown hat, ist das Ergebnis der Verknüpfung true, weil Or
-Verknüpfungen true sind, sobald ein Operand true ist.
Diese Logik kann man sich auch mit einem anderen Modell erklären: Dafür ersetzt man jeden Null
-Wert gedanklich durch den Aufruf einer Zufallsfunktion random()
. Wenn das Ergebnis des Ausdruckes zwangsläufig immer dasselbe ist, egal welchen Wert die Zufallsfunktion liefert, ist das Ergebnis des Ausdrucks offenbar nicht vom Null
-Wert abhängig und daher auch nicht unknown.
Im oberen Beispiel kann man den Null
-Wert gedanklich durch die Werte 0 und 1 ersetzen, sodass der Vergleich im ersten Operanden false oder true wird. In beiden Fällen ist das Ergebnis des gesamten Ausdrucks jedoch true. Das Gesamtergebnis hängt also nicht davon ab, welchen Wert man für null
annimmt.
Wichtig
Der logische Wert unknown zeigt an, dass das Ergebnis tatsächlich von einem Null
-Wert abhängt.
Bei der And
-Verknüpfung gibt es einen ähnlichen Fall: And
-Verknüpfungen sind false, sobald ein Operand false ist. Daher ist das Ergebnis des folgenden Ausdrucks nicht unknown, sondern false.
(NULL = 1) AND (0 = 1)
In allen anderen Fällen ist das Ergebnis der logischen Operationen not
, and
und or
unknown, sobald ein Operand unknown ist.1
Grundregel: Where
, Having
, When
, etc.
Die Where
-, Having
- und When
-Klauseln (z. B. in Case
-Ausdrücken) erfordern den Wahrheitswert true.2 Es genügt nicht, dass eine Bedingung nicht false ist.
Das Ergebnis der folgenden Abfrage ist daher immer die leere Menge:
SELECT col
FROM t
WHERE col = NULL
Das Ergebnis des Vergleiches mit null
ist immer unknown. Daher verwirft die where
-Klausel alle Zeilen.
Für die Suche nach Null
-Werten muss man das is null
-Prädikat verwenden:
WHERE col IS NULL
Seltsame Konsequenz: P or not P
ist nicht immer true
Wie der Name „dreiwertige Logik“ bereits suggeriert, muss man bei logischen Ausdrücken immer drei Fälle berücksichtigen. Auf den ersten Blick scheint die folgende Where
-Klausel eine Tautologie zu sein – also eine immer wahre Aussage. Da sie aber nur zwei der drei möglichen Werte berücksichtigt – nämlich dass col = null
true oder false ist –, verhält sie sich aber völlig anders.
SELECT col
FROM t
WHERE col = NULL
OR NOT (col = NULL)
(1) Das Ergebnis des Vergleiches col = null
ist beide Male unknown; (2) not(unknown)
ist ebenfalls unknown; (3) or
ergibt nur true, wenn ein Operand true ist. Daher ist das Ergebnis der Bedingung immer unknown, sodass die Where
-Klausel alle Zeilen verwirft. Also das krasse Gegenteil von dem, was man bei einer zweiwertigen Logik erwartet.3
Seltsame Konsequenz: not in (null, …)
ist nie true
Betrachte dieses Beispiel:
WHERE 1 NOT IN (NULL)
Um dieses Beispiel zu verstehen, hilft es den Null
-Wert als „könnte alles sein“ oder als Zufallsfunktion zu verstehen. Wenn man dann zwei Werte findet, die den Ausdruck einmal true und einmal false machen, ist das Ergebnis unknown. Das funktioniert zum Beispiel mit den Werten 0
und 1
. Mit 0
wird der Ausdruck 1 NOT IN (0)
und damit true. Mit 1
wird der Ausdruck 1 NOT IN (1)
und damit klar false. Das tatsächliche Ergebnis des Ausdruckes ist daher unknown, weil es sich ändern kann, wenn man null
durch verschiedene Werte ersetzt.
Wenn man dieses Beispiel erweitert wird schnell klar, dass not in
-Prädikate, die einen Null
-Wert enthalten, niemals true sind.
WHERE 1 NOT IN (NULL, 2)
Das Ergebnis dieses Ausdrucks ist ebenfalls unknown, weil man mit verschiedenen Werten für null
verschiedene Ergebnisse erzielen kann (z. B. mit 0
und 1
). Es ist jedoch einfach zu zeigen, dass das Ergebnis von not in
-Prädikaten trotz eines Null
-Wertes false sein kann:
WHERE 1 NOT IN (NULL, 1)
Egal mit welchem Wert man null
ersetzt: das Ergebnis immer false.4
Tipp
Erlaube keinen Null
-Wert in not in
-Listen.
Erwäge bei Unterabfragen not exists
anstatt not in
zu verwenden5 oder füge eine Where
-Bedingung hinzu, die mögliche Null
-Werte entfernt.
Ausnahme: Check
-Constraints
Check
-Constraints verwenden die umgekehrte Logik: Sie lehnen false ab6, anstatt true zu akzeptieren, wie es die anderen Klauseln tun. Check
-Constraints akzeptieren also true und unknown.
Im folgenden Beispiel kann die Spalte a
oder b
einen Wert größer 10 haben, wenn der andere Wert null
ist.
CREATE TABLE t (
a NUMERIC CHECK (a >= 0),
b NUMERIC CHECK (b >= 0),
CHECK ( a + b <= 10 )
)
Verwandte Funktionen
Wahrheitstests: is [not] (true|false|unknown)
Ähnlich zu is null
hat der SQL-Standard auch eine optionale Funktion, um direkt auf die drei Wahrheitswerte zu prüfen:7
is [not] (true|false|unknown)
Beachte, dass dieser Is
-Test analog zu is [not] null
niemals unknown liefert.8
Tipp
Verwende (<Ausdruck>) is not false
statt (<Ausdruck>) or (<Ausdruck>) is null
. Siehe „Binäre Entscheidungen auf Basis dreiwertiger Ergebnisse“ unten.
Datentyp Boolean
: Literale true
, false
und unknown
Das kaum unterstützte optionale Feature T031, “BOOLEAN data type”, führt die Schlüsselworte true
, false
und unknown
auch außerhalb des Is
-Prädikates ein.
Beachte, dass der Wahrheitswert unknown und der Null
-Wert des Types Boolean
ununterscheidbar sind.9 Andernfalls hätte der Typ Boolean
vier logische Werte.
Der Unterschied zwischen den Literalen null
und unknown
ist, dass unknown
immer vom Typ Boolean
ist, während null
jeden Typen annehmen kann. Durch einen not null
-Constraint wird eine Spalte vom SQL-Typ Boolean
zu einem klassischen, zweiwertigen booleschen Wert.
Binäre Entscheidungen auf Basis dreiwertiger Ergebnisse
Wenn ein logischer Ausdruck nicht bedingungslos true oder false ist, verlagert die dreiwertige Logik die endgültige Entscheidung an eine andere Stelle. Letztendlich findet jedoch immer eine zweiwertige (binäre) Entscheidung statt – z. B. ob eine Zeile aufgrund einer Where
-Klausel angenommen oder abgelehnt wird.10
Wie oben erklärt, folgt SQL grundsätzlich der Regel, dass unknown wie false behandelt wird (Ausnahme: Check
-Constraints). Das ist so, als hätte jede Where
-, Having
-, etc.-Klausel einen impliziten is true
Test.
Unknown wie false zu behandeln ist jedoch nicht immer die richtige Wahl. Wenn man ein anderes Verhalten benötigt, muss man den is [not] (true|false|unknown)
-Test eben explizit einsetzen, um das gewünschte Verhalten zu erreichen.
Im folgenden Beispiel wird nullif
verwendet, um einen „Division durch Null (0)“-Fehler zu vermeiden. Für Zeilen, bei denen die Spalte d Null (0) ist, ist das Ergebnis der Bedingung in der Where
-Klausel unknown, sodass diese Zeilen abgelehnt werden.
SELECT n, d
FROM t
WHERE n/NULLIF(d,0) > 1
Wenn man die Zeilen mit d = 0
nicht verwerfen möchte, kann man natürlich OR d = 0
zur Where
-Klausel hinzufügen. Diese Lösung ist zwar richtig, erfordert aber ein Verständnis der Bedingung. Ein allgemeinerer Ansatz ist es, den ganzen Ausdruck, der den Null
-Wert liefern kann, zu wiederholen, um den Null
-Fall explizit einzuschließen: OR (n/NULLIF(d,0)) IS NULL
. Das ist natürlich auch nicht gerade elegant.
Der idiomatische Weg ein dreiwertiges SQL-Ergebnis auf ein zweiwertiges zu reduzieren ist der Wahrheitstest is [not] (true|false|unknown)
:
WHERE (n/NULLIF(d,0) > 1) IS NOT FALSE
Dieses Beispiel akzeptiert beide Ergebnisse: true und unknown und ist damit logisch gleichwertig zu den Lösungen mit einer Or
-Verknüpfung. Mit dieser Methode kommt jedoch völlig ohne Wiederholung oder semantischem Verständnis der Bedingung aus.
Tipp
Setze die Bedingung in Klammern um Mehrdeutigkeiten vorzubeugen.
(…) IS NOT FALSE
Das is not false
-Prädikat gehört zum optionalen Feature F571, “Truth value tests”, und wird nur von wenigen Datenbanken unterstützt. Mit einem Case
-Ausdruck ist es dennoch möglich, diese Logik ohne Wiederholung in praktisch allen Datenbanken umzusetzen.
WHERE CASE WHEN NOT(n/NULLIF(d,0) > 1)
THEN 0
ELSE 1
END = 1
Der Ausdruck prüft den False-Fall explizit ab (when not (…)
) und verwendet den Else
-Zweig für die beiden anderen Fälle true und unknown. Dadurch kann die nötige Übersetzung von unknown auf true ohne Wiederholungen erreicht werden. Die numerischen Literale wurden willkürlich gewählt um „false“ (0) und „true oder false“ (1) darzustellen. Der abschließende Vergleich (= 1
) ist immer true oder false, da keiner der beiden Operanden den Null
-Wert annehmen kann.
Die Methode mit case
kann unknown entweder true oder false zurechnen. Damit kann man vier der sechs möglichen Wahrheitstests umsetzen: is [not] (true|false)
. Die zwei verbleibenden Fälle, is unknown
und is not unknown
, können so nicht ohne Wiederholung umgesetzt werden.
Hinweis in eigener Sache
Ich lebe von SQL-Schulungen, anderen SQL-Dienstleistungen und dem Verkauf meines Buches. Mehr dazu auf winand.at.
Um einen is [not] unknown
-Test zu emulieren, kann man sich zunutze machen, dass null
und unknown beim Datentyp Boolean
gleichbedeutend sind. Daher ist es grundsätzlich möglich is [not] null
anstatt is [not] unknown
verwenden. In der Praxis ist das jedoch kaum nützlich, weil Datenbanken, die is [not] unknown
nicht unterstützen, den Datentyp Boolean
meist auch nicht unterstützen.
Stattdessen muss man den is [not] null
-Test auf die Operanden des Vergleiches anwenden und das Ergebnis mit einer Or
- bzw. And
-Operation verbinden.
Das folgende Beispiel zeigt das:
(<Ausdr1> <op> <Ausdr2>) IS UNKNOWN
Ist logisch gleichwertig mit:
<Ausdr1> IS NULL
OR <Ausdr2> IS NULL
Für einen is not unknown
-Test müssen natürlich is not null
-Tests mit einer And
-Operation verbunden werden.11
Interaktiver is [not] (true|false|unknown)
-Emulator
Klicke auf die Wahrheitswerte im Bild um einen SQL-Ausdruck zu erhalten, der die aktivierten Werte auf true, die anderen auf false übersetzt.
Wahrheitstest
… IS TRUE
(<Ausdr1> <op> <Ausdr2>) IS UNKNOWN
… IS FALSE
… IS NOT TRUE
(<Ausdr1> <op> <Ausdr2>) IS NOT UNKNOWN
… IS NOT FALSE
Tautologie
Kontradiktion
SQL-92-Alternative
CASE WHEN …
THEN 0
ELSE 1
END = 0
<Ausdr1> IS NULL
OR <Ausdr2> IS NULL
CASE WHEN NOT(…)
THEN 0
ELSE 1
END = 0
CASE WHEN …
THEN 0
ELSE 1
END = 1
<Ausdr1> IS NOT NULL
AND <Ausdr2> IS NOT NULL
CASE WHEN NOT(…)
THEN 0
ELSE 1
END = 1
Tautologie
Kontradiktion
Kompatibilität
Die dreiwertige Logik ist von Anfang an integraler Bestandteil von SQL und wird weitestgehend umgesetzt.
SQL:1999 hat zwei artverwandte, optionale Funktionen eingeführt:
F571, “Truth value tests”: erweitert den
Is
-Operator um alle drei logischen Werte.T031, “BOOLEAN data type”: definiert den Datentyp
Boolean
, die Aggregatfunktionenevery
,any
undsome
, sowie die Literaletrue
,false
undunknown
auch außerhalb vonis [not]
.