Skip site navigation (1) Skip section navigation (2)

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

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: Mike Mascari <mascarm(at)mascari(dot)com>
Cc: mike(at)linkify(dot)com, pgsql-general(at)postgresql(dot)org
Subject: Re: correlated delete with 'in' and 'left outer join'
Date: 2004-02-27 17:59:37
Message-ID: 20040227095523.G87097@megazone.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-general
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(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
...
> 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;

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;

In response to

Responses

pgsql-general by date

Next:From: Michael ChaneyDate: 2004-02-27 18:01:16
Subject: Re: correlated delete with 'in' and 'left outer join'
Previous:From: Tom LaneDate: 2004-02-27 17:27:50
Subject: Re: PostgreSQL insert speed tests

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group