The subquery will always return a row from LogEvent, but that row's itemID will be null if theitemID doesn't match a row from Item. That's why the subquery has the "and i.ItemID is null".
> Stephan Szabo wrote: > >> On Thu, 26 Feb 2004 [EMAIL PROTECTED] 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); > > Maybe I'm not reading his subquery correctly, but the left outer > join will produce a row from LogEvent regardless of whether or not a > matching row exists in Item, correct? So doesn't it reduce to: > > DELETE FROM LogEvent WHERE EventType <> 'i'; > > ??? > > Mike Mascari ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html