Given a company's Employee table, we are tasked to write an SQL query to identify the employees who earn a higher salary than their managers. Each row in the Employee table provides the employee's ID, name, salary, and the ID of their manager.
We'll be examining two SQL solutions to this problem. Each takes a slightly different approach to identify employees earning more than their managers.
The first code snippet uses a nested
SELECT statement in the
WHERE clause. The subquery finds the salary of each employee's manager, and the main query compares this to the employee's salary:
SELECT e.name AS Employee FROM Employee e WHERE e.salary > ( SELECT m.salary FROM Employee m WHERE m.id = e.managerId )
With a runtime of 746ms, this solution beats 76.97% of other solutions. This method can be a bit slower due to the nested subquery which executes for every employee.
The second solution uses a
JOIN clause to combine rows from the Employee table where the employee's
managerId matches the manager's
id. It then uses a
WHERE clause to compare the salaries:
SELECT e1.name AS Employee FROM Employee e1 JOIN Employee e2 ON e1.managerId = e2.id WHERE e1.salary > e2.salary
This solution has a faster runtime of 662ms, beating 94.99% of other solutions. By using
JOIN, we effectively reduce the number of operations as we avoid executing a subquery for each row.
While both solutions provide the correct answer, the second one performs better due to its use of
JOIN to avoid multiple subquery executions. However, it's essential to understand that the choice of method depends on the specific requirements and constraints of your database or problem scenario.
You can find the original problem at LeetCode.
For more insightful solutions and tech-related content, feel free to connect with me on my Beacons page.