'NOT IN' vs '<> ALL'

  • Thread starter Thread starter Sarah
  • Start date Start date
S

Sarah

greetings to all

An SQL fragment I use is... WHERE tblValues NOT IN (SELECT ...
The query runs properly. I have noticed that I can get the very same output
by using the fragment... WHERE tblValues <> ALL (SELECT ...

Is 'NOT IN' the same as '<> ALL'? Is there a preference?

thanks in advance

Sarah
 
Sarah said:
An SQL fragment I use is... WHERE tblValues NOT IN (SELECT ...
The query runs properly. I have noticed that I can get the very same output
by using the fragment... WHERE tblValues <> ALL (SELECT ...

Is 'NOT IN' the same as '<> ALL'? Is there a preference?
Click to expand...


Sure seems that way, but I have not explored the case when
the rblValues field contains Null.

I think a better way would be to use an outer join that also
appears to be equivalent to those. If the tblValues and
valuesfield fields are indexed, this approach should be
dramtically faster for even slightly large tables:

FROM table LEFT JOIN othertable
ON table.tblValues = othertable.valuesfield
WHERE othertable.valuesfield Is Null
 
Marshall - this works great but it took me a while before the light bulb came
on. I guess the idea is that after the LEFT JOIN, the non-NULL field values
in the right table are in exactly the records we want to exclude. Clever! I
haven't used OUTER JOINS that way before.
Thanks - Sarah
 
Back
Top