Why choose to exclude NULL records for varchar comparisons

I have a table with 782,856 records. This table has a PEOPLE_TYPE column which is varchar (20). I don't think the table schema matters, but if it does, I'll happily post it.

It has these different meanings (parens is an account of each type):

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


So why does this choice return these results?

select * from people where PEOPLE_TYPE != 'BOARD'


This returns 225 rows ... USER and ANONYMOUS .... why are my zeros not included ... because now I have done a text search and NULLs cannot be compared, so are they eliminated?

Thank you for your patience with my corrected question.


source to share

3 answers

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

NULL = NULL false

NULL! = Something is false

NULL! = NULL is also wrong.

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

Your request will require

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




You can read this for details on why NULL records are not returned: http://msdn.microsoft.com/en-us/library/ms188048.aspx

If you want NULL records to return, you need to write your query like this:

select * from people where ISNULL(PEOPLE_TYPE, '0') != 'BOARD'


Or that:

select * from people where PEOPLE_TYPE != 'BOARD' OR PEOPLE_TYPE IS NULL




There is one more thing called COALESCE http://msdn.microsoft.com/en-us/library/ms190349.aspx I use quite often,





All Articles