Default Zero for Missing Manager — COALESCE and NULLIF in SQL

The problem

The HR portal at Helix Systems renders a staff directory listing every employee alongside their direct supervisor's ID.

Write a query to return each employee's name and a display value for their supervisor ID.

Assumptions:

The employees table has one row per employee with a name and a manager_id.
Executives at the top of the organization have a missing manager_id because they have no supervisor on record.
An employee with a missing manager_id should appear with 0 as their supervisor ID; all other employees should show their recorded manager_id.

Output:

One row per employee, with columns name and manager_id.

Schema · hr 4 tables

departments

id integer

name text

location text

budget numeric

salaries

id integer

employee_id integer

amount numeric

effective_date date

end_date? date

employees

id integer

name text

email text

department_id integer

manager_id? integer

hire_date date

title text

is_active boolean

job_history

id integer

employee_id integer

title text

department_id integer

start_date date

end_date? date

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  name,
  COALESCE(manager_id, 0) AS manager_id
FROM
  employees

The shape

COALESCE(manager_id, 0) swaps in the integer 0 for the executives at the top of the org, the rows where manager_id is missing, and returns the real manager_id for everyone else. The directory ends up with a numeric value in every row.

Clause by clause

SELECT name, COALESCE(manager_id, 0) AS manager_id returns each employee's name and a guaranteed-numeric supervisor ID. COALESCE evaluates its arguments left to right and returns the first one that is not NULL; for the executives, that is the fallback 0.
FROM employees reads every row in the table. Executives stay in the result; they are the reason the substitution exists.

The trap

0 is a presentational sentinel here, not a real supervisor ID. Any consumer that treats this column as a reference into employees.id will read the executives as pointing at a row that does not exist. The substitution is safe for a display column on the directory page but corrupts any downstream query that compares this column back to a real id. When the value will be used for matching rather than display, leave the unsubstituted manager_id in place and let NULL behavior carry the missing executives through.

You practiced COALESCE(column, 0) — substitute a sentinel number for a missing value when the consumer needs a numeric value on every row.

Return each employee's name and a display value for their supervisor ID

The shape

Clause by clause

The trap

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.