A Friendly Reminder: One Plus NULL Equals NULL
With the unveiling of Fivetran Transformations, you now have even more reason to perform arithmetic operations across columns within your data warehouse. Unfortunately, a case may arise where a value is missing, but its absence should not invalidate an entire record.
Suppose you have sales records from a variety of jurisdictions and product categories. Not all jurisdictions have sales taxes and some product categories come with mandatory hazardous materials fees:
If you tried to calculate the total cost for each record using the following query:
SELECT item, price, tax, hazmat_fee, price + tax + hazmat_fee as total_cost FROM sales.records
Your results will look like so:
What you really want in this case is to default to a value of 0 whenever a
NULL is encountered. You can do this using the COALESCE function, which takes the first non-NULL argument from a list):
SELECT item, price, tax, hazmat_fee, price + COALESCE(tax, 0) + COALESCE(hazmat_fee, 0) as total_cost FROM sales.records
Remember - when adding across columns, one plus NULL equals NULL! This is true for subtraction, multiplication, and division, too.