CodesJava

Easy learning with example program codes

OUTER JOIN in Oracle

OUTER JOIN
Oracle supports three major types of Outer joins namely, Left Outer Join, Right Outer Join and Full Outer Join.

Left Outer Join:
As the name suggests, the Left Outer Join query offers more benefits for the Left table, can also be understood as the First table and thus returns all the rows from the Left table for the specified fields and only those rows from the Right table, can also be understood as the Second table where the join condition is met.

Syntax:

SELECT expr_1, expr_2, ... expr_n  
FROM table_1   
LEFT JOIN table_2  
ON join_predicate;

Parameters:
expr_1, expr_2, … expr_n: It is used to specify the columns of the table which needs to be joined.
table_1, table_2: It is used to specify the name of the tables from which the records need to be joined.
join_predicate: It is used to specify the joining conditions to be strictly followed by the rows to be included in the result set.

Example: Joining two tables with the Left Outer Join Query.
Students Table:

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

Teachers Table:

TEACHER_ID	TEACHER_NAME	TEACHER_AGE
101	        James	        30
102	        Bond	        25
103	        Smith	        40

Query:

SELECT students.student_id, students.student_name, teachers.teacher_id  
FROM students  
LEFT JOIN teachers  
ON students.student_name = teachers.teacher_name  

Output:

STUDENT_ID	STUDENT_NAME	TEACHER_ID
4	        James	        101
5	        Bond	        102
1	        Joy	        -
2	        Smiley	        -
3	        Happy	        -

Explanation:
The ‘students’ and the ‘teachers’ are the already existing tables. After the joining, all the selected fields of the rows of the ‘students’ table and the selected fields of the rows of the ‘teachers’ table which are satisfying the conditions will be displayed as the result. For the rows in the ‘students’ table that does not have any matching row in the ‘teachers’ table, the result will display a NULL value in the columns of the ‘teachers’ table.

Right Outer Join:
As the name suggests, the Right Outer Join query offers more benefits for the Right table, can also be understood as the Second table, and thus returns all the rows from the Right table for the specified fields and only those rows from the Left table, can also be understood as the First table, where the join condition is met.

Syntax:

SELECT expr_1, expr_2, ... expr_n  
FROM table_1   
RIGHT JOIN table_2  
ON join_predicate;

Parameters:
expr_1, expr_2, … expr_n: It is used to specify the columns of the table which needs to be joined.
table_1, table_2: It is used to specify the name of the tables from which the records need to be joined.
join_predicate: It is used to specify the joining conditions to be strictly followed by the rows to be included in the result set.
Example: Joining two tables with the Right Outer Join Query.
Students Table:

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

Teachers Table:

TEACHER_ID	TEACHER_NAME	TEACHER_AGE
101	        James	        30
102	        Bond	        25
103	        Smith	        40

Query:

SELECT students.student_id, students.student_name, teachers.teacher_id  
FROM students  
RIGHT JOIN teachers  
ON students.student_name = teachers.teacher_name  

Output:

STUDENT_ID	STUDENT_NAME	TEACHER_ID
4	        James	        101
5	        Bond	        102
-	        -	        103

Explanation:
The ‘students’ and the ‘teachers’ are the already existing tables. After the joining, all the selected fields of the rows of the ‘teachers’ table and the selected fields of the rows of the ‘students’ table which are satisfying the conditions will be displayed as the result. For the rows in the ‘teachers’ table that does not have any matching row in the ‘students’ table, the result will display a NULL value in the columns of the ‘students’ table.

Full Outer Join:
As the name suggests, the Full Outer Join query does not gives more priority to any of the table, and thus returns all the rows of the selected fields from both the tables, no matter whether the join condition is met or not.

Syntax:

SELECT expr_1, expr_2, ... expr_n  
FROM table_1   
FULL OUTER JOIN table_2  
ON join_predicate;

Parameters:
expr_1, expr_2, … expr_n: It is used to specify the columns of the table which needs to be joined.
table_1, table_2: It is used to specify the name of the tables from which the records need to be joined.
join_predicate: It is used to specify the joining conditions to be strictly followed by the rows to be included in the result set.

Example: Joining two tables with the Full Outer Join Query.
Students Table:

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

Teachers Table:

TEACHER_ID	TEACHER_NAME	TEACHER_AGE
101	        James	        30
102	        Bond	        25
103	        Smith	        40

Query:

SELECT students.student_id, students.student_name, teachers.teacher_id  
FROM students  
FULL OUTER JOIN teachers  
ON students.student_name = teachers.teacher_name  

Output:

STUDENT_ID	STUDENT_NAME	TEACHER_ID
4	        James	        101
5	        Bond	        102
1	        Joy	        -
2	        Smiley	        -
3	        Happy	        -
-	        -	        103

Explanation:
The ‘students’ and the ‘teachers’ are the already existing tables. After the joining, all the selected fields of the rows of the ‘teachers’ table and the selected fields of the rows of the ‘students’ table will be displayed as the result. For the rows in the ‘teachers’ table that does not have any matching row in the ‘students’ table, the result will display a NULL value in the columns of the ‘students’ table. Similarly, for the rows in the ‘students’ table that does not have any matching row in the ‘teachers’ table, the result will display a NULL value in the columns of the ‘teachers’ table.

Please follow and like us:
error









Copyright © 2019 CodesJava DMCA.com Protection Status