>The engine actually resolves the subquery form to EXISTS(), because it is logically equivalent and is a far more economical
>search than IN(). However, this does *NOT* apply to a NOT IN() predicate. While NOT IN(sq) AND NOT EXISTS() are equivalent
>in many situations, there are some situations where they return different results. (I can't think of an example; I just
>know that it is proven somewhere that this can be so.)

I think it is generally regarding NULL. At least (rdb$security_class is null in my test database)

select *
from rdb$database r1
where r1.rdb$security_class not in (select r2.rdb$security_class from rdb$database r2)

doesn't return anything, whereas

select *
from rdb$database r1
where not exists(select * from rdb$database r2 where r1.rdb$security_class = r2.rdb$security_class)

returns one row.

Although I know that Firebird in many cases resolves a subquery to EXISTS, I'd be impressed if Dmitry could claim that Firebird is able to transform every other possible form of IN (<subselect>) into EXISTS, there are so many possible ways to write bad SQL (not that I know whether Firebird is able to do this or not, I'd just be impressed).

Set