Quick Tip: Using coalesce to check for NULL or zero


Here is a quick tip that you can use in QGIS expressions, T-SQL, or even PostgresSQL.

Normally if you have a column that you need query on to find all the NULL or zeros you have to do something like this:

COLA IS NULL OR COLA = 0

Well that isn’t too bad. Sure yeah it’s fine for one column but what if you have three and you need to check them all together

(COLA IS NULL OR COLA = 0) AND (COLB IS NULL OR COLB = 0) AND (COLC IS NULL OR COLC = 0)

That is pretty long and gets hard to read pretty quick.

To cut this down we can use the coalesce function – T-SQL, PostgresSQL, QGIS Expression. The coalesce function returns the first non-NULL value out of an expression, or list of values. So if you do something like this:

coalesce(NULL, "A", 0)

You will get “A” because the first value is NULL. The function will just evaluate each value/expression until something turns up that isn’t NULL.

Using that logic we can replace the above function with the following:

coalesce(COLA, 0) = 0 AND coalesce(COLB, 0) = 0 AND coalesce(COLC, 0) = 0

To me that is a lot clearer and readable.

Advertisements

2 thoughts on “Quick Tip: Using coalesce to check for NULL or zero

  1. Hi Nathan –
    Here it’s friday evening after ½ bottle of wine (from Australia, actually ;-), but the expression: “COLA IS NOT NULL OR COLA = 0” will find all rows where COLA is a number (different from NULL), making the last part of the expression superfluous. You probably mean: “COLA IS NULL OR COLA = 0” which will give you all rows where COLA is null or zero – the same as “COALESE (COLA,0) = 0”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s