> USING UPSCENE OPTIMIZATION [WITHOUT BAMSEMUMS]
>
> which created such funny results?

It's probably the lack of Bamsemums that makes me answer
questions like this with such stupid answers :-)

With regards,

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

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