Practical SQL Handbook, The: Using SQL Variants, Fourth Edition

NULLs and Computations

The really sticky problems associated with NULLs become apparent when you try to figure out what to do with those unknown values when you operate on them.

It's easy enough to see that when you perform an arithmetic operation on an unknown value, the result can only be unknown. You can double all the advances, for example, and the advance for Net Etiquette is still NULL. You can subtract the NULL advance from the advance for The Psychology of Computer Cooking, and you certainly can't say that the answer is zero: It is still NULL.

But what if you want to find the average of all advances? Here's what you'd do:

SQL

select avg(advance)
from titles
avg(titles.advance)
===================
            5962.50
[1 row]

Let's check up on SQL ...