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.
Das Problem kenn ich sehr gut. Viel einfacher gehts aber mit COALESCE
SELECT ‚Test‘ WHERE COALESCE(NULL, 1) NOT IN (2, 3, 4)
würde funktionieren. ISNULL ist meistens DB spezifisch. COALESCE ist ANSI SQL und funktioniert überall. (Getestet unter MS SQL Server, Oracle, MySQL, PostgreSQL)
Mein Problem ist nicht NULL auf der linken Seite vom NOT IN. Das Problem ist das NULL in dem Subquery, also NULL auf der rechten Seite!
Und hier kann ich COALESCE schwer verwenden, bzw, es würde den Subquery extrem verlangsamen.
Warum? Nimmst du Links COALESCE kannst du Rechts NULL weglassen.
Du hast das Problem nicht verstanden.
Im rechten Teil (Subquery) ist nun mal ein NULL drin! Die Lösung ist es ja gerade dieses weg zu bekommen.
Im rechten Teil vom IN irgendwas zu verändern nützt gar nichts.
Der folgende Code gibt auch nichts aus, warum auch! Er löst das Problem nicht. Im rechten Ausdruck von IN darf eben kein NULL stehen!
SELECT ‚Test 3‘ WHERE COALESCE(NULL,1) NOT IN (NULL, 2, 3, 4) — !!!
SELECT ‚Test 5‘ WHERE NOT (COALESCE(NULL,1) IN (NULL, 2, 3, 4)) — !!!
Wo ist dein Problem? Du mußt doch nur NULL aus dem IN rausnehmen.
SELECT ‚Test 3‘ WHERE COALESCE(NULL,-1) NOT IN (-1, 2, 3, 4) — !!!
oder
SELECT ‚Test 3‘ WHERE COALESCE(NULL,-1) NOT IN (2, 3, 4) — !!!
Je nachdem was du erreichen willst mußt du halt COALESCE an
der richtigen Stelle verwenden. NULL darf nie in einm IN stehen.
SELECT [ID] FROM [Table1]
WHERE [SomeData] NOT IN
(SELECT [OtherIDWithNULL] FROM [Table2])
Wenn du die Einträge von Table1 haben willst wenn
Table2.OtherIDWithNull NULL ist, dann mußt du nur
SELECT [ID] FROM [Table1]
WHERE [SomeData] NOT IN
(SELECT COALESCE([OtherIDWithNULL], 1) FROM [Table2])
Du mußt natürlich schauen daß der Wert 1 oder welchen du wählst
in Table1 enthalten ist.
Ich würde folgendes machen:
SELECT [ID] FROM [Table1] a
LEFT OUTER JOIN [Table2] b ON
a.[SomeData] = b.[OtherIDWithNULL]
WHERE b.[OtherIDWithNULL] IS NULL
@Samuel: Du hast meinen PS nicht gelesen! Ich weiß auch dass es einen JOIN gibt. Aufgrund der Komplexität des Statements und der Verständlichkeit halber wurde aber eben zu dem IN Konstrukt gegriffen. Zudem spricht auch gegen den JOIN, das IDs in Table2 mehrfach vorkommen können… (aber das hatte ich nicht geschrieben und wäre nur weiterer Diskussionsbalast).
@Marc: Es ist mit Sicherheit einfacher in der WHERE CLAUSE mit IS NOT NULL zu verwenden, als ein COALECE bei dem man sich auch noch Gedanken mach muss was man zurückgibt 😉
Ich denke wir haben beide das Problem verstanden und können die Diskussion beenden.
Gib es zu, das PS hast du noch schnell reingemogelt. 😉
Joins sind nach meinem Wissen schneller als Unterabfragen. Allerdings sind Unterabfragen intuitiver.
[Zitat]Zudem spricht auch gegen den JOIN, das IDs in Table2 mehrfach vorkommen können…[/Zitat]
Würde in dem Fall kein Problem sein, da nur die IDs von Table1 übernommen werden, für die es kein Pendant in Table2 gibt.
@Samuel:
1. Nööö. Ich habe nichts reingemogelt. Ich blogge „ehrlich“ Nachträge werden mit Datum eingefügt. 😉
2. Upps. Da habe ich mich selbst reingelegt mit meiner Argumentation 1:0 für Dich!
Hi und vielen Dank für diesen Eintrag. Das hat mir jede Menge Ärger erspart. Top! 🙂