Helix Systems' HR team needs to identify managers for an org-chart audit.
Write a query to return the employee ID and name for every employee who has at least one direct report on record.
Assumptions:
- The
employeestable contains every active and former employee at Helix Systems. - An employee is a manager if at least one other employee's
manager_idmatches theirid. - The check is against the same table — both alias instances of
employeescome from the same source.
Output:
- One row per manager, with columns
idandname.
Schema · hr 4 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
id,
name
FROM
employees e
WHERE
EXISTS (
SELECT
1
FROM
employees r
WHERE
r.manager_id = e.id
)The shape
The check is whether any other row in the same employees table has its manager_id pointing back at the current employee's id. EXISTS with the table aliased twice expresses that self-reference directly. One alias for the candidate manager, another for the potential report.
Clause by clause
SELECT id, name FROM employees ereads every employee at Helix Systems and aliases this instance ase.erepresents the candidate manager — the outer row whose id might appear as someone else'smanager_id.WHERE EXISTS (SELECT 1 FROM employees r WHERE r.manager_id = e.id)is the correlated test.ris a second alias of the same table, representing a potential direct report. For each outere.id, PostgreSQL looks inside the table for any row wherer.manager_idmatches. If one is found, the outer row passes. If none is found, the row drops.- The two aliases are what make the self-reference unambiguous. Without them, the inner
WHERE manager_id = idwould be checking a row against itself.e.idandr.manager_idare values from two row contexts of the same table; the aliases give each context its own name.
Why this and not IN (SELECT manager_id FROM employees)
For this prompt IN produces the same result and is shorter to type. EXISTS reads more naturally on hierarchical "is X a parent of anything" questions because the outer question maps directly onto the inner phrasing "does a row exist where manager_id = this employee's id."
The quieter advantage: manager_id is NULL for top-level employees, so the negation NOT IN (SELECT manager_id FROM employees) would silently return zero rows. NOT EXISTS doesn't have that hazard. Picking EXISTS for the positive case keeps the shape consistent with the safer negation.
You practiced EXISTS against the same table the outer query is reading from. The recurring shape: hierarchical "is X a manager / parent / referrer" tests use a correlated EXISTS that joins the table to itself through the foreign-key column.
