CodesJava

Easy learning with example program codes

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 follow and like us:
error









Copyright © 2019 CodesJava DMCA.com Protection Status