vk-kirov (12/8/2010)

---

Good question.

But this is not an explanation at all:

Explanation: If a subquery returns any null value the main query will not return any rows

I suggest the following explanation.

The SELECT query can be modified as follows:

select *

from dbo.Employees

where EmployeeID not in (SELECT NullableColumn FROM NullOperation)

-->

select *

from dbo.Employees

where EmployeeID not in (NULL, 0, 1)

-->

select *

from dbo.Employees

where (EmployeeID <> NULL) and (EmployeeID <> 0) and (EmployeeID <> 1)

The result of the comparison "EmployeeID <> NULL" is undefined for all values of EmployeeID (unless you set the ANSI_NULLS option to OFF). So the result of the WHERE clause is undefined (i.e. not true), and no rows are returned.