I've had a query which expected to return all records except with specified value in the nullable column.
Select * FROM MyTbl where (MyColumn<>'ValueToExclude')
But the query didn't return any records with null values in the column.
The correct query to include records with null should have explicit is Null condition like the following:
Select * FROM MyTbl where (MyColumn<>'ValueToExclude' or MyColumn is Null)
Update: I found, that it is a well known problem, discussed in many forums, e.g here and here.
Another workarounds are:
where (Coalesce(MyColumn,"")<>'ValueToExclude' ) or
where (IsNull(MyColumn,"")<>'ValueToExclude' )
There is similar article here:
http://www.devx.com/vb2themax/Tip/18541
posted @ Tuesday, July 15, 2008 11:45 PM