{"id":594,"date":"2012-01-17T18:25:27","date_gmt":"2012-01-17T18:25:27","guid":{"rendered":"http:\/\/tech.avant.net\/q\/?p=594"},"modified":"2012-12-25T22:38:38","modified_gmt":"2012-12-25T22:38:38","slug":"nvl-ifnull-nullif-isnull-coalesce","status":"publish","type":"post","link":"https:\/\/tech.avant.net\/q\/nvl-ifnull-nullif-isnull-coalesce\/","title":{"rendered":"nvl, ifnull, nullif, isnull, coalesce"},"content":{"rendered":"

I would like to programmatically handle NULL values in a database.<\/p>\n

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.<\/p>\n

That said, if I want a sum of line-items I could filter out NULL values, e.g.,<\/p>\n

\r\nSQL> SELECT SUM(col) FROM foo WHERE col IS NOT NULL;\r\n<\/pre>\n
However, this could get difficult to manage in larger queries.<\/p>\n
In Oracle, we can use NVL to get the above result<\/p>\n
\r\nSELECT SUM(NVL(col,0)) FROM foo;\r\n<\/pre>\n
In MySQL and SQLite, we can use IFNULL<\/p>\n
\r\nSELECT SUM(IFNULL(col,0)) FROM foo;\r\n<\/pre>\n
In Postgres we can use NULLIF<\/p>\n
\r\nSELECT SUM(NULLIF(col,0)) FROM foo;\r\n<\/pre>\n
And in Microsoft SQL Server we can use ISNULL<\/p>\n
\r\nSELECT SUM(ISNULL(col,0)) FROM foo;\r\n<\/pre>\n
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.,<\/p>\n
\r\nSELECT SUM(COALESCE(col,0)) FROM foo;\r\n<\/pre>\n
Accepting multiple parameters can be very useful in cases where we’re aggregating otherwise mutually exclusive columns, e.g.,<\/p>\n
\r\nSELECT SUM(COALESCE(eggs.weight, spam.weight, 0)) AS lonely_weight\r\nFROM eggs FULL OUTER JOIN spam ON eggs.id = spam.id\r\nWHERE eggs.id IS NULL OR spam.id IS NULL;\r\n<\/pre>\n
In the above case, it’s the sum weight of all eggs without spam, and spam without eggs.<\/p>\n","protected":false},"excerpt":{"rendered":"
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. […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[16,15,18],"tags":[],"_links":{"self":[{"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/posts\/594"}],"collection":[{"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/comments?post=594"}],"version-history":[{"count":7,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/posts\/594\/revisions"}],"predecessor-version":[{"id":696,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/posts\/594\/revisions\/696"}],"wp:attachment":[{"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/media?parent=594"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/categories?post=594"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/tags?post=594"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}