NOT IN vs. NOTEXISTS

Asked Jul 12 '13 at 20:40

Active Jul 12 '13 at 21:27

Viewed 163 times

Why would the first query return 0 rows but the second query return rows?

SELECT *
FROM   dbo.GW_ASO_SCRUBBED
WHERE  ACCOUNT_NUMBER NOT IN (SELECT Acct_num
                              FROM   dbo.ACCOUNT_X_MASTER)

SELECT *
FROM   dbo.GW_ASO_SCRUBBED s
WHERE  NOT EXISTS (SELECT Acct_num
                   FROM   dbo.ACCOUNT_X_MASTER M
                   WHERE  s.ACCOUNT_NUMBER = M.ACCT_NUM)

edited Jul 12 '13 at 21:27

Martin Smith

438,706
87
741
845

asked Jul 12 '13 at 20:40

Chad

23,658
51
191
321

10

Because `Acct_num` contains a `NULL` – Martin Smith Jul 12 '13 at 20:41
1

This post has a good explanation if Acct_num has nulls then Not In won't work the same way as Not exists: http://stackoverflow.com/questions/173041/not-in-vs-not-exists – scrappedcola Jul 12 '13 at 20:43
That was exactly it. Plz eply and I will mark the answer correct. :-) Thx – Chad Jul 12 '13 at 20:46
It's already answered in the possible duplicate. – Martin Smith Jul 12 '13 at 20:48
1

@ChadD No need, we already have an answer. an answer that simply links to another Stack Overflow question is subject to being deleted. The good way to handle it is just mark this question as a duplicate. – George Stocker Jul 12 '13 at 20:48

NOT IN vs. NOTEXISTS

0 Answers0