Find all employees whose salary is strictly greater than their manager's salary. Return employee names.
This self-join problem is a classic in Microsoft interviews, testing your understanding of hierarchical data and self-referential relationships. In organizational databases, employees and managers are stored in the same table, requiring you to join the table to itself. This pattern appears frequently in tree-structured data like org charts, file systems, and category hierarchies. The challenge is conceptualizing the same table playing two different roles in the query.
Core concepts: self-join (joining a table to itself), table aliases for disambiguation (e for employee, m for manager), foreign key relationships within the same table (manager_id references id), WHERE clause for comparison across joined rows, and handling NULL values (top-level managers have no manager_id). Understanding when and how to use self-joins is essential for hierarchical queries.
Microsoft's HR and analytics systems use similar queries to: generate organizational charts and reporting structures, identify compensation anomalies for HR review, calculate span of control metrics (how many direct reports per manager), detect circular reporting relationships in data quality checks, power manager dashboards showing team composition, and analyze promotion patterns across the organization hierarchy.
When tackling this Microsoft problem, the key is to understand the grain of the result. Are you returning one row per user, or one row per category? Always start by identifying your unique join keys and consider if filtered aggregations (CASE WHEN) are more efficient than multiple subqueries.
Be careful with NULL values in your JOIN conditions or aggregate functions. In interview scenarios, datasets often include edge cases like zero-count categories or duplicate entries that can throw off a simple COUNT(*) if not handled with DISTINCT.
Share your approach, optimized queries, or ask questions. Learning from others is the fastest way to master SQL.
SELECT e.name
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salaryThe self-join aliases the employees table twice — once for employees (e) and once for managers (m) — connecting them on e.manager_id = m.id. The WHERE clause then directly compares salaries. This is the most intuitive approach: the join condition establishes the employee-manager relationship, and the filter expresses the business rule plainly. INNER JOIN automatically excludes employees with NULL manager_id (top-level executives), which is the correct behavior. Performant with an index on employees(id) as the primary key and an index on manager_id.
SELECT e.name
FROM employees e
WHERE e.manager_id IS NOT NULL
AND e.salary > (
SELECT m.salary
FROM employees m
WHERE m.id = e.manager_id
)The correlated subquery looks up the manager's salary for each employee row individually. The explicit IS NOT NULL guard prevents the subquery from returning no rows for employees without a manager, which would make the salary comparison evaluate to UNKNOWN and silently exclude those rows anyway — the guard makes the intent explicit. This approach is useful when you need the manager's salary in multiple parts of the query without repeating a join. Performance is generally worse than a self-join unless the optimizer rewrites the subquery as a join.
Both approaches require a lookup by employee ID. An index on employees(id) — typically the primary key — ensures each join or subquery lookup is O(log n). The self-join is almost always more performant because the optimizer plans it as a hash join or merge join, executing a single combined pass over both sides of the table. Correlated subqueries execute once per outer row in naive execution, resulting in O(n log n) compared to O(n) for a join. Modern optimizers (PostgreSQL, SQL Server, MySQL 8+) often automatically rewrite correlated subqueries as joins, but this is not guaranteed in all databases or all query shapes. For very large employee tables, a covering index on (id, salary) allows the manager salary to be retrieved directly from the index without a heap lookup, reducing I/O significantly. An additional index on manager_id further accelerates the join condition lookup.
The most frequent mistake is reversing the comparison direction — ensure e.salary > m.salary, not m.salary > e.salary. Using LEFT JOIN without awareness of its behavioral implications can seem equivalent, but relying on NULL > salary evaluating to UNKNOWN (and thus being excluded) is fragile and non-obvious to readers — INNER JOIN is explicitly correct. Employees with NULL manager_id are top-level executives with no manager; INNER JOIN correctly excludes them, but the correlated subquery approach needs an explicit IS NOT NULL guard for clarity. Another common error is assuming manager_id references a separate managers table — always verify it is a self-referential foreign key within the same employees table before writing the join.
Extend the self-join SELECT list: SELECT e.name AS employee_name, e.salary AS employee_salary, m.name AS manager_name, m.salary AS manager_salary FROM employees e JOIN employees m ON e.manager_id = m.id WHERE e.salary > m.salary. The self-join already has both rows in scope — simply add manager columns to SELECT. With the correlated subquery version, you would need a second subquery or refactor to a self-join to access the manager's name.
With the INNER JOIN self-join, employees with NULL manager_id are automatically excluded because JOIN ON e.manager_id = m.id fails to match any row — NULL = m.id is never TRUE. In the correlated subquery, the subquery returns no rows for NULL manager_id, making the > comparison evaluate to UNKNOWN, also excluding the row. Add WHERE e.manager_id IS NOT NULL explicitly in the subquery version to make this behavior visible to future readers.
Use a window function for efficiency: SELECT name FROM (SELECT name, salary, AVG(salary) OVER (PARTITION BY department_id) AS dept_avg FROM employees) t WHERE salary > dept_avg. This scans the table once. Alternatively, a correlated subquery WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id) works but scans once per employee. The window function approach is significantly faster at scale.