Table employees(id int primary key, name text, dept text, salary int, manager_id int references employees(id)); manager_id is null for staff with no manager.
Return every employee's name as employee and their manager's name as manager. Employees with no manager should show - as the manager. Order by employee id.
Constraints
- Write one SELECT statement for PostgreSQL 16.
- Output columns and row order must match exactly.
Input and output
- Input
- The setup script in the test case creates and fills the tables described above.
- Output
- Columns: employee, manager.
Example
Input
create table employees (id int primary key, name text not null, dept text not null, salary int not null, manager_id int references employees(id));
insert into employees values (1,'Leela Bose','Tech',150000,null),(2,'Rahul Joshi','Tech',90000,1),(3,'Zoya Khan','Tech',70000,2),(4,'Manav Gill','HR',60000,null);Output
Leela Bose|-
Rahul Joshi|Leela Bose
Zoya Khan|Rahul Joshi
Manav Gill|-Leela and Manav have no manager; Rahul reports to Leela and Zoya to Rahul.
Show hints
- Join the table to itself under two aliases.
- Use LEFT JOIN so employees without a manager are kept, and COALESCE to replace NULL.
Show the explanation
A self join treats one table as two: employees e for the worker and employees m for the manager, matched on e.manager_id = m.id. It must be a LEFT JOIN, otherwise the people at the top (manager_id NULL) disappear. coalesce(m.name, '-') turns the missing manager name into a dash.
Original UniCircle question. Last reviewed 22 Oct 2026.