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