Showing posts with label isnull. Show all posts
Showing posts with label isnull. Show all posts

SQL NULL cannot be applied to arithmetic operations (select NULL > 1 = NULL)


"Any arithmetic expression containing a null always evaluates to null. For example, null added to 10 is null." -http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements005.htm#i59110

In SQL Server, this behavior can result in string concatenation failing due to one of the values being NULL, which, as is NULL's nature, turns the entire variable into NULL.



....results in: NULL

Two easy ways to work around* the inability to evaluate NULL are as follows:
 (COALESCE takes first non-NULL value specified whereas ISNULL takes just one alternate value)

Different database platforms have different rules for NULL but arithmetic ops will not work in any, as NULL is only ever NULL, and NULL is an unknown. If you are checking for the existence of data, you should be using:


select top 5 m.Column1 FROM MYTABLE m WHERE m.Column1 IS NOT NULL


*you can do this in lieu of not getting bad data in the 1st place, obviously- if the column in question needs to hold a legitimate numeric value for calculation.. your app probably should not be generating and storing null values into that column.