Write a query to find employees whose salary is higher than their manager's salary?
Interview preparation resource from Gate Smashers.
Use a self-join on the Employees table to compare each employee’s salary with their manager’s salary. The query joins Employees to itself on e.manager_id = m.employee_id and filters where e.salary > m.salary (see SQL block below).
| employee_id | employee_name | department | salary | manager_id |
|---|---|---|---|---|
| 101 | Aarav | Engineering | 95000 | 105 |
| 102 | Meera | Engineering | 72000 | 105 |
| 103 | Kabir | Sales | 65000 | 106 |
| 104 | Isha | Sales | 92000 | 106 |
| 105 | Rohan | Engineering | 90000 | NULL |
| 106 | Neha | Sales | 88000 | NULL |
SELECT e.employee_name, e.salary, m.employee_name AS manager_name, m.salary AS manager_salary
FROM Employees AS e
JOIN Employees AS m ON e.manager_id = m.employee_id
WHERE e.salary > m.salary;Interview‑ready answer (query)
Compare each employee to their manager by self‑joining Employees and selecting employees whose salary is greater than their manager’s salary. The exact SQL is provided in the code block below.
- Selected columns: e.employee_name, e.salary, m.employee_name AS manager_name, m.salary AS manager_salary
How it works
This uses a self‑join: the Employees table is aliased twice
once for the employee row (e) and once for the manager row (m). The join condition e.manager_id = m.employee_id pairs each employee with their manager row in the same table. After the join, the WHERE clause e.salary > m.salary keeps only those employee rows whose salary exceeds the matched manager’s salary.
- Join condition: e.manager_id = m.employee_id — links an employee to their manager row.
- Filter: WHERE e.salary > m.salary — returns only employees paid more than their manager.
Behavior for employees without managers
An INNER JOIN is used in this query, so employees with a NULL manager_id (no manager) are excluded automatically because there is no matching manager row. If you wanted to include those employees for inspection, use a LEFT JOIN instead and handle NULL manager rows in the WHERE or HAVING logic.
- Current behavior: Employees with no manager are excluded (inner join).
- Alternative: Use LEFT JOIN to include employees without managers for additional checks.
Sample data (reference)
The Employees table sample used to illustrate this query is shown below.
- Note: Do not repeat the full dataset here — the table block below contains the exact rows used.