How to Find All Employees Under Each Manager in SQL

Have you ever been tasked with building an organizational chart – i.e. finding the employees under each manager in a company? In SQL, you can do that easier than you think! This article will show you how to get the answer using a recursive query.

In the business world, it’s a very common task to list all the subordinate employees for a given manager. Is doing this hard enough to warrant a whole article? If you picture it as an organizational chart, it seems pretty straightforward: find the manager, list all their subordinates, and that’s it. What’s the fuss?

The fuss is that SQL works with tables, not organizational charts. When an organizational chart is translated into a table, it can become a little tricky to achieve what you want.

The technique I’ll be using in this article is rooted mainly in SQL joins and recursive queries. If you need some practice (or more comprehensive explanations), check out our SQL JOINs and Recursive Queries courses.

Let’s first examine how an organizational chart is usually shown in a table suitable for SQL querying.

Employee Table

We’ll use a table named employee that has the following columns:

• employee_id: The ID of the employee.
• first_name: The first name of the employee.
• last_name: The last name of the employee.
• manager_id: The ID of the employee’s manager.

In this example, there are ten employees. Let’s examine the records:

Nothing complicated here, just a list of employees. The key feature is the column manager_id, which contains the ID of every employee’s boss. For example:

Nancy Young (employee_id 4529) has a boss. Her boss’s ID is 4125, shown in the column manager_id. If you search for that value in the column employee_id, you’ll find out this is the ID of Mafalda Ranieri.

Now, let’s take a look at Ms. Mafalda Ranieri:

There’s a NULL value in the manager_id column, which means Mafalda Ranieri has no manager above her – i.e. she’s the president of the company.

Logic says whenever there’s a NOT NULL value in the column manager_id, that employee reports to a manager. Of course, it’s possible an employee could be a manager and also have someone above them in the hierarchy.

Count All Employees Under Each Manager

Let’s do this ‘warm-up’ exercise. Using only the table employee, how would you count all the employees under each manager? Here’s how you could do it, with self-joining the table being the magical trick:

SELECT	
sup.employee_id,
	sup.first_name,
	sup.last_name,
	COUNT (sub.employee_id) AS number_of_employees
FROM employee sub 
JOIN employee sup 
ON sub.manager_id = sup.employee_id
GROUP BY sup.employee_id, sup.first_name, sup.last_name;

If you’re not too familiar with self-joins, see this article that explains self-joins with examples.

When self-joining the table employee, I’ve created two aliases so it’ll be easier to follow what I’m doing. One table will have the alias sub (for subordinate employee); the other one’s alias is sup (for superior employee). Since I’m looking for managers’ analytical data, I’ve told the query to return the columns employee_id, first_name, and last_name from the table sup.

The code then counts the number of employees using the COUNT() function on the sub.employee_id column. You’d get the same result if you used sup.employee_id instead. I just wanted to use the sub table to explain this step in the following way: “Aha, table sub is for subordinate employees, so, logically, I count the number of subordinates in that table”.

As I said earlier, I self-join the employee table using two aliases. The join condition is sub.manager_id = sup.employee_id. It seems logical, as the value in the column manager_id is the manager’s ID as an employee and will naturally be in the column employee_id.

I’ve used the aggregate function COUNT(), so I have to group the result by the columns employee_id, first_name, and last_name.

When you run the code, it’ll return this result:

The table shows four managers and the number of their subordinates (number_of_employees).

Find All Direct Subordinates Under Each Manager

Finding direct subordinates is similar to the problem above. It’s logical to think that if I’ve found the number of subordinate employees, then I could find their names too. To tell you the truth, the solution for this exercise is only a variation of the previous code:

SELECT	
sub.employee_id AS subordinate_id,
	sub.first_name AS subordinate_first_name,
	sub.last_name AS subordinate_last_name,
	sup.employee_id AS superior_id,
	sup.first_name AS superior_first_name,
	sup.last_name AS superior_last_name
FROM employee sub 
JOIN employee sup 
ON sub.manager_id = sup.employee_id
ORDER BY superior_id;

The principle here is the same; the table employee is self-joined where the column sub.manager_id = sup.employee_id. To do that, I again use the aliases sub and sup.

First, I take the columns sub.employee_id, sub.first_name, and sub.last_name. I rename them so that they reflect the fact that the data relates to the subordinate employees. I do the same with these columns for the sup table, but this time the name reflects the employee’s position as a superior.

Finally, the result is ordered by the column superior_id:

