At 10:21 AM 10/03/2005 -0500, you wrote:

>I have a query
>
>Select .....
>Where z not in
>( select .....)
>
>There are a few duplicates in the sub query, but not many. I know a
>disctinct is slower, but in this caes it would possibely speed up the first
>select? Which would be a "general" better strategy. To add distinct to the
>subquery or no?

It won't be better and it might be worse. DISTINCT queries have to walk
the entire table in order to discover whether a value is distinct or
not. Of course, a correlated subquery is run for every row in the outer
select. "NOT IN" queries can't use an index, either. So, all in all, this
does not have any of the features of an efficient query, not matter how you
look at it.

Any "NOT-ed" existential subquery will be costly but the least costly might be
...
from atable A
where not exists (
select 1 from othertable B
where B.z = A.z)

...or not. Semantically, according to the optimizer, because of the NOT,
it's probably no different.

Another approach is to avoid the row-by-row reprocessing of the subquery
and instead use a left join to find the non-matches, which should utilise
an index on B.z, if there is one, for the join search:

select A.field1, A.field2, A.z, B.z
from atable A
left join othertable B
on A.z = B.z
where B.z is null

./hb