--- 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
>

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