William L. Thomson Jr. wrote:

>
> Although I am curious as to the difference between WHERE NOT EXISTS and
> WHERE <field> NOT IN? Is one better than the other, more efficient etc?

Basically, IN always worries me because of it's tricky query
transformation in the latter stages of compilation. If you start with

select <fields> from table1 t1
where not exists
(select 1 from table2 t2
where t1.field = t2.field)

you've got a correlated subquery that will be optimized to walk (natural
scan) table1 and perform indexed lookups on table2 - assuming that
table2.field is indexed, which, of course, it would be.

This query is more problematic...

select <fields> from table1 t1
where t1.field not in
(select t2.field from table2 t2)


An earlier, much dumber version of our compiler/optimizer might lookup
all records in table2 for each record in table1 to determine that the
the value in table1 isn't present. As a general thing, I like to let
the optimizer pick paths and hope Dmitry and Arno will fix it when its
wrong, but the IN construct just leaves too many opportunities for bad
guesses.


Regards,


Ann