I would like to programmatically handle NULL values in a database.

If I add or multiply a known value to a NULL, the result is still NULL. This is an appropriate result as a NULL value in a database schema means “unknown” or “nonexistent”, so mathematic functions against known values and NULL should produce NULL.

That said, if I want a sum of line-items I could filter out NULL values, e.g.,

SQL> SELECT SUM(col) FROM foo WHERE col IS NOT NULL;

However, this could get difficult to manage in larger queries.

In Oracle, we can use NVL to get the above result

SELECT SUM(NVL(col,0)) FROM foo;

In MySQL and SQLite, we can use IFNULL

SELECT SUM(IFNULL(col,0)) FROM foo;

In Postgres we can use NULLIF

SELECT SUM(NULLIF(col,0)) FROM foo;

And in Microsoft SQL Server we can use ISNULL

SELECT SUM(ISNULL(col,0)) FROM foo;

While I prefer NVL for it’s conciseness, I recommend COALESCE as it works in each of the above database engines. COALESCE accepts two or more parameters and simply returns the first non-null parameter, e.g.,

SELECT SUM(COALESCE(col,0)) FROM foo;

Accepting multiple parameters can be very useful in cases where we’re aggregating otherwise mutually exclusive columns, e.g.,

SELECT SUM(COALESCE(eggs.weight, spam.weight, 0)) AS lonely_weight
FROM eggs FULL OUTER JOIN spam ON eggs.id = spam.id
WHERE eggs.id IS NULL OR spam.id IS NULL;

In the above case, it’s the sum weight of all eggs without spam, and spam without eggs.