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)
ANONYMOUS (1)
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
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
source to share
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
source to share
There is one more thing called COALESCE http://msdn.microsoft.com/en-us/library/ms190349.aspx I use quite often,
SELECT * FROM People WHERE COALESCE(PEOPLE_TYPE, '') != 'BOARD'
source to share