Employees with Their Managers — Self-Joins in SQL

The problem

Helix Systems' HR team is building an employee directory and needs each person listed alongside their direct manager.

Write a query to return the employee name and manager name for every employee who has a manager on record.

Assumptions:

The employees table contains every active and former employee at Helix Systems.
The manager_id column points to another row in employees — the employee's direct manager.
Top-of-hierarchy executives have a missing manager_id and will not appear in the result.

Output:

One row per employee with a manager, with columns employee_name and manager_name.

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
  e.name AS employee_name,
  m.name AS manager_name
FROM
  employees e
  JOIN employees m ON e.manager_id = m.id

The shape

The employee name and the manager name both live in employees, so the query reads the same table twice under two different aliases and matches each employee's manager_id to the other alias's id.

Clause by clause

SELECT e.name AS employee_name, m.name AS manager_name pulls name from each of the two aliased instances. e.name is the employee's name; m.name is the manager's name from the same table, just a different row.
FROM employees e reads the table in its employee role.
JOIN employees m ON e.manager_id = m.id reads the same table again in its manager role and links each employee row to the row whose id matches that employee's manager_id. Sarah Chen, who has no manager_id, never matches anything in m and drops out of the result, which is what the prompt asks for.

The trap

The alias is not optional here. Without two distinct aliases, every reference to name, id, or manager_id is ambiguous between the two instances and PostgreSQL refuses to run the query. The fix is mechanical: pick two short labels that name the roles (e for employee, m for manager) and use them on every column reference inside the SELECT list and the ON clause.

You practiced a self-join: joining a table to itself by giving each instance its own alias. The recurring shape: any time a column references another row in the same table (manager_id, parent_id, replied_to_id), a self-join is the assembly mechanism.

Return the employee name and manager name for every employee who has a manager on record

The shape

Clause by clause

The trap

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