Re: correlated delete with 'in' and 'left outer join'

Stephan Szabo wrote:
> On Fri, 27 Feb 2004, Mike Mascari wrote:
>
>>To do what I think you believe to be happening w.r.t. outer joins,
>>you'd have to have a subquery like:
>>
>>[test(at)lexus] select a.fookey
>>test-# FROM
>>test-# (SELECT foo.key AS fookey, bar.key as barkey FROM foo LEFT
>>OUTER JOIN bar ON foo.key = bar.key) AS a
>>test-# WHERE a.barkey IS NULL;
>
> This AFAICS is pretty much what he did, except that he didn't alias the
> join which is okay I believe. He had one condition in on and two
> conditions in where.
>
> The original subquery looked like:
> select distinct e.ItemID from LogEvent e left outer join Item i
> on e.ItemID = i.ItemID where e.EventType != 'i' and i.ItemID is null

That is indeed the original subquery. But the 'i.ItemID is null'
condition doesn't change the IN list one iota. He was somehow
expecting the subquery to yield records internally like:

1 NULL
2 NULL
3 3

and simultaneously have the condition 'i.ItemID is null' eliminate
the third tuple. But that is not how the left outer join executes.
The 'i.ItemID is null' condition is evaluated, probably always to
false, which ensures that the left outer join will never find a
matching row from the 'Item' relation and, if queried not as a
subquery but stand-alone as:

select distinct e.ItemID, i.ItemID
from LogEvent e left outer join Item i on e.ItemID = i.ItemID
where e.EventType != 'i' and i.ItemID is null

would always yield a relation of the form:

e.ItemID NULL

for every e.ItemID whose e.EventType != 'i'. That ain't right.

Another example:

[test(at)lexus] select * from foo;
key
-----
1
3
(2 rows)

[test(at)lexus] select * from bar;
key | value
-----+-------
1 | Mike
2 | Joe
(2 rows)

[test(at)lexus] select foo.key, bar.key from foo left outer join bar on
foo.key = bar.key and bar.key is null;
key | key
-----+-----
1 |
3 |
(2 rows)

[test(at)lexus] select foo.key, bar.key from foo left outer join bar on
foo.key = bar.key;
key | key
-----+-----
1 | 1
3 |
(2 rows)

[test(at)lexus] select a.fookey, a.barkey from (select foo.key as
fookey, bar.key as barkey from foo left outer join bar on foo.key =
bar.key) as a where a.barkey is null;
fookey | barkey
--------+--------
3 |
(1 row)

Mike Mascari