Wieder mal ein ganz normaler Wahnsinn, der einen Stunden gekostet hat…
Wenn wir uns die nachfolgenden SQL Statements ansehen, dann würde ich davon ausgehen, dass die ersten 6 eine Ausgabe erzeugen.
SELECT 'Test 1' WHERE 1 IN (NULL, 1, 2, 3, 4)
SELECT 'Test 2' WHERE 1 IN (1, 2, 3, 4)
SELECT 'Test 3' WHERE 1 NOT IN (NULL, 2, 3, 4) -- !!!
SELECT 'Test 4' WHERE 1 NOT IN (2, 3, 4)
SELECT 'Test 5' WHERE NOT (1 IN (NULL, 2, 3, 4)) -- !!!
SELECT 'Test 6' WHERE NOT (1 IN (2, 3, 4))
-- Empty as expected
SELECT 'Test 5' WHERE 1 IN (NULL, 2, 3, 4)
SELECT 'Test 6' WHERE 1 IN (2, 3, 4)
Aber ❗ Pustekuchen ❗
Die Zeilen 3 und 5 erzeugen keine Ausgabe ❗Dadurch, dass in dem Ausdruck in der Klammer NULL enthalten ist funktioniert der NOT IN Test nicht mehr korrekt.
Ich bin darauf gestoßen, weil ich ein Subquery durchgeführthabe und mit NOT IN prüfen wollte, dass ein bestimmter Wert eben nicht in diesem Subquery enthalten ist. Einziges Problem war, dass in einigen Fällen dieser Subquery eben auch NULL als Ergebnis geliefert hat.
Und genau in diesem Fall funktionierte die Abfrage nicht korrekt (s.o.). Das sah vereinfacht in etwas so aus:
SELECT [ID] FROM [Table1]
WHERE [SomeData] NOT IN
(SELECT [OtherIDWithNULL] FROM [Table2])
Ich habe den Subquery dann entsprechende um ein WHERE … IS NOT NULL erweitert und siehe da es ging:
SELECT [ID] FROM [Table1]
WHERE [SomeData] NOT IN
(SELECT [OtherIDWithNULL] FROM [Table2]
WHERE [OtherIDWithNULL] IS NOT NULL)
Rein gefühlt ist das für mich ein Bug, aber es ist keiner ❗
Hier kommt eine Einstellung zum tragen, die sich ANSI_NULL schimpft. (siehe SET ANSI_NULL { ON | OFF })
ANSI_NULL ist im Allgemeinen ON, und das bedeutet, dass ein Vergleich eines Wertes mit NULL immer undefiniert ist. Das ein IN-Statement aber nichts anderes ist als ein Vergleich der einzelnen Werte in der Klammer mit dem Zielwert, führt dies hier zu einem irritierenden Ergebnis. NULL=NULL und 1=NULL ist eben undefiniert und nicht False wenn ANSI_NULL ON ist! Also können die Zeilen 3+5 kein korrektes Ergebnis liefern.
Man beachte: Dies hat keinen Einfluß solange man IN verwendet und nicht NOT IN!
Meine rein persönliche Meinung:
Ich habe selten solch einen Unsinn in einem ANSI-Standard gesehen. Einfach Unlogisch ❗
Für mich ist NULL=NULL und NULL<>AnyThingElse eben True… Just my 2 cents…
Jetzt könnte man meinen, dass es also auch eine Lösung wäre SET_ANSI_NULL OFF zu verwenden ❗ (BTW: Eine Einstellung die auch als Attribut direkt auf der Datenbank selbst gesetzt werden kann). Aber der folgende Hinweis in der Doku sollte einen zum Umdenken bewegen:
Wichtig:
In einer späteren Version von SQL Server wird ANSI_NULLS immer auf ON festgelegt, und jede Anwendung, die für die Option explizit OFF festlegt, löst einen Fehler aus. Verwenden Sie dieses Feature beim Entwickeln neuer Anwendungen nicht, und planen Sie eine Änderung von Anwendungen, in denen es zurzeit verwendet wird.
Da dies also evtl. Einfluss auf andere Codebereiche haben könnte und zukünftig nicht mehr unterstützt wird habe ich auf diese Nutzung verzichtet und lieber das Subquery abgeändert.
PS: Bitte jetzt nicht darauf hinweisen, dass es auch JOIN gibt. Aufgrund der Komplexität der Abfrage und weil diese auch noch nach bestimmten Bedingungen gebaut wurde, war ein IN mit einem Subquery, der einfachere Weg.
