'NOT IN' vs '<> ALL'

[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

 

[Marshall Barton]

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?

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

 

[KenSheridan via AccessMonster.com]

Sarah:

As Marshall says, a join will generally perform best, but if using a subquery
the NOT IN predicate (but not the IN predicate) will only work if none of the
rows returned by the subquery has a NULL at the column position in question.
This is because a NOT IN(x,y, NULL, z) is the equivalent of:

a <> x AND a <> y AND a <> NULL AND a <> z

so it evaluates to NULL, neither TRUE nor FALSE

The same is true of <> ALL

Using the NOT EXISTS predicate will get round this:

SELECT SomeTable.*
WHERE NOT EXISTS
(SELECT *
FROM SomeOtherTable
WHERE SomeOtherTable.SomeColumn = SomeTable.SomeColumn);

Performance is also generally better with the EXISTS or NOT EXISTS predicate
than with IN or NOT IN.

Ken Sheridan
Stafford, England

 

[Sarah]

Thank to Ken and Marshall for great advice.
Sarah

 

[Sarah]

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