OUTER JOIN in Oracle

by

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
1 James 101
2 Bond 102
3 Smith –
4 Smiley –
5 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 Smith –
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.