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

On Thu, 26 Feb 2004 mike@linkify.com wrote:

> I'm using postgresl 7.3.2 and have a query that executes very slowly.
>
> There are 2 tables: Item and LogEvent.  ItemID (an int4) is the primary key
> of Item, and is also a field in LogEvent.  Some ItemIDs in LogEvent do not
> correspond to ItemIDs in Item, and periodically we need to purge the
> non-matching ItemIDs from LogEvent.
>
> The query is:
>
> delete from LogEvent where EventType != 'i' and ItemID in
> (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);
>
> I understand that using "in" is not very efficient.
>
> Is there some other way to write this query without the "in"?

Perhaps
delete from LogEvent where EventType != 'i' and not exists
 (select * from Item i where i.ItemID=LogEvent.ItemID);