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 ;-)

Well, certainly in this case they are complete different queries?
Would not be good if engine would rewrite query1 into query2 ;-)

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://newsgroups.firebirdsql.info