NOT IN und NULLs

Heute Morgen hat mich ein Kollege nach dem berüchtigten Problem von NULL-Werten in NOT IN Vergleichen gefragt und eigentlich wollte ich ihn auf meinen Blog verweisen, musste dann aber feststellen, dass dort kein entsprechendes Beispiel zu finden ist. Dem soll hiermit abgeholfen werden:

Angelegt werden zwei Tabellen mit jeweils 10 Sätzen. Die erste enthält die Werte 1 bis 10, die zweite die Werte 2 bis 10 und an Stelle der 1 einen NULL-Wert. Demnach enthält die Schnittmenge beider Mengen also die Werte 2 bis 10. Nur ein Wert weicht ab.

-- Anlage der Test-Tabellen
drop table t1;
drop table t2;

create table t1
as
select rownum id
  from dual
connect by level <= 10;

create table t2
as
select rownum id
  from dual
connect by level <= 10;

update t2 
   set id = NULL
 where id = 1;   

-- Queries zur Bestimmung der Sätze, aus t1, die in t2 nicht erscheinen
-- NOT IN
select * 
  from t1 
 where t1.id not in (select t2.id 
                       from t2);

Es wurden keine Zeilen ausgewählt

-- NOT EXISTS
select * 
  from t1
 where not exists (select NULL
                     from t2
                    where t2.id = t1.id);

        ID
----------
         1

-- MINUS
select id from t1
minus
select id from t2;

        ID
----------
         1

Demnach liefern MINUS und NOT EXISTS das erwartete Ergebnis - den Wert 1 -, aber NOT IN liefert kein Ergebnis. Verantwortlich dafür ist die logische Behandlung von NULL-Werten in Oracle: NOT IN (NULL) ergibt weder TRUE noch FALSE, sondern unbekannt. Bei AskTom gibt's eine kurze Erklärung zum Thema (dunkel erinnere ich mich, vom Herrn Kyte auch noch ausführlichere Aussagen dazu gelesen zu haben, aber möglicherweise war das in einem seiner Bücher).

Nachtrag 06.02.2013: eine konzise Erklärung des Verhaltens hat gelegentlich Jonathan Lewis gegeben: colx NOT IN (value1, value2, value3) ist logisch äquivalent zu colx != value1 AND colx != value2 AND colx != value3. Wenn einer der Vergleiche NULL liefert ist auch das Gesamtergebnis NULL. Im Fall eines IN ergibt sich eine Verknüpfung von OR-Prädikaten: wenn eines davon NULL wird, bleibt das Gesamtergebnis davon unbeeinflusst.