On 21-2-2012 9:45, venussoftop wrote:

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

That is a totally different select and will have an entirely different
result than the one you desire.

Mark
--
Mark Rotteveel