>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.
Below is one example (assuming rdb$description is null).
This doesn't return anything since it is unknown whether NULL is in [NULL] or
not:
select r1.*
from rdb$database r1
where r1.rdb$description not in (select r2.rdb$description
from rdb$database r2)
This returns one row since the equivalence test doesn't return anything and,
hence, NOT EXISTS yields true:
select r1.*
from rdb$database r1
where not exists (select * from rdb$database r2
where r1.rdb$description = r2.rdb$description)
The top select could be translated:
select r1.*
from rdb$database r1
where not exists (select * from rdb$database r2
where r1.rdb$description = r2.rdb$description
and r1.rdb$description is not NULL
and r2.rdb$description is not NULL)
but I don't think all cases are that easily translated.
And, of course, I agree that NOT IN (SELECT) often is a lot worse than
IN(SELECT), although I do think the WHERE clause matters (if the Firebird crew
has managed to translate all cases of IN into EXISTS under the hood, then I
agree it doesn't matter).
Set
