On Fri, Apr 13, 2012 at 12:43 PM, venussoftop <venussoftop@...> wrote:

>
>
> --- In firebird-support@yahoogroups.com, Svein Erling Tysvær
> <svein.erling.tysvaer@...> wrote:
> > First, I would generally recommend you to forget that it is at all
> possible to use IN (<subselect>). Myself, I occasionally use IN (constant),
> but hardly ever IN (<subselect>) - using EXISTS can normally replace it, is
> sometimes faster and never slower (well, I don't know about the later
> Firebird versions, but would expect there to still be queries that Firebird
> doesn't convert from IN (<subselect>) to EXISTS under the hood).
>

There's a semantic difference between NOT IN and the equivalent NOT EXISTS
having to do with NULL values and empty result sets. I've understood it
from time to time, but this is not one of those times. So it's not
possible to fold all IN subqueries into EXISTS subqueries. Worse, the NOT
IN defies index optimization.

> Somehow IN comes to my mind first, I am at an intermediate stage where I
> am neither a beginner nor a pro with SELECTs :)

Having IN come to mind is not a problem. Just train your mind to think of
NOT IN as a programmer trap, to be avoided if at all possible.


Good luck,


Ann


[Non-text portions of this message have been removed]