Employee Manager Salary

The Question

“Given a single table employees containing id, name, salary, and manager_id, write a query to find all employees who earn more than their direct manager.”

This is the most common entry-level SQL interview question. It tests a single, fundamental concept: Do you know how to execute a Self-Join?

The Solution

Because the manager’s salary and the employee’s salary live in the exact same table, you must trick the database into treating the table as if it were two completely separate tables.

You do this by using table Aliases (e for employee, m for manager).

SELECT 
    e.name AS employee_name,
    e.salary AS employee_salary,
    m.name AS manager_name,
    m.salary AS manager_salary
    
-- Load the table as "e" (The Employee context)
FROM employees e

-- Join the EXACT SAME table as "m" (The Manager context)
INNER JOIN employees m 
    -- The link: The employee's manager_id equals the manager's id
    ON e.manager_id = m.id
    
-- The filter condition
WHERE e.salary > m.salary;

Follow-Up Questions

1. “You used an INNER JOIN. What if the CEO is in the table, and their manager_id is NULL. Will the CEO be included in the output? Will the query crash?”

Your Answer:
“The query will not crash, but the CEO will not be included in the output.
Because INNER JOIN requires a strict mathematical match, e.manager_id (NULL) = m.id evaluates to Unknown/False. The row is discarded.
Since the question specifically asks for people who earn more than their manager, discarding the CEO (who doesn’t have a manager to compare against anyway) is mathematically correct for this logic.”

2. “A developer writes this query using a Subquery in the WHERE clause instead of a JOIN. Is that acceptable?”

SELECT name FROM employees e
WHERE salary > (
    SELECT salary FROM employees m WHERE m.id = e.manager_id
);

Your Answer:
“Yes, this is an acceptable solution using a Correlated Subquery. It will yield the correct answer.
However, historically, Correlated Subqueries were much slower than JOINs because the database had to execute the inner query separately for every single row in the outer table ($O(N^2)$). Modern SQL Query Optimizers are usually smart enough to instantly detect this pattern and rewrite the subquery into a standard JOIN under the hood anyway, so performance is often identical. Still, the JOIN syntax is generally considered cleaner and more idiomatic.”

3. “How would you find employees who earn more than the average salary of their specific department?”

Your Answer:
“I would not use a Self-Join for this. I would use a Window Function.
I would write a CTE that calculates the department average using AVG(salary) OVER(PARTITION BY department_id) as dept_avg. Then, in the outer query, I simply filter WHERE salary > dept_avg. This prevents the need to run an expensive GROUP BY and JOIN cycle.”