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.

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.

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.

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