Geek Answers Handbook

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

I asked myself whether to use <> ALLor NOT IN. Take 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)

Two queries return exactly the same results.

Now I wonder what are the main differences between <> ALLand NOT IN. Does anyone have an idea?

+4
source share
2 answers

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

, .

, - / . NOT IN . ALL ( ANY SOME) .

. MS Technet , , . - BTW: , .

+1

:

DECLARE @t1 TABLE (a INT)
INSERT INTO @t1 VALUES (1), (2)

DECLARE @t2 TABLE (b INT)
INSERT INTO @t2 VALUES (2)

SELECT * FROM @t1
WHERE a <> ALL(SELECT b FROM @t2)

SELECT * FROM @t1
WHERE a NOT IN (SELECT b FROM @t2)

NULL:

INSERT INTO @t2(b) VALUES (NULL)

SELECT * FROM @t1
WHERE a <> ALL(SELECT b FROM @t2)

SELECT * FROM @t1
WHERE a NOT IN (SELECT b FROM @t2)
0

More articles:


All Articles