--- In firebird-support@yahoogroups.com, 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