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 15:04:39
Message-ID: 20040227070104.Q84032@megazone.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-general
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


In response to

Responses

pgsql-general by date

Next:From: Greg StarkDate: 2004-02-27 15:05:45
Subject: Re: PostgreSQL insert speed tests
Previous:From: Keith BottnerDate: 2004-02-27 15:01:06
Subject: Re: Postgres clustering?

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