A friendly reminder: One plus NULL equals NULL

Whenever you perform arithmetic across SQL columns, take care to handle NULL values appropriately.
Charles Wang
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:

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 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

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 for free

Join the thousands of companies using Fivetran to centralize and transform their data.

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
Get demo
Blog

A friendly reminder: One plus NULL equals NULL

June 6, 2019
A friendly reminder: One plus NULL equals NULL
Charles Wang
,
Overview
Topics
No items found.
Share
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 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

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.

No items found.

Related blog posts

No items found.
No items found.
No items found.

Start for free

Join the thousands of companies using Fivetran to centralize and transform their data.

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
Get demo
Product
Extract + loadEmbeddedCDC Database ReplicationTransformationsSecurityConnectorsPricing
Solutions
Marketing analyticsSales + customer successFinance analyticsAmazon Web ServicesAzureDatabricksGoogle CoudSnowflakeCase studies
Resources
BlogDocumentationFivetran CommunityEventsWebinarsResource centerStatusSupport portal
Company
About FivetranCultureCareersNewsContact usLegalPrivacy policyCookie SettingsTerms of service
From the blog
Blend data pipelines into your app with co-branded Connect Cards
Blend data pipelines into your app with co-branded Connect Cards
Read more
The benefits of working with Fivetran solution architects
The benefits of working with Fivetran solution architects
Read more
New and upcoming connectors and data models
New and upcoming connectors and data models
Read more
Follow us
©2022 Fivetran Inc.

*dbt Core is a trademark of dbt Labs, Inc. All rights therein are reserved to dbt Labs, Inc. Fivetran Transformations is not a product or service of or endorsed by dbt Labs, Inc.