How to treat null as 0 when use addition in sql with out IFNULL

Question

I have a query which add many thing in a expression. Like this select a+b+c+d+e ....
But some of them may be null and I want to treat null value as 0.

I know I can use IFNULL like select ifnull(a,0)+ifnull(b,0)+ifnull(c,0)+ifnull(d,0)+ifnull(e,0), but I have many queries and this seems laboursome.

Is there some easy way like a function to achieve the same result?

Please tag the actual RDBMS you are using. I'm guessing mysql, as sql server doesn't have an IFNULL function afaik. — DeanOC
– DeanOC, Commented Nov 18, 2020 at 6:40
No, there isn't a shortcut. IFNULL if you want to handle NULLs... — CoffeeNeedCoffee
– CoffeeNeedCoffee, Commented Nov 18, 2020 at 17:43

geertjanvdk · Accepted Answer · 2021-01-14 18:06:48Z

0

As mentioned in the comments, you will need to use indeed either IFNULL or IF.

However, if you have lots of them, and you are in control of the schema, I would suggest defining your fields as .. NOT NULL DEFAULT 0 (explicit default is good). This way, you will not need IFNULL any longer.

answered Jan 14, 2021 at 18:06

geertjanvdk

3,52827 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to treat null as 0 when use addition in sql with out IFNULL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related