Hi,

> 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
>
> SELECT TABLE_A.field1
> from TABLE_A
> inner JOIN TABLE_A on TABLE_A.joinfield = TABLE_B.joinfield
>
> which is lightning quick.

The optimizer could rewrite the first query into the second, but doesn't.
Arno might explain why ;-)

>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?

Because this is on the list for future improvements.

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com