SELF JOIN in Oracle

SELF JOIN
The Oracle SELF Join query is used to join a table with itself, i,e, each row is combined with itself and every other rows of the table.

Syntax:

SELECT A.columns, B.columns   
FROM table_1 A, table_1 B   
WHERE A.common_filed = B.common_field;    

Example:
Students Table:

STUDENT_ID STUDENT_NAME STUDENT_AGE
1 Joy 20
2 Smiley 19
3 Happy 21
4 James 22
5 Bond 25

Query:

SELECT A.student_id, A.student_name, B.student_age  
FROM students A, students B
WHERE A.student_age < B.student_age;

Output:

STUDENT_ID STUDENT_NAME STUDENT_AGE
1 Joy 21
1 Joy 22
1 Joy 25
2 Smiley 20
2 Smiley 21
2 Smiley 22
2 Smiley 25
3 Happy 22
3 Happy 25
4 James 25

Explanation:
The ‘students’ is an already existing table. After the joining, the selected fields of the rows of table A and table B will be displayed for which the student_age in table A is less than that in table B.

Please Share