MySQL "select ... not in ..."

[SolarCatcher]

Hallo,

ich habe zwei Tabellen, die den gleich Schlüssel "id" verwenden. Ich möchte wissen, ob in Tabelle1 id's zu finden sind, die es nicht in Tabelle2 gibt.

select id from Tabelle1 where id not in (select id from Tabelle2)

hat zwar was gefunden, aber bei weitem nicht alle.

select id from Tabelle1 where id not in (select DISTINCT(id) from Tabelle2)

dagegen scheint alles richtig identifiziert zu haben.

Weiß jemand, warum das einen Unterschied macht und ich unbedingt distinct() verwenden muss? Es leuchtet mir einfach nicht ein.

 

[Kamikaze]

Mir fällt da auch nichts ein aber wie wäre es mit einer Mengenoperation:

select id from tab1 except select id from tab2;

Update:

Du könntest auch mal
select id from Tabelle1 where id not in (select Tabelle2.id from Tabelle2)

ausprobieren. Wäre aber seltsam.

 

[kai_001]

Glaube hat was mit NULL ( mal googlen ) zu tun.

Probiere mal folgendes:

select id from Tabelle1 where id not in (select id from Tabelle2 where id is not null)


Grüße,
Kai

 

[bananenBrot]

Ist dafür nicht gerade ein outer join gedacht?

 

[Tron]

Richtig, dafür ist OUTER JOIN da:

SELECT t1.id
FROM Tabelle1 AS t1
LEFT OUTER JOIN Tabelle2 AS t2 ON t1.id = t2.id
WHERE t2.id IS NULL

 

[Kamikaze]

So wie ich den Query von Tron lese ist das Ergebnis leer.

Es geht darum ids aufzulisten, die nicht in in der 2. Tabelle vorkommen. Dafür ist ein Join nicht gedacht.

Update:
MySQL kann anscheinend kein EXCEPT oder INTERSECT. Lediglich UNION.

Bin ich froh, dass es PostgreSQL gibt.

 

[Tron]

So wie ich den Query von Tron lese ist das Ergebnis leer.

Meine Abfrage ist völlig korrekt. Lies nach, was der Unterschied zwischen einem OUTER JOIN und einem INNER JOIN ist, dann wird klar, warum die Abfrage funktioniert.
Kurzfassung: Wenn es gemäß der ON-Klausel keinen passenden Eintrag für T1 in T2 gibt, dann wird (bei einem LEFT OUTER JOIN) der Eintrag aus T1 zusammen mit einem NULL-Eintrag für T2 im Ergebnis aufgeführt. Genau anhand dieser NULL-Einträge filtert dann die WHERE-Klausel.
Hier ein Beispiel:

sqlite> create table t1 (id integer);
sqlite> create table t2 (id integer);
sqlite> insert into t1 (id) values (1);
sqlite> insert into t1 (id) values (2);
sqlite> insert into t2 (id) values (1);
sqlite> select t1.id from t1 left outer join t2 on t1.id = t2.id where t2.id is null;
2

Es geht darum ids aufzulisten, die nicht in in der 2. Tabelle vorkommen. Dafür ist ein Join nicht gedacht.

Doch, dafür ist ein OUTER JOIN da. Wenn man mehr als nur eine Spalte im Ergebnis sehen will, dann funktioniert die Technik mittels Mengenoperationen auch nicht mehr (zuindest nicht, wenn die weitere Spalte aus T1 nicht auch redundanterweise in T2 vorhanden ist).

Nachtrag: Es geht auch dann mit Mengenoperationen, aber umständlich: SELECT von t1 ohne SELECT von einem INNER JOIN aus t1 und t2. Da nimmt man doch gleich besser den OUTER JOIN, der dafür gedacht ist.

 

[Kamikaze]

t1.id = t2.id

Ich verstehe das immer noch so dass du es damit auf Zeilen einschränkst in denen auch t1.id null ist.

 

[SolarCatcher]

t1.id = t2.id

Ich verstehe das immer noch so dass du es damit auf Zeilen einschränkst in denen auch t1.id null ist.

Das würde ich auch vermuten - allerdings wäre das für mein konkretes Problem egal, weil die ID in meinem Fall immer eine natürliche Zahl sein muss (aber das hängt halt mit meinem konkreten Anwendungsfall zusammen).

Ich werde in den nächsten Tagen einmal den verschiedenen Vorschlägen, die hier gemacht wurden, nachgehen. Nachwievor finde ich ja meinen Ansatz den naheliegendsten. Ich werde mal sehen, ob ich irgendwelche Performance-Unterschiede messen kann (aber vermutlich sind die bei meinen relativ kleinen Tabellen so unbedeutend, dass man keine Aussage machen kann).

Vielen Dank an alle, die hier Vorschläge und Kommentare beigesteuert haben! Ich hatte nicht erwartet, dass es so lebhaft wird

 

[bananenBrot]

Die Abfrage von Tron ist korrekt.

Siehe http://en.wikipedia.org/wiki/Join_(SQL)#Left_outer_join

This means that a left outer join returns all the values from the left table, plus matched values from the right table (or NULL in case of no matching join predicate).

Und da er ein "WHERE t2.id IS NULL" macht, bekommt man nur die Einträge, die in t2 nicht vorkommen.

 

[Kamikaze]

Hmm. Ich verwende in der Regel NATURAL joins da habe ich mir diese Gedanken nie gemacht.

Ich finde immer noch, dass es von hinten durch die Brust ins Auge geht, aber ich sehe ein, dass es funktioniert.

Ich finde die Mengenoperationen immer noch am intuitivsten, aber das wird von MySQL ja nicht unterstützt.

 

[Tron]

t1.id = t2.id

Ich verstehe das immer noch so dass du es damit auf Zeilen einschränkst in denen auch t1.id null ist.

Lies nochmal meine Kurzerklärung plus das Zitat von bananenBrot. Es handelt sich eben um einen OUTER JOIN, nicht um einen INNER JOIN. Eine ON-Klausel ist etwas anderes als eine WHERE-Klausel.

Ein kurzer Blick auf den Aufwand: Indizes vorausgesetzt (d.h. es gibt eine Sortierung nach id) hat die OUTER-JOIN-Methode einen Aufwand von O(n) (parallel über beide Tabellen laufen), die mit Sub-SELECT einen Aufwand von O(n ld n) (über Tabelle eins laufen (n) und testen, ob der jeweilige Eintrag in Tabelle zwei zu finden ist (ld n))

Anbei: NULL = NULL ist falsch, d.h. zwei NULL-Einträge, die verglichen werden, liefern nicht wahr als Ergebnis. Der einzige Test mit NULL, der wahr liefert, ist IS NULL. NULL != NULL ergibt ebenso falsch.