--- In firebird-support@yahoogroups.com, "Arno Brinkman" wrote:

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

Maybe we could introduce a new SQL extention:

select TABLE_A.field1
from TABLE_A
where TABLE_A.joinfield not in
(select distinct joinfield from TABLE_B)
USING UPSCENE OPTIMIZATION [WITHOUT BAMSEMUMS]

which created such funny results?

On a more serious note, why does the optimizer care about DISTINCT
when using subselects with [NOT] IN at all? Anyway, it is strange SQL
and I think I will continue using

select TABLE_A.field1
from TABLE_A
where not exists (SELECT * FROM TABLE_B WHERE TABLE_A.joinfield =
TABLE_B.joinfield)

for such queries.

Set