06 Jun 2019 | Analyst Recipe

A Friendly Reminder: One Plus NULL Equals NULL

Charles Wang
Charles Wang
A Friendly Reminder: One Plus NULL Equals NULL
Whenever you perform arithmetic across SQL columns, take care to handle NULL values appropriately.

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:

item price tax hazmat_fee
oat_bran 1.00 0.06 NULL
scalpel 5.50 NULL NULL
mercury_fulminate 200.00 NULL 35.00
cesium_iodide 450.00 45.00 35.00

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:

item price tax hazmat_fee total_cost
oat_bran 1.00 0.06 NULL NULL
scalpel 5.50 NULL NULL NULL
mercury_fulminate 200.00 NULL 35.00 NULL
cesium_iodide 450.00 45.00 35.00 530.00

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

Returning:

item price tax hazmat_fee total_cost
oat_bran 1.00 0.06 NULL 1.06
scalpel 5.50 NULL NULL 5.50
mercury_fulminate 200.00 NULL 35.00 235.00
cesium_iodide 450.00 45.00 35.00 530.00

Remember - when adding across columns, one plus NULL equals NULL! This is true for subtraction, multiplication, and division, too.

Learn more about Fivetran Transformations with a personalized demo or a free trial.

Are You A Data Expert?

Start a free trial today.

Discover the smartest solution for data-driven results.
We have detected that you are using an adblocking plugin in your browser. We don't show ads, but we rely on advertising services, so it might restrict you from completing important functions or seeing important content. Please make sure you whitelist our website in your adblocking plugin.
Fivetran uses cookies to enhance your user experience and improve the quality of our website. Unless you disable cookies, you consent to the placement and use of cookies as described in our Privacy Policy by continuing to use this website.
Adblock Detection