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
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;

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;

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.

Alex Martin