SQL self join or sub-query interview question (employee-manager salary)

One of my favorite interview questions that tips even seasoned SQL guys (maybe because it's too simple) is around querying data that involves a self join.

What is a self join?
A self join is a join in which a table is joined with itself, specially when the table has a foreign  key which references its own primary key. 

Question: Given an Employee table which has 3 fields - Id (Primary key), Salary and Manager Id, where manager id is the id of the employee that manages the current employee, find all employees that make more than their manager in terms of salary. Bonus: Write the table creation script.


Let's start by creating our table:

CREATE TABLE Employee (
    [Id] [int] NOT NULL,
    [ManagerId] [int] NOT NULL,
    [Salary] [money] NOT NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
    (
        [Id] ASC
    )
)

This will create our table with Id as primary key. Now let's modify this table to add a foreign key betweek ManagerId and Id.

ALTER TABLE [dbo].[Employee]  WITH CHECK ADD  CONSTRAINT [FK_Employee_Employee] FOREIGN KEY([ManagerId])
REFERENCES [dbo].[Employee] ([Id])
GO

ALTER TABLE [dbo].[Employee] CHECK CONSTRAINT [FK_Employee_Employee]
GO

Now we come to the meat of the question. How do you find all employees that make more than their manager. There are 2 ways to do this - one, use a self join; and two; use a sub-query.

Self-join solution:
select e.*, m.Salary as "Manager Salary"
from Employee e
join Employee m on e.ManagerId = m.Id
where e.Salary > m.Salary

Here you are "joining" Employee table to itself on the FK relation of ManagerId and then querying on salary.

Sub-query solution:
select *
from Employee e
where e.Salary >
    (select m.Salary from Employee m
    where e.ManagerId = m.Id)

This is most common solution I get from candidates. Which is fine, but knowing alternate approaches to finding a solution is always very helpful.

Happy coding till next time!