Total Pay When the Bonus Is NULL

SQLNULL handlingJuniorMidUpdated Nov 11, 2026

Return each employee's name and total pay, which is salary plus bonus. Employees without a bonus have NULL in bonus, and their total pay is their salary.

Schema

CREATE TABLE employees (
  id      BIGINT PRIMARY KEY,
  name    TEXT NOT NULL,
  salary  NUMERIC NOT NULL,
  bonus   NUMERIC  -- NULL when the employee has no bonus
);

Drill This: Your Query First

Answer, Then Reveal

Write the query before you look at the answer.

Skip to Reading

The Correct Query

Option (b)

SELECT name, salary + COALESCE(bonus, 0) AS total_pay
FROM employees;

Any arithmetic with NULL returns NULL, so (a) shows a NULL total for everyone without a bonus. COALESCE(bonus, 0) in (b) swaps the missing bonus for 0 before the addition. (c) applies COALESCE after the addition has already produced NULL, so those employees show a total pay of 0. NULLIF(bonus, 0) in (d) works the other way round: it turns a bonus of 0 into NULL and leaves NULL as NULL, which makes more totals NULL.

Going Deeper

Aggregates skip NULL values entirely. For bonuses of 5000, NULL, and 0, SUM(bonus) is 5000 and COUNT(bonus) is 2, while AVG(bonus) is 2500 because it divides by the 2 non-NULL rows; AVG(COALESCE(bonus, 0)) gives 1666.67 across all 3 employees. SUM over a set with no non-NULL values returns NULL, so wrap it as COALESCE(SUM(bonus), 0) when a report needs a number.

The Options That Fail

The explanation above says why each of these breaks.

Option (a)

SELECT name, salary + bonus AS total_pay
FROM employees;

Option (c)

SELECT name, COALESCE(salary + bonus, 0) AS total_pay
FROM employees;

Option (d)

SELECT name, salary + NULLIF(bonus, 0) AS total_pay
FROM employees;