Addition with NULL values

Question

In a stored procedure (Oracle in my case), I want to add some values to an existing record. Problem is that both the existing value and the value to be added can be null. I only want the result to be NULL when both operands are null. If only one of them is null, I want the result to be the other operand. If both are non-null, I want the result to be "normal" addition.

Here's what I am using so far:

SELECT column INTO anz_old FROM aTable Where <someKeyCondition>;
IF anz_old IS NULL
THEN
    anz_new := panzahl;
ELSE
    anz_new := anz_new + NVL (panzahl, 0);
END IF;
UPATE aTabel set column = anz_new Where <someKeyCondition>;

Is there a more elegant way (pereferably completely in SQL, i.e. just in an update statement short of a long CASE-Statement with basically the same logic as the above code)?

Answer 1

If you want to add a and b and either may be null, you could use coalesce, which returns the first non-null parameter you pass it:

coalesce(a+b,a,b)

So in this case, if neither parameter is null, it will return the sum. If only b is null, it will skip a+b and return a. If a is null, it will skip a+b and a and return b, which will only be null if they are both null.

Answer 2

In SQL, Null is supposed to be a state that says "I don't know".

If you don't know how much b is, then you also do not know how much a+b is, and it is misleading to pretend that a+b=a in that case.