LeetCode 181: Employees Earning More Than Their Managers Solution in SQL Explained

Problem Statement

In LeetCode 181, you’re given an Employee table:

• id (int, primary key)
• name (varchar)
• salary (int)
• managerId (int, foreign key to id, nullable)

Your task is to write an SQL query to find the names of employees whose salaries exceed their managers’ salaries, returning a result table with a single column Employee, ordered arbitrarily. This differs from sequence detection like LeetCode 180: Consecutive Numbers, focusing on hierarchical comparison within a single table.

Constraints

• Table has at least 1 row.
• managerId can be null (no manager) or reference an id.
• Some employees may not have managers.

Example

Let’s see a case:

Copy codeEmployee table:
+----+-------+--------+-----------+
| id | name  | salary | managerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | null      |
| 4  | Max   | 90000  | null      |
+----+-------+--------+-----------+
Output:
+----------+
| Employee |
+----------+
| Joe      |
+----------+
Explanation:
<ul>
<li>Joe (id=1, salary=70000, managerId=3) vs Sam (id=3, salary=60000): 70000 > 60000.</li>
<li>Henry (id=2, salary=80000, managerId=4) vs Max (id=4, salary=90000): 80000 < 90000.</li>
<li>Sam, Max: managerId=null, no comparison.</li>
<li>Only Joe earns more than his manager.</li>
</ul>

This shows we’re comparing employee-manager salary pairs.

Understanding the Problem

How do you solve LeetCode 181: Employees Earning More Than Their Managers in SQL? We need to:

• Match each employee to their manager using managerId and id.
• Compare salary of the employee to the manager’s salary.
• Select the employee’s name where their salary exceeds the manager’s.
• Handle cases where managerId is null (no comparison needed).

For the example, Joe’s salary (70000) > Sam’s (60000), so Joe is included; Henry’s (80000) < Max’s (90000), so Henry is not. We need a query to join or filter efficiently, not a ranking task like LeetCode 178: Rank Scores. We’ll use: 1. Self Join: Clear, efficient—our best solution. 2. Subquery: Alternative approach.

Let’s dive into the best solution.

Best Solution: Self Join Approach

Explanation

Self Join finds employees earning more than their managers by:

• Joining the Employee table with itself (e1 for employees, e2 for managers).
• Matching e1.managerId = e2.id to link employees to managers.
• Filtering where e1.salary > e2.salary.
• Selecting e1.name as Employee.

This ensures O(n) time with indexing on id and managerId, O(1) extra space beyond result, and clarity without subqueries, making it efficient and intuitive.

For the example:

• Join on managerId and id: Joe (70000) > Sam (60000), output "Joe".

Step-by-Step Example

Example: Employee = [1:Joe,70000,3], [2:Henry,80000,4], [3:Sam,60000,null], [4:Max,90000,null]

Goal: Return table with Employee = "Joe".

• Step 1: Self-join Employee as e1 and e2.
• e1: All employees.
• e2: Managers via e1.managerId = e2.id.
• Step 2: Match and compare.
• e1(1,Joe,70000,3) → e2(3,Sam,60000,null): 70000 > 60000 → "Joe".
• e1(2,Henry,80000,4) → e2(4,Max,90000,null): 80000 < 90000 → no match.
• e1(3,Sam,60000,null) → no e2 (null) → no match.
• e1(4,Max,90000,null) → no e2 (null) → no match.
• Step 3: Select matching names.
• Result: "Joe".
• Finish: Return ["Joe"].

How the Code Works (Self Join) – Detailed Line-by-Line Explanation

Here’s the SQL query with a thorough breakdown:

Copy code-- Line 1: Select employee names
SELECT 
    e1.name AS Employee
    -- Name of employee (e.g., "Joe")

-- Line 2: Join Employee table with itself
FROM 
    Employee e1
    -- First instance: employees (e.g., id=1, name=Joe)
JOIN 
    Employee e2
    -- Second instance: managers (e.g., id=3, name=Sam)

-- Line 3: Match employee to manager
ON 
    e1.managerId = e2.id
    -- Link via managerId (e.g., 3=3 for Joe and Sam)

-- Line 4: Filter where employee earns more
WHERE 
    e1.salary > e2.salary
    -- Compare salaries (e.g., 70000 > 60000)

This detailed breakdown clarifies how the self join efficiently identifies high earners.

Alternative: Subquery Approach

Explanation

Subquery finds employees earning more than their managers by:

• Selecting name from Employee (e1).
• Using a subquery to get the manager’s salary via managerId.
• Filtering where e1.salary > (subquery result).
• Handling null managerIds implicitly (no match).

It’s a practical alternative, O(n) time with indexing, O(1) space beyond result, but may involve multiple table scans, making it slightly less efficient than a join in some databases.

For the example:

• Joe: 70000 > (SELECT salary WHERE id=3) = 60000 → "Joe".
• Henry: 80000 < 90000 → no match.

Step-by-Step Example (Alternative)

For the same example:

• Joe: SELECT salary FROM Employee WHERE id=3 → 60000, 70000 > 60000 → "Joe".
• Henry: WHERE id=4 → 90000, 80000 < 90000 → no match.
• Sam: WHERE id=null → null, no comparison → no match.
• Max: WHERE id=null → null, no match.
• Result: "Joe".

How the Code Works (Subquery)

Copy codeSELECT 
    name AS Employee
FROM 
    Employee e1
WHERE 
    e1.salary > (
        SELECT salary 
        FROM Employee e2 
        WHERE e2.id = e1.managerId
    )

Complexity

• Self Join:
• Time: O(n) – join with indexing.
• Space: O(1) – minimal extra space.
• Subquery:
• Time: O(n) – subquery per row (database-dependent).
• Space: O(1) – minimal extra space.

Efficiency Notes

Self Join is the best solution with O(n) time and O(1) space, offering clarity and efficiency with a single pass—Subquery matches time complexity but may involve multiple scans (less efficient in some databases), though equally space-efficient, making it a clear but less preferred alternative.

Key Insights

• Self Join: Direct comparison.
• Subquery: Nested lookup.
• ManagerId: Links hierarchy.

Additional Example

Copy codeEmployee:
| 1 | Alice | 50000 | 2 |
| 2 | Bob   | 40000 | null |
Output:
| Alice |
Explanation: Alice (50000) > Bob (40000).

Edge Cases

• All Null Managers: No matches.
• No Managers: managerId=null → no output.
• Ties: Salary = manager’s → no match.

Both solutions handle these well.

Final Thoughts

LeetCode 181: Employees Earning More Than Their Managers in SQL is a practical hierarchy challenge. The Self Join solution excels with its efficiency and simplicity, while Subquery offers an alternative approach. Want more SQL? Try LeetCode 180: Consecutive Numbers for sequences or explore Python Basics for coding skills. Ready to practice? Solve LeetCode 181 on LeetCode with the example table, aiming for "Joe"—test your skills now!