Using SQL self join Find Upper level manager in Employee table

A self-join is a query in which a table is joined (compared) to itself. Self-joins are used to compare values in a column with other values in the same column in the same table.

You can use a self-join to simplify nested SQL queries where the inner and outer queries reference the same table. These joins allow you to retrieve related records from the same table. The most common case where you'd use a self-join is when you have a table that references itself

I have the employee detail table, In that i want to find the manager for each Employee.
The employee name and manager column are present in same table

Consider the following Employee table

CREATE TABLE Employee

(

    Emp_Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,

    Emp_Name VARCHAR(100),

    Manager_Id INT NULL

)

INSERT INTO Employee(Emp_Name,Manager_Id)VALUES('a',null)

INSERT INTO Employee(Emp_Name,Manager_Id)VALUES('b',1)

INSERT INTO Employee(Emp_Name,Manager_Id)VALUES('c',2)

INSERT INTO Employee(Emp_Name,Manager_Id)VALUES('d',3)

INSERT INTO Employee(Emp_Name,Manager_Id)VALUES('e',4)

INSERT INTO Employee(Emp_Name,Manager_Id)VALUES('f',5)

Emp_Id	Emp_Name	Manager_Id
1	a	Null
2	b	1
3	c	2
4	d	3
5	e	4
6	f	5

To find the manager name for each employee in the Employee table

SELECT e2.Emp_Id,

      e2.Emp_Name as EMP_NAME,

      e1.Emp_Name as MANAGER_NAME

FROM Employee e1,Employee e2

WHERE e1.Emp_Id=e2.Manager_Id

OUTPUT

Emp_Id	EMP_NAME	Manager_NAME
2	b	a
3	c	b
4	d	c
5	e	d
6	f	e

Now find the Employee Manager Name and its senior Manager Name

SELECT e2.Emp_Id,

         e2.Emp_Name AS EMP_NAME,

         e1.Emp_Name AS MANAGER_NAME,

         e3.Emp_Name AS SENIOR_MANAGER_NAME

FROM Employee e1, Employee e2, Employee e3

WHERE e1.Emp_Id= e2.Manager_Id

AND e1.Manager_Id = e3.Emp_Id

ORDER BY e2.Emp_Id

OUTPUT

Emp_Id	EMP_NAME	MANAGER_NAME	SENIOR_MANAGER_NAME
3	c	b	a
4	d	c	b
5	e	d	c
6	f	e	d