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

On Fri, 27 Feb 2004, Mike Mascari wrote:

> 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@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
...
> Another example:
>
> [test@lexus] select * from foo;
>   key
> -----
>     1
>     3
> (2 rows)
>
> [test@lexus] select * from bar;
>   key | value
> -----+-------
>     1 | Mike
>     2 | Joe
> (2 rows)
>
> [test@lexus] select foo.key, bar.key from foo left outer join bar on
> foo.key = bar.key and bar.key is null;

ON conditions and WHERE conditions are different.

Try
select foo.key, bar.key from foo left outer join bar on foo.key=bar.key
where bar.key is null;