I have a table with one column named value, of type int, and 4 rows in it:
20
50
NULL
0
Why does this query return 20 and 50 only? I need NULL values, too.
SELECT *
FROM dbo.myTable
WHERE value != 0
Add a comment
|
3 Answers
In SQL, NULL is a special value, nothing can ever be equal to or not equal to it. In fact, NULL doesn't even equal itself. When searching for null values, you must use the IS operator. So you need to change your query to include them:
SELECT *
FROM dbo.myTable
WHERE value !=0
OR value IS NULL
Comments
You can also replace null for sth else what is not a zero using isnull or coalesce
SELECT *
FROM dbo.myTable
WHERE ISNULL(value,1) <> 0
Comments
NULL is a "non-value". Some comparing-tools will ignore/skip every NULL's. (NULL doesn't exists.)
You have 3 Options:
1) Insert a value (42) instead of NULL. ex.: Set up a default ALTER TABLE myTable ADD CONSTRAINT DF_mytable_value DEFAULT (42) FOR value
2) Add NULL to your query condition.
SELECT *
FROM myTable
WHERE value != 1 OR value IS NULL
3) Compare every 0 as 0 and every else as 1 within your WHERE clausel:
SELECT *
FROM myTable
WHERE 1 = CASE value WHEN 0 THEN 0 ELSE 1
