>Is NOT EXISTS also an option to replace NOT IN in that it will use

indices?

>Only for NOT NULL columns being a subject of NOT IN predicate. If the

column

>is nullable, then NOT EXISTS and NOT IN are not equivalent and expected to
>return different results.

I generally use NOT IN when the subselect clause contains a field which is
either

a primary key field or an element of a composite key so the problem so I
think that

Not Exists will be equivalent to NOT IN.

From a personal point of view I like the NOT IN syntax better because to my
mind

it is closer to natural language than the more complex structure required by
NOT EXISTS

or a left join and IS NULL.

It's very easy for people to look at a NOT IN query and see what it is
trying to accomplish

even if they are not proficient in SQL.

I have still found it to work OK when dealing with subqueries with small
recordsets (<20) but my initial

attempts to use it joining tables with about 10,000 records in each didn't
return any result in the

first 30 minutes, and kept the firebird server at 97+% CPU usage for hours
afterwards until I

first figured out what was going on.







[Non-text portions of this message have been removed]