Good question, and good comment to explain the result and the fact that it depends on ANSI_NULLS setting.

It got me thinking about the other IN / NOT IN syntax as well:

Suppose I want a list of all the employees in NORTHWIND database who have other people reporting to them. I coud do it like this:

select * from employees where employeeid IN (select reportsto from employees)

and I get 2 records: employee 2 and employee 5.

Suppose I want a list of all the employees in NORTHWIND database who DON'T have anyone reporting to them. If I try it using the apparently equivalent code ...

select * from employees where employeeid NOT IN (select reportsto from employees)

... I get no records returned.

The reason is that there is one employee with a NULL entry in "reportsto", which makes the NOT IN comparison translate to (employeeid <>2 AND employeeid <>5 AND employeeid <> NULL)

And as mentioned above, TRUE AND TRUE AND UNKNOWN equals UNKNOWN.

To make it work I need to remove the NULLS:

select * from employees where employeeid NOT IN (select reportsto from employees where reportsto is not null)

Now I just need to check my actual code to make sure I haven't fallen for this one in real life.