Find Employees Who Manage Someone

A manager is defined by the data, not the title

In this employees table, 37 people work here and 35 of them have a manager_id. Those 35 values point at only 12 distinct people, so 12 employees manage someone.

It's tempting to filter on the title instead:

SELECT id, first_name, last_name, title
FROM employees
WHERE title LIKE '%Manager%';

That returns 6 people, and 4 of them (Lisa Anderson, Matthew Martin, Michelle Perez and Eric Evans) have no direct reports at all. Meanwhile Melissa Cook, a UX Designer, has one. The relationship is the source of truth, and the label is just text.

Approach 1: IN with a subquery

The shortest answer asks whether each id appears in the manager_id column:

SELECT id, first_name || ' ' || last_name AS name, title
FROM employees
WHERE id IN (SELECT manager_id FROM employees)
ORDER BY id;

It returns the 12 managers, from Alice Chen to Melissa Cook. The subquery's list contains NULLs (from the two CEOs, who have no manager), but that's harmless for IN: a NULL in the list can't create a match.

Approach 2: EXISTS

EXISTS asks the same question row by row:

SELECT COUNT(*)
FROM employees e
WHERE EXISTS (
  SELECT 1 FROM employees r WHERE r.manager_id = e.id
);

This counts 12 as well. Many engines plan IN and EXISTS the same way for a query like this, so pick whichever reads better to you. The EXISTS vs IN question covers where they differ.

Approach 3: a self join, with a catch

A self join is the version that can also count reports, which is why the practice question asks for it. The catch is duplicates. Each manager appears once per person reporting to them, so this join returns 35 rows, not 12:

SELECT COUNT(*)
FROM employees m
JOIN employees e ON e.manager_id = m.id;

Add DISTINCT on the manager, or group by the manager, to get back to one row each:

SELECT COUNT(*)
FROM (
  SELECT DISTINCT m.id
  FROM employees m
  JOIN employees e ON e.manager_id = m.id
);

That gives 12 again. Grouping is better when you also want the count, as in the solution above: Alice Chen has 6 reports, Robert Martinez and Rachel Phillips have 2, Melissa Cook has 1, and the other eight have 3 each. If the self join itself is new to you, start with Employee and Manager Self Join.

The reverse question and the NOT IN trap

A common follow up is "now find the employees who manage nobody". Flipping IN to NOT IN looks right:

SELECT COUNT(*)
FROM employees
WHERE id NOT IN (SELECT manager_id FROM employees);

It returns 0. The subquery's list includes NULL, and id NOT IN (..., NULL) is never true, because the database can't rule out that the unknown value equals the id. Filter the NULLs out, or use NOT EXISTS:

SELECT COUNT(*)
FROM employees e
WHERE NOT EXISTS (
  SELECT 1 FROM employees r WHERE r.manager_id = e.id
);

Both fixes count 25, which matches 37 employees minus 12 managers. Don't confuse this with Employees Without a Manager, which looks at the other end of the relationship: people whose own manager_id is NULL.

What to say in the interview

Start by defining a manager as "someone whose id appears in manager_id", then write the IN version. Mention that a self join needs DISTINCT or GROUP BY, and offer the grouped version if they want report counts. If they ask for the opposite list, explain why NOT IN fails with NULLs before you're asked.