Die dreiwertige Logik von SQL


trueunknownfalse

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:

  1. Vergleiche mit dem Null-Wert
  2. Logische Operationen mit Unknown
  3. Grundregel: Where, Having, When, etc.
  4. Ausnahme: Check-Constraints
  5. Verwandte Funktionen
  6. Binäre Entscheidungen auf Basis dreiwertiger Ergebnisse
  7. Interaktiver is [not] (true|false|unknown)-Emulator
  8. 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.

unknownfalsetrue

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

unknownfalsetrue

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

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

falsetrueunknown

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

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 biete SQL Schulungen, Optimierung und Beratung an. Auch der Kauf meines Buches „SQL Performance Explained“ (ab €9,95) unterstützt meine Arbeit an dieser Webseite.

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

trueunknownfalse

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 Aggregatfunktionen every, any und some, sowie die Literale true, false und unknown auch außerhalb von is [not].

BigQuerybDb2 (LUW)abMariaDBbMySQLbOracle DBPostgreSQLbSQL ServerSQLiteabDreiwertige LogikF571: Boolean testtrue, false außerhalb von is
  1. Kein is unknown. Is null kann stattdessen verwendet werden
  2. Nur true und false, nicht unknown

20 Jahre SQL-Evolution kann man nicht an einem Tag nachholen. Abonniere den Newsletter via E-Mail, Twitter oder RSS, um sukzessive aufzuholen und modern-sql.com am Radar zu behalten.

Über den Autor

Foto von Markus Winand

Markus Winand gibt auf modern-sql.com Einblick in SQL und zeigt, wie es von verschiedenen Systemen unterstützt wird. Zuvor machte er use-the-index-luke.com, was er noch immer wartet. Markus kann als Trainer, Sprecher und Berater auf winand.at engagiert werden.

Sein Buch kaufen

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

Die Essenz: SQL-Tuning auf 200 Seiten

Jetzt Kaufen
(Taschenbuch und/oder PDF)

Sein Training

Markus verwandelt veraltetes SQL-92-Wissen in solides und zeitgemäßes SQL-Know-how

Erfahren Sie mehr»

Fußnoten

  1. SQL:2016-2: §8.2, General Rule 1a

  2. SQL:2016-2: §6.39, General Rules 2 and 3

  3. Für die where-Klausel in select: SQL:2016-2: §7.12, General Rule 2; in update SQL:2016-2: §14.14, General Rule 5; in delete SQL:2016-2: §14.9, General Rule 6; in filter SQL:2016-2: §10.9, General Rule 4a und SQL:2016-2: §10.11, General Rule 3a (JSON).

    Für die having-Klausel: SQL:2016-2: §7.14, General Rule 1.

    Für die when-Klausel: in case SQL:2016-2: §6.12, General Rule 2a; in Triggern SQL:2016-2: §15.19, General Rule 4bi2 and 3.

    In match_recongize(…define…) SQL:2016-2: §10.9, General Rule 4a.

    Viele weitere Funktionen sind über where definiert – z. B. die on und using-Klauseln von Joins (SQL:2016-2: §7.10, General Rule 1b and c respektive )

  4. Ein Bruch des Satzes vom ausgeschlossenen Dritten.

    Verwendet man is null anstatt = null liefert die Abfrage natürlich alle Zeilen. Das lässt sich auch so verstehen, das is null ein zweiwertiges Ergebnis liefert (niemals unknown) und die where-Klausel daher alle möglichen Werte abgedeckt sind.

  5. x NOT IN (NULL, …)NOT(x IN(NULL, …))NOT(x = ANY(NULL, …)) (SQL:2016-2: §8.4 Syntax Rules 4 and 5).

    Ein = ANY-Prädikat ist nur false (und damit die Negierung true) wenn alle Vergleiche false sind SQL:2016-2: §8.9 General Rule 2d. Das ist jedoch nicht möglich, wenn es einen Vergleich mit null gibt, weil dieser unweigerliche unknown liefert. Das Ergebnis von x NOT IN (NULL, …) ist daher entweder false (SQL:2016-2: §8.9 General Rule 2c) oder unknown (SQL:2016-2: §8.9 General Rule 2e).

  6. Exists liefert niemals unknown: SQL:2016-2: §8.10 General Rule 2

  7. SQL:2016-2: §4.23.3.4

  8. F571, “Truth value tests”

  9. SQL:2016-2: §6.39, General Rule 3. SQL:2016 hat ein neues Is-Prädikat eingeführt (is json) das sehr wohl unknown liefern kann (SQL:2016-2: §8.22, General Rule 2a).

  10. SQL:2016-2: §4.5.1.

  11. Das Argument, dass es immer eine zweiwertige Entscheidung gibt hält nicht, wenn der Datentyp boolean verwendet wird: Werte dieses Typs können dann auch als Spalte an eine Applikation geliefert werden.

  12. Da das eigentliche Ergebnis des Vergleiches – true oder false – gleichbehandelt wird, fällt die Vergleichsoperation weg.

Mit Markus Winand verbinden

Markus Winand auf LinkedInMarkus Winand auf XINGMarkus Winand auf Twitter
„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 und DSGVO | CC-BY-NC-ND 3.0 Lizenz