Re: correlated delete with 'in' and 'left outer join' - Mailing list pgsql-general

mike@linkify.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@lexus] \d foo
       Table "public.foo"
  Column |  Type   | Modifiers
--------+---------+-----------
  key    | integer |

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

[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 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@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