It seems the table shows what I intended; we have the names of employees and their managers’ names. But my ex-auditor eagle eye notices there are only nine employees, but I know there are ten employees in the company. Why is that? The reason is Mafalda Ranieri; she’s the president of the company, so she doesn’t have a superior and she’s not anyone’s subordinate. This is represented as a NULL value in the column manager_id:

She’s missing from the query result because of the nature of the self-join; I used JOIN (meaning, INNER JOIN). This type of join doesn’t return rows with NULL values. If you’d like to see those rows too in your query result, you’ll have to use LEFT JOIN instead of JOIN; everything else in the query stays the same. This part of the code will look like this:

...
FROM employee sub LEFT JOIN employee sup ON sub.manager_id = sup.employee_id
...

Running the changed code will return all employees, even those who don’t have a superior:

However, this result can still be unsatisfying because it’ll return only direct subordinates. In more complex organizations, some managers have direct subordinates who manage other employees. Those employees have a direct boss, but they also respond to their boss’s boss. Is it even possible to choose a manager and get the list of all their direct and indirect subordinates in SQL? You bet it is – with a little help from our friend, the recursive query.

If you’re not very familiar with the concept of recursive queries, it might be advisable to read this article explaining how the recursive queries work before continuing.

Find Both Direct and Indirect Subordinates Under Each Manager

In the table employee, we have one employee named Nancy Young. She has only one superior, the president of the company. Nancy is a top executive; naturally, she has subordinates. Her subordinates also have their own subordinates. What I would like to show you is how you get all the subordinates, both direct and indirect, of a manager – Nancy Young in this case. I’ll be doing this by using a recursive CTE.

The code that’ll get me what I want is as follows:

WITH RECURSIVE subordinate AS (
	SELECT	employee_id,
			first_name,
			last_name,
			manager_id,
			0 AS level
	FROM employee
	WHERE employee_id = 4529

	UNION ALL

	SELECT	e.employee_id, 
			e.first_name,
			e.last_name,
			e.manager_id,
			level + 1
	FROM employee e 
JOIN subordinate s 
ON e.manager_id = s.employee_id
)

SELECT	
s.employee_id,
	s.first_name AS subordinate_first_name,
	s.last_name AS subordinate_last_name,
	m.employee_id AS direct_superior_id,
	m.first_name AS direct_superior_first_name,
	m.last_name AS direct_superior_last_name,
	s.level
FROM subordinate s 
JOIN employee m 
ON s.manager_id = m.employee_id
ORDER BY level;

In standard SQL syntax, if your intention is to have a recursive CTE, you start writing the code by writing WITH RECURSIVE. Important notice! If you’re using SQL Server, this won’t work; you’ll need to write the above query without RECURSIVE. In other words, the first line of the code has to look like this:

WITH subordinate AS (
...

Now, let’s go back to explaining what the above code does. It creates a recursive CTE named subordinate. The first SELECT statement in this CTE will return the columns from the table employee. I’ve also added a new column, level. Nancy Young will be the level 0 manager; you’ll see the purpose of this column later on. Since Nancy Young’s employee ID is 4529, I’ve added that ID in the WHERE clause.

I want this SELECT statement result to be “merged” with the result of the second SELECT statement. To do that, both SELECT statements have to have the same number of columns in the result. For UNION ALL to make sense, I’ll put the columns employee_id, first_name, last_name, and manager_id in the second SELECT.

The last column in the statement will be the value of level from the first SELECT statement (which is 0). We’ll add a 1 to this value with every recursion, which will return the levels of hierarchy. This will be useful for sorting data and for easily following who is whose manager. I’ve joined the table employee with the CTE itself. I’m treating the CTE as a table (which it is), giving it an alias and joining both tables on e.manager_id = s.employee_id.

Finally, I come to the SELECT statement outside the CTE. In this part of the code, I’ve joined the CTE itself with the table employee. It first selects the columns employee_id, first_name, and last_name from the CTE, as I’m using the CTE as the source for the subordinates data. I’ve also renamed those columns accordingly, to avoid confusion.

The next step is to select the same columns from the table employee; these columns will contain the data of the employees’ direct managers.

Finally, the result is ordered by the column level, which will sort employees hierarchically. Such a long query could return only a nice result; take a look for yourself:

The table shows us all Nancy Young’s direct and indirect subordinates along with their direct superiors. Examine the table and you’ll see Nancy Young has two direct subordinates: Jakub Hrabal and Sandra Rajkovic. Jakub has no subordinates, but Sandra has two of them, Moira Areas and Jon Nilssen.

See? It’s not that easy to get something you might think will be straightforward. Luckily, recursive queries come in very handy for times like these.