Was bewirkt NULL bei NOT IN?

Hi Leute,

Ich habe hier eine Tabelle mit 2 Spalten:

CREATE TABLE `tabelle` (
`sp1` int(10) unsigned NOT NULL auto_increment,
`sp2` int(10) unsigned default NULL,
PRIMARY KEY USING BTREE (`sp1`),
UNIQUE KEY `Index_vg_einmalig` USING BTREE (`sp2`),
CONSTRAINT `FK_tabelle_sp1` FOREIGN KEY (`sp2`) REFERENCES `tabelle`
(`sp1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

und folgendem Inhalt:

INSERT INTO `tabelle` (`sp1`,`sp2`) VALUES
(1,NULL),
(2,NULL),
(3,NULL),
(7,1),
(6,2),
(4,3),
(5,4),
(8,5),
(9,6),
(10,9);

Ich möchte alle Werte in sp1 ermitteln welche nicht in sp2 vorkommen.

Folgende Abfrage liefert wider Erwarten 0 Zeilen:
SELECT sp1 FROM tabelle t where sp1 not in (select sp2 from tabelle);

wohingegen folgende Abfrage korrekterweise 7 von 10 Zeilen liefert (1-6,9 ->
das Gegenteil
funktioniert also):
SELECT sp1 FROM tabelle t where sp1 in (select sp2 from tabelle);

Erst ein
SELECT sp1 FROM tabelle t where sp1 not in (select sp2 from tabelle where
sp2 is not null);
liefert wie gewünscht die 3 Zeilen (7,8,10).

Was bewirken denn die NULL's in sp2, wodurch 'where sp2 is not null'
notwendig wird?
Server-Version: 5.0.18-nt

Ciao,
Roland

Re: Was bewirkt NULL bei NOT IN?

Roland Aleksa wrote:

> Ich möchte alle Werte in sp1 ermitteln welche nicht in sp2 vorkommen.
>
> Folgende Abfrage liefert wider Erwarten 0 Zeilen:
> SELECT sp1 FROM tabelle t where sp1 not in (select sp2 from tabelle);
>
> wohingegen folgende Abfrage korrekterweise 7 von 10 Zeilen liefert
> (1-6,9 -> das Gegenteil
> funktioniert also):
> SELECT sp1 FROM tabelle t where sp1 in (select sp2 from tabelle);
>
> Erst ein
> SELECT sp1 FROM tabelle t where sp1 not in (select sp2 from tabelle where
> sp2 is not null);
> liefert wie gewünscht die 3 Zeilen (7,8,10).
>
> Was bewirken denn die NULL's in sp2, wodurch 'where sp2 is not null'
> notwendig wird?

Das, was sie bei jedem DBMS bewirken:
Jeder Vergleich mit NULL ergibt UNKNOWN, d.h. es gibt eine 3-Wege-Logik.
TRUE or UNKNOWN gibt TRUE, TRUE AND UNKNOWN gibt UNKNOWN

1 in (1,2,NULL)
-> (1 = 1) OR (1 = 2) OR (1 = NULL)
-> TRUE OR FALSE OR UNKNOWN
-> TRUE

4 not in (1,2,NULL)
-> (4 <> 1) AND (4 <> 2) AND (4 <> NULL)
-> TRUE OR TRUE OR UNKNOWN
-> UNKNOWN

NOT IN ist böse, sobald eine der beiden Spalten NULLs enthält, dann
bekommst du drei Mengen, die mit TRUE, die mit FALSE und die mit UNKNOWN.

Also stellst du entweder sicher, dass in der Subquery keine NULL
zurückkommen, 'where sp2 is not null', oder du definierst die Spalte
gleich mit NOT NULL, dann hat's auch das DBMS einfacher.

Wenn du korrelierte Subqueries mit EXISTS/NOT EXISTS verwendest, gibt es
dieses Problem übrigens nicht, da das eine 2-Wege-Logik ist und damit
sind die EXISTS-Menge und die NOT EXISTS-Menge wirklich die Gesamtmenge.

Das gleiche Problem tritt übrigens bei jedem NOT in Verbindung mit AND auf.

Dieter