Fivetran to Acquire HVR; Announces $565M in Series D. Learn More.

HomeBlog
A Friendly Reminder: One Plus NULL Equals NULL
Fivetran
HomeBlog
A Friendly Reminder: One Plus NULL Equals NULL

A Friendly Reminder: One Plus NULL Equals NULL

Whenever you perform arithmetic across SQL columns, take care to handle NULL values appropriately.

By Charles Wang, June 6, 2019

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:

itempricetaxhazmat_fee
oat_bran1.000.06NULL
scalpel5.50NULLNULL
mercury_fulminate200.00NULL35.00
cesium_iodide450.0045.0035.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:

itempricetaxhazmat_feetotal_cost
oat_bran1.000.06NULLNULL
scalpel5.50NULLNULLNULL
mercury_fulminate200.00NULL35.00NULL
cesium_iodide450.0045.0035.00530.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:

itempricetaxhazmat_feetotal_cost
oat_bran1.000.06NULL1.06
scalpel5.50NULLNULL5.50
mercury_fulminate200.00NULL35.00235.00
cesium_iodide450.0045.0035.00530.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.

Start analyzing your data in minutes, not months

Launch any Fivetran connector instantly.