Odd inconsistent behavior of SQL Server “IN” clause

Question

Query 1:

select count(UserID)
from users
where UserID IN (select UserID from otherTable)

Returns: 178

Query 2:: (simply changed IN to NOT IN)

select count(UserID)
from users
where UserID NOT IN (select UserID from otherTable)

Returns: 0

Query 3: (remove the IN-clause completely)

select count(UserID)
from users

Returns: 1047123 (over a million)

What? I can't... Even...

The only explanation I have is my database in inconsistent/corrupted, I have to run DBCC CHECKDB or something.

PS. UserID is a primary key, so - no dupes.

NOT IN will not be true when a NULL is returned by the subquery. See stackoverflow.com/questions/1406215/…. Use NOT EXISTS instead to avoid this gotcha. — Dan Guzman, Oct 28 '17 at 19:35
@DanGuzman THANKS!!! You should move your comment to an answer, so I can upvote + accept — jitbit, Oct 28 '17 at 19:42
sqlperformance.com/2012/12/t-sql-queries/left-anti-semi-join — Aaron Bertrand♦, Oct 28 '17 at 20:56

Erik Darling · Accepted Answer · 2017-10-28 20:16:21Z

3

NOT IN will not evaluate to true when one or more NULL values are returned by the subquery.

Use NOT EXISTS to avoid this common gotcha. See this answer for more information and links to more detailed information.

Erik Darling

28.1k13 gold badges86 silver badges143 bronze badges

answered Oct 28 '17 at 19:48

Dan Guzman

16.2k2 gold badges22 silver badges42 bronze badges

add a comment |

1 Answer 1