Antwort.

Hintergrund der Frage

Oft müssen Abfragen formuliert werden, bei denen die Filterkriterien dynamisch festgelegt werden — die Liste der Werte wird vom Benutzer der Schnittstelle festgelegt, und fehlende Werte (NULL) sollten ebenfalls in das Ergebnis einfließen oder nicht. Solch eine Abfrage muss schnell auf großen Datenmengen arbeiten und semantisch korrekt in SQL sein.

Problem

Eine inkorrekte Kombination von Bedingungen IN, NOT IN und NULL-Prüfungen führt zu unerwarteten Ergebnissen, da in SQL der Ausdruck "NULL IN (...)" immer UNKNOWN zurückgibt und "NOT IN" zu einem leeren Ergebnis führen kann, wenn mindestens eines der Elemente in der Liste NULL ist. Die Leistung bei großen Datenmengen kann bei schlecht formulierten Bedingungen stark sinken.

Lösung

Die korrekte Kombination sieht folgendermaßen aus:

Wenn Werte aus der Liste ausgewählt werden, ergänzen wir die Bedingung für NULL, wenn wir sie zurückgeben möchten:

SELECT * FROM sales
WHERE region IN ('Moscow', 'Samara') OR region IS NULL;

Für die Negation verwenden wir eine explizite Logik:

SELECT * FROM sales
WHERE (region NOT IN ('Moscow', 'Samara') OR region IS NULL);

Für die strikte Ausschluss der fehlenden:

SELECT * FROM sales
WHERE region NOT IN ('Moscow', 'Samara') AND region IS NOT NULL;

Bei großen Tabellen sind häufig Indizes von Bedeutung, insbesondere wenn der Filter nach einer Liste eine häufige Operation ist.

Wesentliche Merkmale:

Die Filterung nach NULL muss explizit gesteuert werden, sonst gehen Zeilen verloren.
Die korrekte Verwendung von IN und NOT IN ist entscheidend für die korrekten Abfragen.
Die Indizierung der Felder, die an IN/NOT IN beteiligt sind, beschleunigt den Filter erheblich.

Fangfragen.

Was passiert, wenn man "WHERE field NOT IN ('a', NULL)" prüft?

Das Ergebnis ist immer leer, da jede Zeile mit NULL in der Liste verglichen wird, was UNKNOWN ergibt und alles herausfiltert.

Beispielcode:

SELECT * FROM test WHERE name NOT IN ('Ivan', NULL);

Gibt "IN (NULL,...)" einen Wert für Zeilen mit NULL zurück?

Nein. Der Ausdruck NULL IN (...) gibt immer UNKNOWN zurück, nicht TRUE.

Wie beschleunigt man die Filterung nach einer großen Werteliste?

Indizes verwenden und vorzugsweise einen JOIN mit einer temporären Tabelle machen, wenn die Liste groß ist (Hunderte/tausende von Werten) — das beschleunigt die Filterung im Vergleich zu einem langen IN.

Typische Fehler und Anti-Pattern

Nur IN/NOT IN ohne EXPLIZITE Bedingung für NULL schreiben
Die Besonderheiten der NOT IN-Auswertung mit NULL nicht berücksichtigen
Die Indizierung für das Filterfeld nicht optimieren

Beispiel aus dem Leben

Negativer Fall

Im Analysebericht wurde "WHERE city NOT IN ('London', field_2, NULL)" angewendet. Das Ergebnis — völlig leeres Ergebnis, niemand konnte lange die Ursache für die verlorenen Zeilen verstehen.

Vorteile:

Kurze und "lesbare" Abfrage

Nachteile:

Datenverlust, nicht offensichtliche Logik für Entwickler

Positiver Fall

Im CRM wurde eine Liste von Kundenregionen ausgewählt und eine zusätzliche Schaltfläche "Unvollständige Werte einbeziehen" hinzugefügt. Logik des Filters:

... WHERE city IN ('SPB', 'NNov') OR city IS NULL