>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