Add Columns With NULL in SQL Server: ISNULL, COALESCE or SUM

To add columns with NULL in SQL Server, decide first what an empty value means. Any addition that touches a NULL returns NULL, so one empty cell wipes out the whole sum. ISNULL, COALESCE and SUM each fix that, and each answers a slightly different question.

Why One NULL Wipes Out the Sum

NULL means unknown, not zero. When you add columns with NULL in them, you add an unknown number to 1 and 2. The result is unknown, so the plus operator returns NULL. A fruit stall shows the problem well. Each shelf has three bowls, and a NULL means nobody counted that bowl.

The demo creates a database named NullSumDemo with one table of five shelves. Shelf d has no counts at all. Run it on a test server.

IF DB_ID(N'NullSumDemo') IS NULL CREATE DATABASE NullSumDemo;
GO
USE NullSumDemo;
GO
DROP TABLE IF EXISTS dbo.BowlCounts;
CREATE TABLE dbo.BowlCounts (
    ShelfCode  varchar(2) NOT NULL PRIMARY KEY,
    LeftBowl   int NULL,
    MiddleBowl int NULL,
    RightBowl  int NULL
);
INSERT INTO dbo.BowlCounts (ShelfCode, LeftBowl, MiddleBowl, RightBowl)
VALUES ('a', 1, 2, NULL),
       ('b', 1, 2, 3),
       ('c', NULL, NULL, 3),
       ('d', NULL, NULL, NULL),
       ('e', 1, NULL, 3);

The next query adds the three bowls three ways. The first column uses the plain plus operator. The other two columns are explained below.

SELECT s.ShelfCode,
       s.LeftBowl + s.MiddleBowl + s.RightBowl AS PlainSum,
       ISNULL(s.LeftBowl, 0) + ISNULL(s.MiddleBowl, 0) + ISNULL(s.RightBowl, 0) AS IsNullSum,
       v.Total AS ApplySum
FROM dbo.BowlCounts AS s
CROSS APPLY (SELECT SUM(x.n) AS Total
             FROM (VALUES (s.LeftBowl), (s.MiddleBowl), (s.RightBowl)) AS x(n)) AS v
ORDER BY s.ShelfCode;

The plain sum returns NULL for four of the five shelves. Only shelf b, with all three counts, gets an answer.

ISNULL and COALESCE Treat NULL as Zero

ISNULL(LeftBowl, 0) swaps a NULL for zero before the plus runs. COALESCE does the same and accepts more than two arguments. Either one gives 3, 6, 3, 0 and 4 for the five shelves. That is the shortest way to add columns with NULL, and it is the answer the old question wanted.

Use them when a NULL stands for zero. If an empty bowl was counted, zero is correct. If a NULL means that nobody counted, zero is a guess. Shelf d now reports 0 where the honest answer is unknown.

SUM Ignores NULL and Keeps the Unknown

The aggregate SUM skips NULL values. The CROSS APPLY above turns the three columns of each shelf into three rows with VALUES, then sums them. Shelf a returns 3, because SUM added 1 and 2 and skipped the NULL.

Shelf d shows the difference. ISNULL turned it into 0, while SUM returned NULL, because a sum of nothing is unknown. Wrap the result in COALESCE(v.Total, 0) when you want zero for a shelf with no counts. The choice is yours, and now it is a visible one.

The same rule applies down a column. This query sums each bowl across all shelves.

SELECT SUM(LeftBowl) AS LeftTotal, SUM(MiddleBowl) AS MiddleTotal, SUM(RightBowl) AS RightTotal
FROM dbo.BowlCounts;

The Messages tab also prints a notice: Warning: Null value is eliminated by an aggregate or other SET operation. It is message 8153, a notice and not an error. It tells you that SUM skipped at least one NULL, which matters when the totals matter.

What the Choice Costs

The original question was how to make the addition faster. The script below builds a table of 2,000,000 rows with many NULLs. It then adds columns with NULL three ways. Each query returns the same grand total of 14,499,994. They differ only in the CPU time reported in the Messages tab. The table shows the timings from my test server.

DROP TABLE IF EXISTS dbo.BigCounts;
CREATE TABLE dbo.BigCounts (
    CountID    int NOT NULL PRIMARY KEY,
    LeftBowl   int NULL,
    MiddleBowl int NULL,
    RightBowl  int NULL
);
INSERT INTO dbo.BigCounts (CountID, LeftBowl, MiddleBowl, RightBowl)
SELECT n,
       CASE WHEN n % 3 = 0 THEN NULL ELSE n % 10 END,
       CASE WHEN n % 4 = 0 THEN NULL ELSE n % 7 END,
       CASE WHEN n % 5 = 0 THEN NULL ELSE n % 5 END
FROM (SELECT TOP (2000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
      FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b CROSS JOIN sys.all_objects AS c) AS x;
GO
SET STATISTICS TIME ON;
SELECT SUM(CAST(ISNULL(LeftBowl, 0) + ISNULL(MiddleBowl, 0) + ISNULL(RightBowl, 0) AS bigint)) AS GrandTotal
FROM dbo.BigCounts OPTION (MAXDOP 1);
SELECT SUM(CAST(COALESCE(LeftBowl, 0) + COALESCE(MiddleBowl, 0) + COALESCE(RightBowl, 0) AS bigint)) AS GrandTotal
FROM dbo.BigCounts OPTION (MAXDOP 1);
SELECT SUM(CAST(v.Total AS bigint)) AS GrandTotal
FROM dbo.BigCounts AS b
CROSS APPLY (SELECT SUM(x.n) AS Total
             FROM (VALUES (b.LeftBowl), (b.MiddleBowl), (b.RightBowl)) AS x(n)) AS v
OPTION (MAXDOP 1);
SET STATISTICS TIME OFF;

On my test server, ISNULL was the cheapest of the three, and COALESCE came close. The CROSS APPLY took about twice the CPU. A second server with 20 CPUs gave other numbers. There COALESCE took 265 to 312 ms, ISNULL 579 to 688 ms and the CROSS APPLY 1,437 to 1,609 ms. So ISNULL and COALESCE trade places between servers. Both cost less than the CROSS APPLY, which was the slowest on both.

Your timings will differ with the hardware. Run each query a few times and compare the order. The CROSS APPLY costs more because it builds a small row set for every row. It earns that cost when you want the NULL to survive. It also earns it when the table has many more columns to add.

You could argue that repeating ISNULL in every query is the real waste. It is. A computed column holds the expression once, so every query reads the same rule. Put the rule in the table when many queries need it.

ALTER TABLE dbo.BowlCounts ADD AllBowls AS (ISNULL(LeftBowl, 0) + ISNULL(MiddleBowl, 0) + ISNULL(RightBowl, 0));
GO
SELECT ShelfCode, AllBowls FROM dbo.BowlCounts ORDER BY ShelfCode;

A computed column does not make the addition cheaper. It keeps the rule in one place.

What to Remember

To add columns with NULL correctly, pick the meaning first. If a NULL is a zero, use ISNULL or COALESCE on each column. If a NULL is a gap, use SUM over the columns and let an all-empty row stay NULL. Ask the owner of the data which one it is, because the table itself cannot tell you.

If a NULL always means zero, fix the table instead of every query. A NOT NULL column with a DEFAULT of 0 never stores the unknown, so a plain plus works. That only fits a column where nobody needs the difference between zero and not counted.

Check the column totals too. SUM skips NULLs silently, apart from the warning, so a total can look small when many cells are empty. When you finish the demo, drop the database.

USE master;
GO
DROP DATABASE NullSumDemo;

A NULL is not a zero, it is a question nobody has answered yet.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.