181. Employees Earning More Than Their Managers - Detailed Explanation

Problem Statement

We are given a table called Employee with the following columns:

• id: The unique identifier for each employee.

• name: The employee's name.

• salary: The employee's salary.

• managerId: The id of the employee’s manager (this value is NULL if the employee does not have a manager).

The task is to write a SQL query that returns the names of all employees who earn more than their respective managers.

Example

Consider the following sample data in the Employee table:

Explanation:

• Joe (id 1):
  • Manager: Sam (id 3) with a salary of 60000.
  • Since 70000 > 60000, Joe qualifies.
• Henry (id 2):
  • Manager: Max (id 4) with a salary of 90000.
  • Since 80000 < 90000, Henry does not qualify.
• Max (id 4):
  • Manager: Sam (id 3) with a salary of 60000.
  • Since 90000 > 60000, Max qualifies.

Expected Output:
The query should return the names "Joe" and "Max".

Constraints

• Every employee except the top-level manager has a valid managerId.
• The table can have multiple employees reporting to the same manager.
• Some employees might not have a manager (their managerId is NULL), and they should not be considered in the comparison.

Hints

1. Self-Join:
   • Think about how you can compare rows within the same table. In this case, you need to compare an employee’s salary with his/her manager’s salary. This is a perfect scenario for a self-join.
2. Filtering with Conditions:
   • Use a join condition to connect each employee with his/her manager and then apply a WHERE clause to filter out only those cases where the employee’s salary is greater than the manager’s salary.

Approach: Using a Self-Join

Explanation

1. Self-Join Concept:

   • Since both employees and managers reside in the same Employee table, you can perform a self-join.

   • Create two aliases for the table, for example, e (for employee) and m (for manager).

2. Join Condition:
   • Link the two aliases by matching e.managerId with m.id. This effectively pairs each employee with their manager.
3. Filtering Condition:
   • Use a WHERE clause to select only those pairs where the employee’s salary (e.salary) is greater than the manager’s salary (m.salary).
4. Result:
   • Select the employee’s name from the resulting set.

SQL Query (Solution)

SELECT e.name AS Employee
FROM Employee e
JOIN Employee m ON e.managerId = m.id
WHERE e.salary > m.salary;

Walkthrough

1. Alias Assignment:
   • We create two aliases: e for the employees and m for the managers.
2. Join Operation:
   • We join the table on the condition that e.managerId = m.id. This pairs each employee with their corresponding manager.
3. Applying the Filter:
   • The WHERE clause ensures that only those records are selected where the employee's salary exceeds that of the manager.
4. Result Column:
   • We select e.name and alias it as Employee for clarity in the result.

Python Code