Exercise #60 (tips and solutions)
In 2.3.1 decision it is used full external join (FULL JOIN) of subqueries, to consider all possible cases: when income sum or outcome sum is NULL for some reception point in result of execution these subqueries (in other words, there was no income or outcome). If, to example, income is 1000 and outcome is 800, then all possible cases will be considered:
Case NULL NULL cannot be, because it would mean that the reception point did not exist (at this point in time).
In SELECT clause construction is used to replace the NULL by zero in clause of calculating the balance. Logic absolutely correct, but the design is applied incorrectly:
Mistake is that there actually involved a simple comparison operation with a NULL value, namely,
Comparing with the NULL-value always yields UNKNOWN. Therefore WHEN condition is not fulfilled, resulting in running a ELSE branch, always returning the inc value, even in the case where inc is NULL.
To return to discussion of exercise #60
To solve a problem on SQL-EX.RU