Find Employees Whose Salary is Greater Than Their Manager’s

Introduction

In SQL-based interviews or real-world database management tasks, querying data that involves comparisons between rows in the same table is a common challenge. One such problem is finding employees whose salary is greater than their manager’s salary. This type of query requires a solid understanding of self-joins and filtering based on related rows. In this blog post, we’ll walk through a solution for this problem using MySQL, complete with an explanation of self-joins and sample code.

Problem Statement

You are provided with an Employee table that includes the following columns:

• EmployeeID: Unique identifier for each employee.
• EmployeeName: Name of the employee.
• ManagerID: Identifies the employee’s manager, pointing to EmployeeID.
• Salary: The salary of the employee.

Write an SQL query to find the names of employees whose salary is greater than their manager’s salary.

Sample Table

Let’s assume we have the following Employee table:

EmployeeID	EmployeeName	ManagerID	Salary
1	John	NULL	80000
2	Jane	1	90000
3	Smith	1	70000
4	Emily	2	95000
5	Bob	2	85000

Here, John is the manager of Jane and Smith, and Jane is the manager of Emily and Bob.

Understanding the Solution:

To solve this problem, we need to:

1. Compare each employee’s salary to their manager’s salary.
2. Use a self-join to compare an employee with their respective manager.
3. Filter only those records where the employee’s salary is higher than their manager’s salary.

The main challenge here is using a self-join to link employees with their managers based on the ManagerID.

SQL Query Solution

SELECT e.EmployeeName
FROM Employee e
JOIN Employee m ON e.ManagerID = m.EmployeeID
WHERE e.Salary > m.Salary;

Explanation:

• SELECT e.EmployeeName: This selects the EmployeeName of the employees whose salary is greater than their manager’s.
• FROM Employee e: This selects the Employee table and assigns it an alias e to represent the employees.
• JOIN Employee m ON e.ManagerID = m.EmployeeID: This performs a self-join on the Employee table. The alias m represents the managers, and the join condition is e.ManagerID = m.EmployeeID, meaning we’re linking each employee (e) with their respective manager (m).
• WHERE e.Salary > m.Salary: This filters the results to include only those employees whose salary is greater than their manager’s salary.

Output

For the sample data provided above, the query will return:

Explanation of the Output:

• Jane has a salary of 90,000, which is higher than her manager John’s salary of 80,000.
• Emily has a salary of 95,000, which is higher than her manager Jane’s salary of 90,000.
• Smith and Bob are not included because their salaries are not greater than their respective managers’ salaries.

Best Practices for Writing Similar Queries

• Understand Self-Joins: Make sure you are comfortable with joining a table with itself, as this is a common pattern in relational databases for comparing related rows.
• Use Aliases for Clarity: Using table aliases (e for employee and m for manager) makes the query easier to read and understand.
• Ensure Data Consistency: If your database allows NULL values for ManagerID (indicating a top-level manager without a manager), ensure that your JOIN logic handles these cases properly.

Practice Questions

Here are some related SQL practice problems to help you improve your understanding of self-joins and similar comparisons:

1. Write a query to find the names of managers who have at least one employee with a salary greater than 100,000.
2. Write a query to find all employees who earn more than the average salary of their respective teams.
3. Write a query to find employees who do not have a manager (i.e., top-level managers).
4. Write a query to find the total number of employees whose salary is greater than the salary of their manager.
5. Write a query to find managers whose salary is less than the average salary of their subordinates.

SQL statements

Use the following SQL statement to create the Employee table with columns for EmployeeID, EmployeeName, ManagerID, and Salary:

CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(100),
    ManagerID INT,
    Salary DECIMAL(10, 2),
    FOREIGN KEY (ManagerID) REFERENCES Employee(EmployeeID)
);

Now, let’s insert some sample data into the Employee table:

INSERT INTO Employee (EmployeeID, EmployeeName, ManagerID, Salary) VALUES
(1, 'John', NULL, 80000),
(2, 'Jane', 1, 90000),
(3, 'Smith', 1, 70000),
(4, 'Emily', 2, 95000),
(5, 'Bob', 2, 85000);

Conclusion

Comparing employee salaries with their manager’s salaries is a classic SQL problem that tests your ability to use self-joins and conditional filtering. With a proper understanding of the JOIN clause and how to compare values from the same table, you can easily solve this and similar problems. Mastering these concepts is crucial for building robust queries and analyzing relational data effectively.

Feel free to use this example as a template for similar SQL challenges. Happy querying!