using the NOT IN operator on a column with NULL values

Question

I have a table employee which has a column company which has all null values

Now, I want to use a NOT IN operator for the company name as

select * from employee where employee.company NOT IN ('ABC', 'DEF')

Now technically this should not alter the result since the company column already had null values.

But adding the NOT IN gives 0 rows.

Is this due to the fact that employee.company column has NULL values?

Answer 1

Try this way:

select * 
from employee E
where (E.company NOT IN ('ABC', 'DEF')
       or E.company is null)

Answer 2

SQL uses three valued logic: true, false, and unknown. A comparison with null results in unknown, which is not true.

So a where clause like:

where null in (1,2)

Will not return any rows.