NULL-sichere Vergleiche: is [not] distinct from


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.

ABA = BA IS NOT DISTINCT FROM B
00truetrue
01falsefalse
0nullunknownfalse
nullnullunknowntrue

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:

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

Wenn Dir dieser Artikel gefällt, könnte mein Buch SQL Performance Explained oder mein Training auch etwas für Dich sein.

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:0

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.

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

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.

Über den Autor

Foto von Markus Winand

Markus Winand lehrt effizientes SQL – inhouse und online. Er minimiert die Entwicklungszeit durch modernes SQL und optimiert die Laufzeit durch schlaue Indizierung – dazu hat er auch das Buch SQL Performance Explained veröffentlicht.

Sein Buch bei Amazon kaufen

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

Die Essenz: SQL-Tuning auf 200 Seiten

Bei Amazon kaufen
(Taschenbuch)

Taschenbuch und PDF auch auf Markus' Webseite erhältlich.

Sein Training

Sein beliebtes Training stimmt Entwickler auf SQL Performance ein.

Erfahren Sie mehr»

Fußnoten

1

Dokumentation für Db2 z/OS, Db2 LUW, Oracle, und H2.

2

Der <=>-Operator wird oft als „Spaceship“-Operator bezeichnet. Der „Spaceship“-Operators, wie er von anderen Sprachen angeboten wird (PHP, Perl, Ruby), dient einem anderen Zweck. Siehe “Three-way comparison” in der englischen Wikipedia.

„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 | CC-BY-NC-ND 3.0 Lizenz