Adam wrote:

>> Well, others gave you the solution with IN, but that is not "the best
>> way", as IN can be much slower than EXISTS:
>
> Give the optimiser some credit. Under FB 1.5 and higher, they pretty
> much always use the same plan (internally converted to exists). I am
> yet to see a case where they don't (although I imagine with some
> complex aggregates it may possibly not convert).
>
> Adam

You're right that the optimizer has improved to the extent that there is
far less need for this kind of optimization and that it gets it right in
the vast majority of cases, but I think I saw a case with "multilevel"
IN, i.e. ... WHERE ... IN (SELECT ... WHERE ... IN (SELECT...)), that
had a really bad plan and needed a minute to produce a result on a table
with 5000 records or something (though I can't find it in the list, so
it may be my memory that's in error).

Moreover, not every IN can be translated to EXISTS (or rather, not every
NOT IN can be translated to NOT EXISTS):

select * from rdb$database r1
where r1.rdb$security_class not in
(select r2.rdb$security_class from rdb$database r2)

doesn't return any record in my database, whereas

select * from rdb$database r1
where not exists
(select * from rdb$database r2
where r1.rdb$security_class = r2.rdb$security_class)

returns the record (in my database security_class IS NULL, I don't know
if it ever has a value).

Set