Difference Between Left, Right and Full Outer Join

Earlier we have discussed the differences between Inner Join and Outer Join, where we have overviewed the types of Outer Join. Outer Join is the one who preserves the tuples in the result which would have been lost while Join operation. In this article; we are going to discuss the differences between the types of Outer Join.

There are three types of Outer Join; Left Outer Join, Right Outer Join, and Full Outer Join. The Left, Right, and Full Outer Join differs in their execution plan, and the results obtained. We can omit an Outer word from Left, Right, and Full Outer Join. Let us examine the differences between Left, Right and Full outer Join with the help of comparison chart shown below.

Content: Left Vs Right Vs Full Outer Join

1. Comparison Chart
2. Definition
3. Key Differences
4. Conclusion

Comparison Chart

Basis for Comparison	Left Outer Join	Right Outer Join	Full Outer Join
Basic	All the tuples of the left table remain in the result.	All the tuples of the right table remain in the result.	All the tuples from left as well as right table remain in the result.
NULL Extension	The tuples of left table that does not have a matching tuple in right table are extended with NULL value for attributes of the right table.	The tuples of right table that does not have a matching tuple in left table are extended with NULL value for attributes of the left table.	The tuples of left as well as the right table that does not have the matching tuples in the right and left table respectively are extended with NULL value for attributes of the right and left tables.

Definition of Left Outer Join

Suppose we have, “Table_A Left Outer Join Table_B“. So Table_A is our left table as it appears to left of Left Outer Join operation and Table_B is our right table.

Initially, the inner join would be applied on Table_A and Table_B which will return all the matching tuple from table A and B.

Next, it will return all the tuples from Table_A that do not have a matching tuple in Table_B. Such that, the resultant tuples would be padded with NULL values for the attributes of the right table.

Hence, the result obtained from the Left Outer Join retains all the tuples from left table and only matching tuples from the right table.

Let us discuss Left Outer Join with an example; we have two tables below, Student Table and Department Table. Now, we will apply Left Outer Join, on Student and Department Table.

SELECT * FROM Student LEFT OUTER JOIN Department
    ON Student. Student_ID = Department.Student_ID

In the query above, table Student is the left table and table Department is the right table. So according to Left Outer Join, the result must have all the tuples from Student table, and only matching tuples from the Department table. Observe the result obtained from Left Outer Join; it has all the tuples from the Student table along with the matching tuples from both Student and Department table. The student_id of Jimmy, Joseph Harry of Student table was not present in the Department table. Hence, the attribute values of Department table for Jimmy, Joseph Harry are extended to NULL.

Definition of Right Outer Join

Suppose we have, “Table_A Right Outer Join Table_B“. So Table_A is our left table as it appears to left of Right Outer Join operation and Table_B is our right table.

Like in Left Outer Join, initially, the inner join would be applied on Table_A and Table_B which will return all the matching tuple from table A and B.

Next, it will return all the tuples from Table_B that do not have a matching tuple in Table_A. Such that, the resultant tuples would be padded with NULL values for the attributes of the left table.

Hence, the result obtained from the right outer join retains all the tuples from right table and only matching tuples from the left table.

Let us discuss Right Outer Join with an example; above we have two tables, Student Table and Department Table.

Now, we will apply Right Outer Join to Student table and Department table.

SELECT * FROM Student RIGHT OUTER JOIN Department
    ON Student. Student_ID = Department.Student_ID

In the query above, Student Table is our Left Table and Department Table is our right table. According to the Right Outer Join operation, the result must include all the tuples from Department table and only matching tuples from Student Table. Observe the result obtained from right outer join; it has all the tuples from the Department table along with the matching tuples from both Student and Department table. The Student_ID 10536 and 00954, of Department table, are not present in Student table. Hence, the attribute value of Name for Student_ID 10536 and 00954 is extended to NULL.

Definition of Full Outer join

Suppose we have, “Table_A Full Outer Join Table_B“. So Table_A is our left table as it appears to left of Full Outer Join operation and Table_B is our right table.

Full Outer Join is the combination of both, Left Outer Join and Right Outer Join. Initially, it applies inner join on Table_A and Table_B to retrieve matching tuples from both the tables. Then it extends those tuples of Table_A with NULL that do not have a matching tuple in Table_B. Further, it extends those tuples from Table_B with NULL that do not have a matching tuple in Table_A.

Hence, Full Outer Join retains all the tuples from the left as well as the right table, along with the matching tuples of both the tables.

Let us discuss FULL Outer Join with an example; we have two tables above, Student Table and Department Table.

Now, we will apply Full Outer Join to Student table and Department table.

SELECT * FROM Student FULL OUTER JOIN Department
ON Student. Student_ID = Department.Student_ID

In the query above, Student Table is our left table and Department Table is our right table. According to Full Outer Join, the result must include all the tuples from both the tables. Observe the result obtained from full outer join; it has all the tuples from the Student and Department table along with the matching tuples from both Student and Department table. The student_id of Jimmy, Joseph Harry i.e. 10026,02256,56362 respectively, of Student table, was not present in the Department table.

Hence, the attribute values of Department table for Jimmy, Joseph Harry are extended to NULL .The Student_ID 10536 and 00954, of Department table, are not present in Student_ID column of Student table. Hence, the attribute value of Name attribute for Student_ID 10536 and 00954 is extended to NULL.

Conclusion

Take care of the positions of table names in a query. As the position of table name in query decides whether the table would be considered as left table or right table.