Subject Optimisation Question
Author donjules2k
With firebird, why is that this:

select TABLE_A.field1
from TABLE_A
where TABLE_A.joinfield not in (select distinct joinfield from TABLE_B)

runs much much much slower than

from TABLE_A
inner JOIN TABLE_A on TABLE_A.joinfield = TABLE_B.joinfield

which is lightning quick. What I think is happening is that the sub
query is being run for every row in the outer query, hence making it
slow. But if that is true, why does it not just run the subquery once
and then apply the results?

Any ideas why this might be so?