CODEWITHSUNDEEP
mysqlsqldbt
KushalR
KushalR

Reputation: 351

What does a zero value coalesce function do?

I have been exploring dbt tools and I came across the following code snippet :

coalesce(customer_orders.number_of_orders, 0) as number_of_orders

I understand that a coalesce function is used to return the first non-null value in a list. What I do not understand is what does the zero in the second parameter signify?

Upvotes: 0

Views: 2153

Answers (2)

Ankit Jindal
Ankit Jindal

Reputation: 4050

The COALESCE function returns the first non-null value in a list. COALESCE can take n number of arguments.

COALESCE(val1, val2, ...., val_n)

So according to the query:

coalesce(customer_orders.number_of_orders, 0) as number_of_orders

In case customer_orders.number_of_orders is NULL the result returned in number_of_orders would be 0.

Upvotes: 2

Jonas Metzler
Jonas Metzler

Reputation: 6005

COALESCE can use as many arguments as you want. In most cases (like in your example), COALESCE(some_column,0) is used to prevent that creating a sum or building an average will not lead to the desired result.

Assume there are three columns and you want to sum them. In case you don't use COALESCE, the sum will be NULLeven if only one of your three columns is NULL. So you will use COALESCEand replace NULL values by zero in order to receive the sum of all NOT NULL values. You can "translate" COALESCE into a CASE WHEN construct:

COALESCE(column1,0)

does following:

CASE WHEN column1 IS NULL THEN 0 ELSE column1 END

Another use case of COALESCE is to replace column1 by column2 if column1 is NULL, if also column2 is NULL, take column3 etc. I created an example here, so you can see what I mean: db<>fiddle

Upvotes: 0

Related Questions