Query using IN keyword

Question

I am learning how to write TSQL queries. I am trying to understand them in depth. This query that I got from a tutorial requires that I check for a NOT NULL in the second WHERE clause.

SELECT *
FROM Person.Person AS p
WHERE NOT p.BusinessEntityID IN (
    SELECT PersonID
    FROM Sales.Customer
    WHERE PersonID IS NOT NULL);

Now the table Sales.Customer has some NULL values for PersonID. If I remove this WHERE clause in the sub query, I get no results returned. In my obviously faulty thinking on the matter, I would think that if the sub query returned a NULL it would simply not meet the condition of the WHERE clause in the outer query. I would expect to get a result set for the rows that had a PersonID that is not NULL. Why does it not work according to this reasoning?

Answer 1

Understanding how NULL values are handled by SQL Server can be difficult for newcomers. A value of NULL indicates that the value is unknown. A value of NULL is different from an empty or zero value. No two null values are equal. Comparisons between two null values, or between a NULL and any other value, return unknown because the value of each NULL is unknown.

Null Values