In SQL ist nichts gleich (=
) null
– nicht einmal null
selbst. Entsprechend der dreiwertigen Logik von SQL ist das Ergebnis von null = null
nicht true (wahr), sondern unknown (unbekannt). Um zu prüfen, ob ein Wert null
ist, hat SQL das is [not] null
-Prädikat.
Mit is [not] distinct from
bietet SQL auch einen allgemeinen Vergleichsoperator an, für den zwei null
-Werte gleich sind.
<Ausdruck> IS NOT DISTINCT FROM <Ausdruck>
Beachte, dass man die negierte Form mit not
verwenden muss, um dieselbe Logik wie das Gleichheitszeichen (=
) zu erhalten.
Die folgende Wahrheitstabelle hebt die Unterschiede zwischen dem Gleichheitszeichen (=
) und is not distinct from
hervor.
A | B | A = B | A IS NOT DISTINCT FROM B |
---|---|---|---|
0 | 0 | true | true |
0 | 1 | false | false |
0 | null | unknown | false |
null | null | unknown | true |
Das Ergebnis beim Gleichheitszeichen (=
) ist unknown, sobald ein Operand null
ist. In diesem Fall ist ein Vergleich mit is not distinct from
ist true, wenn beide Operanden null
sind oder false, wenn nur einer null
ist.
Konforme Alternativen
Aufgrund der dreiwertigen Logik von SQL ist ein gleichwertiger Ersatz für A is not distinct from B
, der in allen Datenbanken funktioniert, überraschend komplex – selbst wenn man die Anforderung auf Fälle einschränkt, bei denen die Evaluierung der Ausdrücke A und B deterministisch ist und keine Nebenwirkungen hat.0
CASE WHEN (a = b) or (a IS NULL AND b IS NULL)
THEN 0
ELSE 1
END = 0
Das Ergebnis des Ausdruckes in der when
-Klausel ist true, wenn beide Argumente gleich sind oder beide null
sind. Wenn jedoch nur ein Argument null
ist, ist das Ergebnis unknown. Oft ist das kein Problem, da SQL unknown in der Regel wie false behandelt, wenn eine binäre Entscheidung getroffen wird – z. B. eine Zeile aufgrund der where
-Klausel akzeptiert oder abgelehnt wird.
Um das exakt gleiche Ergebnis wie is not distinct from
zu erhalten – also entweder true oder false aber nie unknown – reduziert der case
-Ausdruck das dreiwertige Ergebnis in ein Zweiwertiges. Alternativ kann man dafür auch is not false
verwenden – das wird jedoch nicht von allen Datenbanken unterstützt.
Hinweis in eigener Sache
Ich lebe von SQL-Schulungen, anderen SQL-Dienstleistungen und dem Verkauf meines Buches. Mehr dazu auf winand.at.
Eine andere Möglichkeit ist es, SQL-Mengenoperationen zu verwenden, da diese intern Distinct-Vergleiche verwenden. Der folgende Auszug verwendet intersect
zur Ermittlung der gemeinsamen Teilmenge. Die beiden Mengen sind dabei jeweils nur ein Wert (eine Zeile mit einer Spalte). Wenn es zweimal derselbe Wert ist, erhält man diesen Wert als Ergebnis. Andernfalls ist das Ergebnis leer. Mit exists
kann man in der where
-Klausel prüfen, ob das Ergebnis leer ist:1
EXISTS (VALUES (A)
INTERSECT
VALUES (B)
)
Der Vorteil ist, dass keine Ausdrücke wiederholt werden. Aufgrund der values
-Klausel funktioniert diese Methode aber nicht bei allen Datenbanken. Mit einem select
von einer einzeiligen Dummy-Tabelle kann man jedoch eine konforme und weitgehend unterstützte Variante machen.
Kompatibilität
Das is [not] distinct from
-Prädikat wurde in zwei Schritten eingeführt: mit SQL:1999 kam das Feature T151, “DISTINCT predicate”. Die optionale Negierung mit not
wurde jedoch erst mit SQL:2003 als Feature T152, “DISTINCT predicate with negation”, eingeführt.
- In 11.1 mit Mod Pack 1/Fix Pack 1 verfügbar
Proprietäre Alternativen
Die meisten Datenbanken, die is not distinct from
nicht unterstützen, bieten eine proprietäre Alternative an, die einfacher als die oben beschriebene konforme Alternative ist. Die folgenden proprietären Funktionen sind jeweils ein vollwertiger Ersatz für is not distinct from
– d. h. das Ergebnis ist niemals unknown.
Exists
, select
ohne from
, intersect
Die Standard-SQL-Lösung mit exists
, values
und intersect
kann man mit einer leichten Änderung für andere Datenbanken anpassen – z. B. indem man anstatt der values
-Klausel select
ohne from
nutzt:
EXISTS (SELECT c1
INTERSECT
SELECT c2
)
decode — Db2, Oracle, H2
Db2, die Oracle Datenbank, und H2 bieten die proprietäre Funktion decode
an. Diese verwendet intern die Semantik von is not distinct from
.2 Das folgende Beispiel hat denselben Effekt wie A is not distinct from B
:
DECODE(A, B, 0, 1) = 0
is
— SQLite, H2
Der is
-Operator von SQLite (Documentation) und H2 (Documentation) kann auch zwei Ausdrücke vergleichen (also nicht nur is [not] null
) und wendet dabei dieselben Regeln wie is not distinct from
an.
<=>
— MySQL, MariaDB
MySQL bietet den proprietären Vergleichsoperator <=>
an, der wie is not distinct from
funktioniert.3
ANSI_NULLS
— SQL Server
SQL Server’s überholte Einstellung „ANSI_NULLS“ kann genutzt werden, damit sich manche Gleichheitszeichen (=
) wie is not distinct from
verhalten.
Warnung
ANSI_NULLS OFF
ist überholt (deprecated): In künftigen SQL Server-Versionen kann diese Einstellung Fehler verursachen.
Beachte auch, dass diese Einstellung nicht alle Gleichheitszeichen (=) betrifft: Nur Vergleiche, die auf einer Seite eine Variable oder das null
-Literal haben, sind betroffen. Ein allgemeiner Vergleich <Ausdruck> = <Ausdruck>
ist nicht betroffen.