Re: [GENERAL] correlated delete with 'in' and 'left outer join'

[Mike Mascari]

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:
[EMAIL PROTECTED] 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:

[EMAIL PROTECTED] select * from foo;
 key
-----
   1
   3
(2 rows)
[EMAIL PROTECTED] select * from bar;
 key | value
-----+-------
   1 | Mike
   2 | Joe
(2 rows)
[EMAIL PROTECTED] 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)

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

[EMAIL PROTECTED] 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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings