|-------------|------------| | employee_id | team_id | |:------------|:-----------| | 1 | 8 | | 2 | 8 | | 3 | 8 | | 4 | 7 | | 5 | 9 | | 6 | 9 | |-------------|------------|
Write an SQL query to find the team size of each of the employees.
SELECT a.employee_id, COUNT(b.team_id) AS team_size
FROM Employee a LEFT JOIN
Employee b
ON a.team_id = b.team_id
GROUP BY a.employee_id
The answer above is correct I am just confused as to why you use a LEFT JOIN on two tables that are the same.
Advertisement
Answer
The best way to understand what is happening is to just view the intermediate table which results from the self join. It looks something like this:
+---------------+-----------+---------------+-----------+ | a.employee_id | a.team_id | b.employee_id | b.team_id | +---------------+-----------+---------------+-----------+ | 1 | 8 | 1 | 8 | | 1 | 8 | 2 | 8 | 3 members | 1 | 8 | 3 | 8 | / | 2 | 8 | 1 | 8 | | 2 | 8 | 2 | 8 | 3 members | 2 | 8 | 3 | 8 | / | 3 | 8 | 1 | 8 | | 3 | 8 | 2 | 8 | 3 members | 3 | 8 | 3 | 8 | / | 4 | 7 | 4 | 7 | - 1 member only | 5 | 9 | 5 | 9 | | 5 | 9 | 6 | 9 | / 2 members | 6 | 9 | 5 | 9 | | 6 | 9 | 6 | 9 | / 2 members +---------------+-----------+---------------+-----------+
The self left join causes each employee record on the left side to be replicated however many times there are other employees on the same team. Then, aggregating by employee and taking the count of records gives the sizes of each employee’s team. In the intermediate table above, we can see this happening in progress.