SQl problem mit not in + subselect
Konrad Hammerer
Ich habe folgendes Problem bei einer SQL Abfrage mit Access:
Ich habe eine Tabelle "1" und eine Tabelle "2". In der Tabelle 1 gibt es
eine Spalte "id (zahl)", die die Werte 1, 2, 3 und 4 enthält. In der
Tabelle 2 gibt es ebenfalls eine Spalte "id (zahl)", die die Werte 1, 2
und 3 enthält.
Nun setzte ich folgende Abfrage ab:
select id from 1 where id not in (select id from 2);
Ergebnis: Ein Eintrag -> 4 (passt also)
Wenn jetzt aber die die Tabelle "2" zusätzlich zu 1, 2 und 3 noch einen
4. Eintrag enthält, der leer ist, dann liefert die Abfrage kein Ergebnis
mehr!
Ich habe das mit vielen Beispieldaten probiert und bin zu dem Ergebnis
gekommen, dass wann immer die 2. Tabelle einen leeren Eintrag enthält,
der "NOT IN + Subselect"-Befehl nicht mehr geht!
Kann das sein bzw. was passiert hier?
Danke,
Konrad
Peter Doering
Konrad Hammerer wrote:
> Ich habe eine Tabelle "1" und eine Tabelle "2". In der Tabelle 1 gibt es
> eine Spalte "id (zahl)", die die Werte 1, 2, 3 und 4 enthält. In der
> Tabelle 2 gibt es ebenfalls eine Spalte "id (zahl)", die die Werte 1, 2
> und 3 enthält.
>
> Nun setzte ich folgende Abfrage ab:
> select id from 1 where id not in (select id from 2);
>
> Ergebnis: Ein Eintrag -> 4 (passt also)
>
> Wenn jetzt aber die die Tabelle "2" zusätzlich zu 1, 2 und 3 noch einen
> 4. Eintrag enthält, der leer ist, dann liefert die Abfrage kein Ergebnis
> mehr!
>
> Ich habe das mit vielen Beispieldaten probiert und bin zu dem Ergebnis
> gekommen, dass wann immer die 2. Tabelle einen leeren Eintrag enthält,
> der "NOT IN + Subselect"-Befehl nicht mehr geht!
Probier mal so:
select id from 1 where id not in (select id from 2 WHERE 2.id=1.id )
Gruss - Peter
--
Ich beantworte keine Fragen per Email.
Mitglied im http://www.dbdev.org
FAQ: http://www.donkarl.com
Josef Poetzl
Konrad Hammerer schrieb:
[select id from 1 where id not in (select id from 2);]
>> Probier mal so:
>> select id from 1 where id not in (select id from 2 WHERE 2.id=1.id )
>
> Funktioniert! Kannst du mir das mal genauer erklären und insbesondere,
> warum mein Versuch scheitert?!?
Das hängt mit dem NULL-Vergleich zusammen.
Ich wollte die Ausführung deines Beispiel mit dem Jet-Showplan zeigen,
funktioniert leider nicht, da der IN(select..)-Ausdruck nicht
ausgewertet wird.
Jet-Showplan für:
SELECT id FROM T1 WHERE id not in (select idT1 from T2)
| NOTE: Currently does not handle subqueries, vt parameters, and subqueries
| NOTE: You may see ERROR messages in these cases
|
| - Inputs to Query -
| Table 'T2'
| Table 'T1'
| - End inputs to Query -
|
| 01) Restrict rows of table T1
| by scanning
| testing expression ""
| ERROR: unexpected empty expression
daher hier der Ausführungsplan des gleichen Beispiels aus dem
SQL-Server: (das Abfrage-Ergebnis ist das gleiche wie unter Jet)
| |--Nested Loops(Left Anti Semi Join,
| WHERE:([tempdb].[dbo].[#T2].[idT1] IS NULL OR
| [tempdb].[dbo].[#T1].[id]=[tempdb].[dbo].[#T2].[idT1]))
| |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#T1]))
| |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#T2]))
|
Wenn nun #T2.idT1 NULL ist, dann ist doch der Where-Ausdruck Wahr und
damit wird der "Anti Semi Join" false. Ist das von mir richtig
interpretiert?
(Ich muss gestehen, dass ich so Abfrage-Ergebnis nicht erwartet
hätte.)
Die gewünschte Lösung würde das bringen:
SELECT id FROM #T1 WHERE id not in (select idT1 from #T2 where idT1 IS NOT NULL)
=>
| |--Nested Loops(Left Anti Semi Join, OUTER REFERENCES:([tempdb].[dbo].[#T1].[id]))
| |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#T1]))
| |--Top(TOP EXPRESSION:((1)))
| |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#T2]),
| WHERE:
| ([tempdb].[dbo].[#T1].[id]=[tempdb].[dbo].[#T2].[idT1]
| AND [tempdb].[dbo].[#T2].[idT1] IS NOT NULL))
oder die in Jet durchaus übliche Variante:
SELECT
T1.id
FROM
T1
LEFT JOIN
T2
ON T2.idT1 = T1.id
WHERE
T2.idT2 is null
=> Jet-Showplan:
01) Outer Join table 'T1' to table 'T2'
using index 'T2!IX_idT1'
join expression "T1.id=T2.idT1"
02) Restrict rows of result of 01)
by scanning
testing expression "T2.idT2 Is Null"
auch diese Variante würde das gewünschte Ergebnis liefern:
SELECT T1.id
FROM T1
where not exists (select * from T2 where idT1 = T1.id)
=> Jet-Showplan:
01) Restrict rows of table T1
by scanning
testing expression "Not "
Was ich übrigens interessant fand:
SELECT id
FROM #T1
WHERE id not in (1, 2, NULL)
liefert beim SQL-Server das gleiche Ergebnis wie "not in (select ...)"
unter Jet wird in diesem Fall aber das "richtige" (gewünschte)
Ergebnis geliefert und NULL anscheinend wie eine Wert betrachtet.
mfg
Josef
--
EPT: (Access Error Prevention Table) http://access.joposol.com/
FAQ: (Access-FAQ von Karl Donaubauer) http://www.donkarl.com/
Konrad Hammerer
Danke für die sehr ausführliche Antwort. Ich hätte das so auf keinen
Fall erwartet aber immerhin gibt es nun eine Erklärung für das
Verhalten. Wenn man es weiß, ist es ok. Aber wenn nicht, sucht man sich
dumm und dämlich ;-)
Grüße,
Konrad
Josef Poetzl schrieb:
Josef Poetzl
Konrad Hammerer schrieb:
> Danke für die sehr ausführliche Antwort. Ich hätte das so auf keinen
> Fall erwartet aber immerhin gibt es nun eine Erklärung für das
> Verhalten.
Wobei ich meine Interpretation noch nicht als passende Erklärung
betrachten würde. Diese stufe ich derzeit noch als Vermutung ein. ;-)
>> daher hier der Ausführungsplan des gleichen Beispiels aus dem
>> SQL-Server: (das Abfrage-Ergebnis ist das gleiche wie unter Jet)
>>| |--Nested Loops(Left Anti Semi Join,
>>| WHERE:([tempdb].[dbo].[#T2].[idT1] IS NULL OR
>>| [tempdb].[dbo].[#T1].[id]=[tempdb].[dbo].[#T2].[idT1]))
>>| |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#T1]))
>>| |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#T2]))
>>|
>>
>> Wenn nun #T2.idT1 NULL ist, dann ist doch der Where-Ausdruck Wahr und
>> damit wird der "Anti Semi Join" false. Ist das von mir richtig
>> interpretiert?
mfg
Siegfried Schmidt
> Wobei ich meine Interpretation noch nicht als passende Erklärung
> betrachten würde. Diese stufe ich derzeit noch als Vermutung ein. ;-)
Die Null dürfte als generelle Ursache schon stimmen, die Erklärung über IN
als Verknüpfung boolscher Operationen ist aber nicht so leicht.
'select true or null, true and null' liefert nämlich true/null
Siegfried
--
http://www.schmidt.ath.cx
Josef Poetzl
Siegfried Schmidt schrieb:
>> Wobei ich meine Interpretation noch nicht als passende Erklärung
>> betrachten würde. Diese stufe ich derzeit noch als Vermutung ein. ;-)
>
> Die Null dürfte als generelle Ursache schon stimmen, die Erklärung über IN
> als Verknüpfung boolscher Operationen ist aber nicht so leicht.
>
> 'select true or null, true and null' liefert nämlich true/null
ich bezog mich auf
>>| |--Nested Loops(Left Anti Semi Join,
>>| WHERE:([tempdb].[dbo].[#T2].[idT1] IS NULL OR
>>| [tempdb].[dbo].[#T1].[id]=[tempdb].[dbo].[#T2].[idT1]))
Das where liefert True, da:
NULL IS NULL OR NULL => True
Die frage ist nun:
entspricht "*anti* semi join" einem "negierten" Join also:
Not (NULL IS NULL OR NULL) oder (NULL IS NULL OR [wert] = NULL)
uns somit immer: NOT (True) ?
Das würde zwar erklären, warum keine DS geliefert werden, aber ob das
die richtige Erklärung ist, weiß ich nicht. (daher "Vermutung" statt
"Erklärung")
Kann jemand meine Vermutung mit Wissen bestätigen?
Siegfried Schmidt
> Die frage ist nun:
> entspricht "*anti* semi join" einem "negierten" Join also:
> Not (NULL IS NULL OR NULL) oder (NULL IS NULL OR [wert] = NULL)
> uns somit immer: NOT (True) ?
> Das würde zwar erklären, warum keine DS geliefert werden, aber ob das
> die richtige Erklärung ist, weiß ich nicht. (daher "Vermutung" statt
> "Erklärung")
>
> Kann jemand meine Vermutung mit Wissen bestätigen?
"The expression RVC NOT IN IPV is equivalent to NOT ( RVC IN IPV )"
[ISO/IEC 9075-2:1999]
Siegfried
--
http://www.schmidt.ath.cx