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

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