In this lesson, you will learn how to prevent NULL values from breaking your mathematical calculations and summary metrics.
The Problem with NULLs in Math
In SQL, the golden rule of arithmetic is that any number plus NULL equals NULL. If you try to calculate a total—like adding a salesperson's base salary to their commission—and the commission value is NULL, the entire result becomes NULL. This happens because the database considers NULL to be "unknown," and it cannot perform a calculation with an unknown quantity.
Strategy 1: The COALESCE Function
To fix this, we use the COALESCE function. It acts like a safety net: it looks at a list of values and returns the first one that is not NULL. If we want to treat missing commission values as zero, we write it like this:
SELECT employee_id,
base_salary + COALESCE(commission, 0) AS total_compensation
FROM payroll;
In this example, if commission is NULL, COALESCE replaces it with 0 before the addition happens, ensuring your math stays accurate.
Strategy 2: Aggregation Behavior
When performing aggregations like SUM() or AVG(), SQL handles NULL values differently by design. Aggregate functions simply ignore NULL values entirely. For example, if you have a list of test scores—90, NULL, and 80—the SUM() function will calculate 170. It treats the missing data as if it doesn't exist rather than as a zero.
If your business logic requires that a missing value must be counted as a zero (for example, if a NULL test score represents a failing grade), you must use COALESCE inside your aggregate function:
-- This treats all NULLs as 0 before calculating the average
SELECT AVG(COALESCE(test_score, 0)) AS average_score
FROM classroom_results;
By wrapping your column in COALESCE, you ensure the database includes the "missing" entries in your math, preventing your calculations from skipping over rows that should have been accounted for.
Key Takeaway: Use COALESCE to transform NULL into a specific number before performing arithmetic, as NULL otherwise nullifies any mathematical operation it touches.