"null" in mathematical calculations?

Question

I'm trying to make a MySQL query that returns rows where (col_a+col_b-col_c+col_d) != col_e, where all of the columns are decimal and default to null. There is one row that I know of that meets these requirements, but when I ran the query with the above logic as the WHERE clause, the row didn't show up. I noticed that col_c was null, instead of a numerical value, and after changing it to 0 the row showed up when I ran the query.

Why did this happen? I have always assumed that null was interpreted as 0 in an instance such as the above?

Answer 1

NULL (as far as my interpretation goes) is unrepresentable data. The only appropriate tests for null are IS NULL, IS NOT NULL, and several functions made specifically to handle NULL values: http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html

You could say - IFNULL(col_c, 0)+col_d (COALESCE will work identically in this case).

More information on working with NULLs: http://dev.mysql.com/doc/refman/5.0/en/problems-with-null.html