What is a Self Join?
A self-join is simply a normal SQL join that joins one table to itself. Joining a table to itself can be useful when you want to compare values in a column to other values in the same column.
Is Self Join Inner Join or Outer Join?
A self-join can be an inner join or an outer join or even a cross join. A table is joined to itself based upon a column that have duplicate data in different rows.
What is a practical use of the Self Join in the real world?
The best example of self join in the real world is when we have a table with Employee data and each row contains information about employee and his/her manager. You can use self join in this scenario and retrieve relevant information. Let us see an example, over here.
Question In below example : List the employee name followed by manager name

create table emp_temp_20220307_0629( emp_id number, emp_name varchar2(20), manager_id number ); insert into emp_temp_20220307_0629 values (1,'Mike',3); insert into emp_temp_20220307_0629 values (2,'David',3); insert into emp_temp_20220307_0629 values (3,'Roger',NULL); insert into emp_temp_20220307_0629 values (4,'Marry',2); insert into emp_temp_20220307_0629 values (5,'Joseph',2); insert into emp_temp_20220307_0629 values (7,'Ben',2);
Answer

SELECT t1.emp_name AS emp_name, t2.emp_name AS mgr_name FROM emp_temp_20220307_0629 t1 left JOIN emp_temp_20220307_0629 t2 ON t1.manager_id = t2.emp_id;
Question : Showing the students who have enrolled in two courses using self join
create table student_temp_202103060804( sid VARCHAR2(10), cid VARCHAR2(10), year number ); insert into student_temp_202103060804 values ('s1','c1','2016'); insert into student_temp_202103060804 values ('s2','c2','2017'); insert into student_temp_202103060804 values ('s1','c2','2018'); select st1.*,st2.* from student_temp_202103060804 st1 join student_temp_202103060804 st2 on (st1.sid = st2.sid and st1.cid <> st2.cid);