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