Oracle: Self Join

To understand SELF JOIN in Oracle you all need to understand How basic join works. SELF JOIN in oracle is used to perform join with a similar table. Although, Joining Tables always happen between two different tables, However here in self join we join a table with itself with help of an alias. This relation of the table with itself is described by the word Unary Relationship. 

Syntax

Select Column from Table alias1, Table alias2
where condition...

Let's not confuse ourselves and understand the concept step by step:

Suppose we have the following Marks_Stu Table:

Step 1: Firstly lets consider 1st part of the code i.e :

Select * from Marks_Stu from Marks_Stu a, Marks_Stu b;

1. Marks_Stu is the name of the Table.
2. As, we all know that Joins are always applied between two tables. But here as per the functioning of SELF JOIN the same table is join with itself. Therefore, to fulfil the criteria of Self Join we use different alias a & b. In easy terms you can understand the concept by considering that two copy of Table Marks_Stu is generated. One is named as a and other is named as b.
3. The Output of step 1 will just provide the cartesian product of table a with table b, in short with itself.

Output

Step 2: Now, Where condition; part of syntax will filter the data according to the condition applied and will output the specific result:

WHERE a.Marks = b.Marks;

The above code specifies that we need those records which have same Marks entries in Cartesian Product above.

Now, if we join Step1 and Step2, we can make a code as:

select * from Marks_Stu a, Marks_Stu b
WHERE a.Marks = b.Marks;

Output