querying using != against a field that has some NULL values
I have report that references a view in a SQL Server. The user can filter on a char field, but when they filter using "!=" to a value it filters out those records with that value AND any records that have a NULL value in that field. I tested this outside of the report, by putting the filter in the WHERE clause of SQL code and got the same results. Why is this happening? How can I get around this issue?
For instance:
SELECT f1, f2, f3
FROM aTable
f1 f2 f3
---- ---- ----
Y Y NULL
Y N M
N Y N
Y N NULL
SELECT f1, f2, f3
FROM aTable
WHERE f3 != 'N'
f1 f2 f3
---- ---- ----
Y N M
开发者_开发技巧Please let me know if you need additional information.
Any insight you can provide would be helpful :-)
Thanks,
Ben
Comparison operations with NULL always evaluate to false. You can use:
WHERE f3 is null or f3 != 'N'
You can use ISNULL function
SELECT f1, f2, f3 FROM aTable WHERE ISNULL(f3,'')!='N'
精彩评论