SQL – Full Outer Join

Main_full outer join

admin Oracle, SQL Joins , ,

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.
Full Outer Join

Above picture represents the output of Full Outer Join.
Syntax is pretty much similar in case of all types of Joins.

Syntax:

SELECT a.col1, a.col2, b.col1, b.col2 FROM
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.

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_IDEMPLOYEES_IDFIRST_NAMEDEPARTMENT_NAMEDEPT_ID
20202PatMarketing20
30119KarenPurchasing30
70204HermannPublic Relations70
90102LexExecutive90
100113LuisFinance100
110206WilliamAccounting110
(null)178Kimberely(null)(null)
(null)(null)(null)Administration10
(null)(null)(null)Treasury120
(null)(null)(null)IT60
(null)(null)(null)Shipping50
(null)(null)(null)Human Resources40
(null)(null)(null)Sales80

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

Sharing is caring!

You May Also Like..

IN

Oracle – IN Condition

Hey people, In this Oracle tutorial, we will learn how to use IN condition in SQL. IN condition is used […]

NULLIF

Oracle SQL_Null Related functions-3_NULLIF

Hi Aliens, Today, we will talk about another Null Related Function, NULLIF. You can read my notes on NVL, NVL2 […]

WHERE

SQL – WHERE clause

Hi DBgeek Army, Welcome back to the world of Oracle! Where clause helps to select desired rows or helps to […]

Leave a Reply

Your email address will not be published. Required fields are marked *