SQL Server: Why does comparison null=value return true for NOT IN?

Why does the comparison of value to null return false, except when using a NOT IN, where it returns true?

Given a query to find all stackoverflow users who have a post:

SELECT * FROM Users
WHERE UserID IN (SELECT UserID FROM Posts)

This works as expected; i get a list of all users who have a post.

Now query for the inverse; find all stackoverflow users who don't have a post:

SELECT * FROM Users
WHERE UserID NOT IN (SELECT UserID FROM Posts)

This returns no records, which is incorrect.

Given hypothetical data¹

Users              Posts
================   ===============================
UserID  Username   PostID   UserID  Subject
------  --------   -------  ------  ----------------
1       atkins     1        1       Welcome to stack ov...
2       joels      2        2       Welcome all!
...     ...        ...      ...
399573  gt6989b    ...      ...
...     ...        ...      ...
                   10592    null    (deleted by nsl&fbi...
                   ...      ... 

And assume the rules of NULLs:

• NULL = NULL evaluates to unknown
• NULL <> NULL evaluates to unknown
• value = NULL evaluates unknown

If we look at the 2nd query, we're interested in finding all rows where the Users.UserID is not found in the Posts.UserID column. i would proceed logically as follows:

Check UserID 1

• 1 = 1 returns true. So we conclude that this user has some posts, and do not include them in the output list

Now check UserID 2:

• 2 = 1 returns false, so we keep looking
• 2 = 2 returns true, so we conclude that this user has some posts, and do not include them in the output