--- 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 >
Thanks a lot Mark for clarifying and suggesting the alternative. I will be using EXISTS. Kind regards Bhavbhuti
