NOT IN, NOT EXISTS und NULL: Fehler in SQL Server vermeiden

Ein Bericht sucht Kunden ohne Bestellung. Monate lang funktioniert er, dann liefert er plötzlich nichts mehr, obwohl weiterhin viele Kunden inaktiv sind. Der SQL-Text wurde nicht geändert. Eine einzige importierte Bestellung ohne Kundenreferenz kann ausreichen, wenn die Abfrage NOT IN verwendet.

Vergleiche mit NULL ergeben in SQL neben wahr und falsch auch unbekannt. WHERE behält nur Zeilen, deren Bedingung wahr ist. Unbekannt wird nicht als wahr behandelt. Diese Regel ist hilfreicher als die pauschale Aussage, IN müsse grundsätzlich ersetzt werden.

Das Ergebnis nachvollziehen

Die erste Abfrage liefert keine Kunden. NOT EXISTS liefert 2 und 3. Der Left Join liefert ebenfalls 2 und 3, weil die geprüfte Bestellspalte bei einer echten Übereinstimmung nicht NULL sein darf. Die letzte Abfrage zählt drei Bestellungen, aber nur zwei vorhandene Kundenreferenzen.

DECLARE @Customers TABLE(CustomerId int NOT NULL PRIMARY KEY);
DECLARE @Orders TABLE(OrderId int NOT NULL, CustomerId int NULL);
INSERT @Customers VALUES (1),(2),(3);
INSERT @Orders VALUES (10,1),(11,NULL),(12,1);

SELECT c.CustomerId
FROM @Customers AS c
WHERE c.CustomerId NOT IN
    (SELECT o.CustomerId FROM @Orders AS o);

SELECT c.CustomerId
FROM @Customers AS c
WHERE NOT EXISTS
(
    SELECT 1 FROM @Orders AS o
    WHERE o.CustomerId=c.CustomerId
);

SELECT c.CustomerId
FROM @Customers AS c
LEFT JOIN @Orders AS o ON o.CustomerId=c.CustomerId
WHERE o.OrderId IS NULL;

SELECT COUNT(*) AS AllOrders,
       COUNT(CustomerId) AS OrdersWithCustomer
FROM @Orders;

Für Kunde 2 muss NOT IN den Ausschluss gegenüber sämtlichen Unterabfragewerten erfüllen. Der Vergleich mit 1 ergibt wahr, die Ungleichheit gegenüber NULL bleibt unbekannt. Die gesamte Bedingung wird nicht wahr. Kunde 1 scheidet wegen seines tatsächlichen Treffers aus; insgesamt bleibt keine Zeile übrig.

NULL innerhalb der Unterabfrage herauszufiltern kann NOT IN für diesen nicht nullable Kundenschlüssel korrigieren. Das ist zulässig, wenn die Nullregeln Teil eines dokumentierten Vertrags sind. Wird später auch der äußere Ausdruck nullable, muss die Bedeutung erneut geprüft werden. NOT EXISTS drückt die fachliche Forderung nach fehlenden Treffern direkter aus.

Die gewünschte Beziehung formulieren

NOT EXISTS prüft, ob irgendeine passende Zeile existiert. Mehrere Bestellungen für Kunde 1 vervielfachen Kunde 2 deshalb nicht. NULL wird dadurch aber nicht gleich NULL. Sollten zwei fehlende Schlüssel fachlich als Übereinstimmung gelten, bildet ein einfacher Gleichheitsvergleich in der korrelierten Unterabfrage diese Sonderregel nicht ab.

Beim Left Join muss die geprüfte rechte Spalte eine fehlende Verbindung eindeutig anzeigen. Eine nullable Lieferdatumsspalte kann auch bei einer echten Bestellung NULL sein. Im Beispiel ist OrderId ausdrücklich NOT NULL; nur die vom Join erzeugte Null-Erweiterung kennzeichnet einen fehlenden Treffer.

Auch die Position weiterer Bedingungen zählt. Für Kunden ohne bezahlte Bestellung gehört die Zahlungsbedingung in NOT EXISTS beziehungsweise in ON des Left Join. Ein Filter auf den rechten Zahlungsstatus im äußeren WHERE kann genau jene null-erweiterten Zeilen entfernen, die gesucht werden. Prüfen Sie bezahlte, unbezahlte und vollständig fehlende Bestellungen in einem kleinen Datensatz.

Erst Semantik, dann Ausführungsplan

Bei großen Tabellen kann ein Index mit Orders.CustomerId als führender Spalte die Existenzprüfung unterstützen. Kommt ein Zahlungsstatus hinzu, entscheiden Verteilung und Zugriffsmuster über einen gefilterten oder zusammengesetzten Index. Unterschiedliche Schreibweisen können zu ähnlichen Anti-Semi-Join-Plänen führen. Die Syntax allein beweist keinen Geschwindigkeitsvorteil.

EXCEPT besitzt Mengen-Semantik, entfernt Duplikate und behandelt NULL für den Vergleich unterschiedlicher Werte als gleich. Es ist deshalb kein beliebiger Ersatz, wenn Mehrfachvorkommen oder NULL-Beziehungen zum Ergebnisvertrag gehören. DISTINCT nach einem versehentlichen Viele-zu-viele-Join kann ebenfalls einen Modellierungsfehler verstecken und zusätzliche Arbeit erzeugen.

Ein sinnvoller Test enthält eine leere rechte Seite, einen Treffer, doppelte Treffer, NULL rechts und gegebenenfalls einen nullable linken Schlüssel. Vergleichen Sie konkrete Ergebniszeilen statt nur deren Anzahl. Verschiedene falsche Ergebnisse können dieselbe Zeilenzahl besitzen.

Untersuchen Sie außerdem, weshalb die fehlende Kundenreferenz überhaupt importiert werden konnte. Die korrigierte Abfrage macht unvollständige Quelldaten nicht vollständig. Ein zuverlässiger Anti-Join legt die fachliche Abwesenheitsregel offen, behandelt NULL bewusst und wird erst anschließend hinsichtlich Indizes optimiert.

Technische Referenzen: Microsoft Learn: IN · Microsoft Learn: EXISTS.