Why select eliminating NULL records on a varchar comparison

Question

I have a table with 782,856 records. There is a column PEOPLE_TYPE in this table that is varchar(20). I don't think table schema matters but if it does I will gladly post it.

It has these distinct values (parens is a count of each type):

NULL (782,101)
ANONYMOUS (1)
BOARD (530)
USER (224)

So why does this select return these results???

select * from people where PEOPLE_TYPE != 'BOARD'

This return 225 rows...USER & ANONYMOUS....why aren't my nulls included...because I have now performed a text search and NULLs can't really be compared so they are eliminated?

Answer 1

NULL is a strange thing. Any comparison with NULL is false:

NULL = NULL is false

NULL != anything is false

NULL != NULL is also false.

You have to say things like column is null, or column is not null.

Your query would need

select * from people where PEOPLE_TYPE != 'BOARD' or PEOPLE_TYPE is null