Search code examples
sql-serverperformancet-sqlexecution

What are the main differences between NOT IN vs. <> ALL?

I was asking myself whether I should use <> ALL or NOT IN. Have a look at these two sample queries:

SELECT PersonId FROM tabPerson
WHERE PersonId <> ALL(SELECT ParentId FROM tabPerson)

SELECT PersonId FROM tabPerson
WHERE PersonId NOT IN(SELECT ParentId FROM tabPerson)

The two queries are returning exactly the same results.

Now I'm wondering what the main differences between <> ALL and NOT IN are. Does anyone have an idea?

Solution

  • Both queries will produce exactly the same results. Even if the column queried in the subquery contains NULL values, it will not make any difference.

    I compared a couple of execution plans of such queries on tables in my databases here and in each case they also were exactly the same.

    So only the only difference is readability/familiarity of the code to other developers. There is a clear advantage for the NOT IN expression here. The ALL (and equally ANY and SOME) keyword is rarely used and will feel alien to other developers.

    See the MS Technet Article Comparison Operators Modified by ANY, SOME, or ALL for further explanation of these keywords. – BTW: This article concludes with the finding that the both statements from your question are equivalent.