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

I didn't look at it too long, but it they return the same results...

But, it seems they don't :-)

With regards,

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