|Subject||RE: [firebird-support] Problem with not in queries in Firebird 2.0 RC3|
>Is NOT EXISTS also an option to replace NOT IN in that it will useindices?
>Only for NOT NULL columns being a subject of NOT IN predicate. If thecolumn
>is nullable, then NOT EXISTS and NOT IN are not equivalent and expected toI generally use NOT IN when the subselect clause contains a field which is
>return different results.
a primary key field or an element of a composite key so the problem so I
Not Exists will be equivalent to NOT IN.
From a personal point of view I like the NOT IN syntax better because to my
it is closer to natural language than the more complex structure required by
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]