--- 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? Please advise Thanks and regards Bhavbhuti
