Difference of two columns with possible NULL cells

Question

I need to sort a table by the difference of two columns as

SELECT (first - second) AS dif
 FROM table1
ORDER BY dif DESC

but the two columns can be NULL. In this case, it returns the error:

BIGINT UNSIGNED value is out of range

The solution to this error is to use IFNULL(col,0), but the problem is that I need dif to be NULL if any of the columns is NULL

• Akina: In this case, it returns the error Extremely strange. I have tested it on MySQL 5.5.60, 5.6.10, 5.6.39, 5.6.40, 5.7.17, 5.7.22, 8.0.11 - all of them give the result you need.
• Googlebot: @Akina you're absolutely right. The problem was not null values, but my unsigned column. Thanks for pointing me in the right direction.

Answer 1

You could test for the columns being null before doing the calculation.

SELECT if(isnull(first) or isnull(second),null,(ifnull(first,0) - ifnull(secon,0))) AS dif
 FROM table1
ORDER BY dif DESC

Answer 2

Many ways of doing this, but I find this readable:

SELECT CASE WHEN first IS NULL OR second IS NULL THEN NULL
 ELSE first - second END AS dif
 FROM table1
ORDER BY 1 DESC

Answer 3

If the datatype of the columns is BIGINT UNSIGNED, then consider doing CAST(x AS SIGNED)

See also the 'operator' <=>