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

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