Self Join in Oracle SQL

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);

Leave a Comment