Hi Oracle folks,
This is going to be last blog on Joins, it is about Full Outer Join.
What is Full Outer Join?
Full Outer Join or Full Join is a combination of Left Outer Join and Right Outer Join. It means it produces all the rows from left table and all the rows from right table.
Above picture represents the output of Full Outer Join.
Syntax is pretty much similar in case of all types of Joins.
Syntax:
left_table1 a FULL OUTER JOIN right_table2 b
ON
<join condition> [WHERE…];
Like Right Outer Join and Left Outer Join, we can omit keyword OUTER. There will be no difference in the output.
We can use the EMP and DEPT tables to understand.
Let’s use the same query we used for Right Outer Join and add Full Outer Join keyword. This time we are fetching DEPARTMENT_ID from DEPT table as well.
SELECT
a.*, --all columns from EMP table
b.department_name, --department_name from DEPARTMENTS table
b.department_id as Dept_ID
FROM
emp a FULL OUTER JOIN dept b
ON a.department_id = b.department_id;
When we run above code, Oracle will generate output with all the rows from EMP table and all the rows from DEPT table. See below.
| DEPARTMENT_ID | EMPLOYEES_ID | FIRST_NAME | DEPARTMENT_NAME | DEPT_ID |
|---|---|---|---|---|
| 20 | 202 | Pat | Marketing | 20 |
| 30 | 119 | Karen | Purchasing | 30 |
| 70 | 204 | Hermann | Public Relations | 70 |
| 90 | 102 | Lex | Executive | 90 |
| 100 | 113 | Luis | Finance | 100 |
| 110 | 206 | William | Accounting | 110 |
| (null) | 178 | Kimberely | (null) | (null) |
| (null) | (null) | (null) | Administration | 10 |
| (null) | (null) | (null) | Treasury | 120 |
| (null) | (null) | (null) | IT | 60 |
| (null) | (null) | (null) | Shipping | 50 |
| (null) | (null) | (null) | Human Resources | 40 |
| (null) | (null) | (null) | Sales | 80 |
You can see in the above output, we have all the records from both table regardless any condition.
If you have any questions/suggestions, please comment below or write me an email.
If you like this blog, do share with your friends and colleagues on your social media.
For more updates join my facebook group and do like my facebook page.
Thank you,
Kapil Kumar
