On Mon, 20 Feb 2012 09:51:19 -0000, "venussoftop" <[email protected]> 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
