Explain Self Join with an example

Answer:

Self Join is just like a inner join, which joins the two instance of the same table.

A simple example is to find-out the manger of an employee from the employee table that contains both employee id an manger id.
Here the manager is also an employee having an employee id.

Let us see the code

1.Creating the Employee table

CREATE TABLE Employee

(employee_id int,

manger_id int,

employee_name varchar(20)

)



2.Inserting the value

INSERT INTO Employee VALUES(1,1,'Sachin'),(2,1,'Rahul'),(3,2,'Sourav'),(4,1,'Sunil')



3.The self join query

SELECT emp.employee_id,emp.employee_name [Employee], mgr.employee_name [Manager]



       FROM Employee emp, Employee mgr



       WHERE emp.manger_id = mgr.employee_id