On 21-2-2012 9:45, venussoftop wrote: > > > --- In [email protected], Mark Rotteveel<mark@...> wrote: >> >> On Mon, 20 Feb 2012 09:51:19 -0000, "venussoftop"<venussoftop@...> >> wrote: >>> Hi all >>> >>> I have tables that can allow me to filter data like this: >>> SELECT a.* >>> FROM tablea a >>> WHERE a.iTableBLinkID NOT IN (SELECT b.iPKID FROM tableb b) >>> ... >>> these tables will grow over the years with more and more records >>> cancelling each other out, so there really will be only few tens of >> records >>> which do not have a corresponding iTableBLinkID records at any given >> point >>> of time >>> >>> Or should I introduce a flag in tablea, something like iClosed SMALLINT >>> which is default zero but set to 1 programatically so the same result >> could >>> be got like >>> SELECT a.* >>> FROM tablea a >>> WHERE a.iClosed = 0 >>> ... >>> >>> Which is more efficient from SQL point of view? >> >> Instead of the NOT IN clause, use a NOT EXISTS, for example: >> >> WHERE NOT EXISTS (SELECT 1 FROM tableb b WHERE b.iPKID = a.iTableBLinkID) >> >> This will make it possible to use indices, which is a lot more efficient >> than populating a list and then checking for existence in that list. >> >> Use of a flag field will probably not be efficient either, as such fields >> usually have low selectivity, making an index useless. >> >> Mark >> > > One more question Mark. Will > WHERE EXISTS (SELECT 1 FROM tableb b WHERE b.iPKID<> a.iTableBLinkID) > > be more efficient as the<> records will be far less over the time or does > that interfere with using indices?
That is a totally different select and will have an entirely different result than the one you desire. Mark -- Mark Rotteveel
