What is self join in oracle database?

A self-join is a join in which a table is joined with itself. To join a table itself means that each row of the table is combined with itself and with every other row of the table.

For E.g:

Suppose we have a table called “SelfJoin” and their data shown below

Query:- Select * from SelfJoin;

O/P:-

EmpId	Name	MgrId
1	Paul
2	Greg	1
3	Jon
4	Samy	3

In the table we have Employee’s id i.e EmpId, Name of the Employee and Manager of those employees i.e MgrId.

Condition:- Fetch the name of the employee and their managers.

Query:- Select M.Name ||' is the employee and '||E.Name||' their manager' as Details

from SelfJoin E,SelfJoin M

where M.mgrid=E.EmpId;

O/P:-

Details

Greg is the employee and Paul their manager

Samy is the employee and Jon their manager

Here the table “SelfJoin” is joined itself on the basis of EmpId and MgrId.