NOT IN und NULL

NOT IN ist das Gegenteil von IN: Es behält die Zeilen, deren Wert nicht in der Liste steht. Das sind die Spiele, die noch nie jemand bestellt hat:

SELECT titel
FROM spiele
WHERE id NOT IN (SELECT spiel_id FROM positionen)
ORDER BY titel;

Du bekämst dasselbe Ergebnis mit LEFT JOIN … IS NULL aus der Lektion über Joins, aber diese Abfrage liest sich fast wie die Frage selbst.

Jetzt eine Falle, in die sogar erfahrene Programmierer tappen. Welche Kunden haben den Shop niemandem empfohlen? Die Spalte geworben_von enthält die ID des Kunden, der den Shop empfohlen hat, die Kunden, die niemanden geworben haben, sind also die, deren id nicht in dieser Spalte steht:

SELECT name
FROM kunden
WHERE id NOT IN (SELECT geworben_von FROM kunden)
ORDER BY name;

Das Ergebnis ist leer, obwohl zum Beispiel Hannes Schulz den Shop offensichtlich niemandem empfohlen hat. Der Grund ist die Liste. Viele Kunden sind von allein gekommen, ihr geworben_von ist also NULL, und die Unterabfrage liefert NULL, 1, 1, NULL, 3, 2, NULL und so weiter.

id NOT IN (1, 3, NULL) bedeutet „id ist nicht 1 und nicht 3 und nicht NULL“. Und wie du aus Lektion 3 weißt, ist ein Vergleich mit NULL nie wahr: NULL ist ein unbekannter Wert, und ob 12 sich von einem unbekannten Wert unterscheidet, ist ebenfalls unbekannt. Die Bedingung ist also für keinen Kunden wahr:

SELECT 12 NOT IN (1, 3) AS ohne_null,
       12 NOT IN (1, 3, NULL) AS mit_null;

1 bedeutet wahr. NULL bedeutet „unbekannt“, und WHERE behält nur die Zeilen, für die die Bedingung wahr ist. Ein NULL in der Liste, und NOT IN findet nichts. (IN hat dieses Problem nicht: Ein Wert, der in der Liste steht, wird gefunden, was sonst noch darin steht.)

Die Lösung ist, NULL aus der Liste herauszuhalten: Füge der Unterabfrage WHERE geworben_von IS NOT NULL hinzu. Oder benutze NOT EXISTS, das du in der nächsten Übung lernst und das diese Falle nicht hat.

Typischer Fehler: NULL vergessen. Wann immer du NOT IN mit einer Unterabfrage schreibst, frage dich, ob die Spalte der Unterabfrage NULL sein kann. Ein Primärschlüssel wie id ist es nie; eine Spalte wie geworben_von kann es sein. Und du bekommst keine Fehlermeldung: Die Abfrage findet einfach stillschweigend nichts.