Why does base_salary + bonus come back NULL for some rows, and how do you fix it?

## Propagation is the default SQL scalar expressions are **NULL-propagating**: if an input is absent, the result is absent. ```sql SELECT 100 + NULL; -- NULL SELECT 100 * NULL; -- NULL SELECT NULL / 2; -- NULL SELECT ABS(NULL); -- NULL ``` The reasoning is the same one behind three-valued logic. NULL marks a value that is missing, and there is no defensible number to report for "one hundred plus something unknown". Rather than guess, SQL returns the marker again. This is why a payroll query like ```sql SELECT employee_id, base_salary + bonus AS total_comp FROM payroll; ``` returns a `total_comp` of NULL for exactly the employees who have no bonus row — usually the ones a reader is most interested in, and usually rendered in a report as a blank cell or a zero, depending on the client. ## COALESCE `COALESCE(v1, v2, ...)` evaluates its arguments in order and returns the first that is not NULL; if all are NULL, it returns NULL. It takes two or more arguments and they must be of comparable types, since the result has a single declared type derived from them. ```sql SELECT employee_id, base_salary + COALESCE(bonus, 0) AS total_comp FROM payroll; ``` The standard defines `COALESCE` as pure shorthand for a `CASE` expression: ```sql CASE WHEN v1 IS NOT NULL THEN v1 WHEN v2 IS NOT NULL THEN v2 ELSE NULL END ``` Two practical consequences follow from that definition. First, once a non-NULL argument is found the later ones do not need to be evaluated, so putting an expensive expression last is sensible. Second, the substitute value must make sense as the column's type — `COALESCE(bonus, 'none')` mixing a number with a string is a type error, not a clever default. A common mistake is reaching for `COALESCE` where the real requirement is a *filter*. `COALESCE(bonus, 0) > 0` and `bonus > 0` select the same rows; the difference only matters when the value flows into the output or into arithmetic. ## Comparison expressions are propagating too The same rule shows up on the predicate side, where it becomes three-valued logic: `bonus > 0` is UNKNOWN when `bonus` is NULL, and `WHERE` discards the row. Expression propagation and predicate UNKNOWN-ness are one rule wearing two hats — absent input, undetermined output. This matters when the expression is computed *and then* filtered: ```sql -- rows with a NULL bonus are filtered out here... WHERE base_salary + bonus > 100000 -- ...but retained here, with the missing bonus counted as zero WHERE base_salary + COALESCE(bonus, 0) > 100000 ``` ## String concatenation In standard SQL, `||` propagates like arithmetic does: `'Ms. ' || NULL` is NULL, so building a display name from nullable parts can wipe out the whole string. ```sql -- NULL for anyone without a middle name SELECT first_name || ' ' || middle_name || ' ' || last_name FROM people; -- Always produces a string SELECT first_name || COALESCE(' ' || middle_name, '') || ' ' || last_name FROM people; ``` Note the nesting in the fixed version: the separator space is inside the `COALESCE` argument, so a missing middle name removes its space too rather than leaving a double space. Engines differ here — Oracle treats a NULL operand of `||` as an empty string, and various engines offer concatenation *functions* with their own NULL rules — so if a query must run on more than one engine, be explicit with `COALESCE` rather than relying on the engine's default. ## Is zero the right answer? The technical fix is easy; the judgment is whether the substitution is truthful. Replacing a missing bonus with 0 asserts that the employee received no bonus. If NULL actually means "payroll has not loaded bonuses yet", that assertion is false, and a NULL total is the more honest output — it signals "cannot compute" rather than fabricating a smaller number. Good candidates say this out loud: decide what the NULL means in the domain first, then choose between defaulting it, filtering the row out, or letting the NULL travel to the consumer. A related discipline is where the defaulting lives. Pushing `COALESCE` into a view or a shared query means every downstream consumer inherits the assumption, which is convenient until one consumer needs to tell "zero" from "missing" and can no longer do so. ## Interview framing State the rule (any NULL operand makes the whole scalar expression NULL), give the fix (`COALESCE` with a type-compatible default), and add the caveat that the default is a modelling decision, not just a syntax fix.