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

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

From: Mike Mascari <mascarm(at)mascari(dot)com>
To: mike(at)linkify(dot)com
Cc: sszabo(at)megazone(dot)bigpanda(dot)com, pgsql-general(at)postgresql(dot)org
Subject: Re: correlated delete with 'in' and 'left outer join'
Date: 2004-02-27 07:07:22
Message-ID: 403EECAA.1010901@mascari.com (view raw or flat)
Thread:
Lists: pgsql-general
mike(at)linkify(dot)com wrote:

> The subquery will always return a row from LogEvent, but that row's itemID
> will be null if the itemID doesn't match a row from Item.  
> That's why the subquery has the "and i.ItemID is null".

You lost me.

[test(at)lexus] \d foo
       Table "public.foo"
  Column |  Type   | Modifiers
--------+---------+-----------
  key    | integer |

[test(at)lexus] \d bar
       Table "public.bar"
  Column |  Type   | Modifiers
--------+---------+-----------
  key    | integer |
  value  | text    |

[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 f.key from foo f left outer join bar b on f.key 
= b.key and b.key is null;
  key
-----
    1
    3
(2 rows)

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;
  fookey
--------
       3
(1 row)

Nevertheless, Stephan's solution matches your description of the 
problem and excutes the logical equivalent of the above much more 
rapidly...

Mike Mascari







In response to

Responses

pgsql-general by date

Next:From: Shachar ShemeshDate: 2004-02-27 08:59:54
Subject: Re: Windows Library for libpq
Previous:From: mikeDate: 2004-02-27 06:50:57
Subject: Re: correlated delete with 'in' and 'left outer join'

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