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, 2017 at 19:35
@DanGuzman THANKS!!! You should move your comment to an answer, so I can upvote + accept — jitbit, Oct 28, 2017 at 19:42
sqlperformance.com/2012/12/t-sql-queries/left-anti-semi-join — Aaron Bertrand, Oct 28, 2017 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

33.6k13 gold badges113 silver badges270 bronze badges

answered Oct 28, 2017 at 19:48

Dan Guzman

24.3k2 gold badges40 silver badges66 bronze badges

Add a comment |

1 Answer 1