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

Sharing is caring!

You May Also Like..

merge

SQL – MERGE Statement

Hi Devs, As we have already learned INSERT INTO and UPDATE statement, our next very important statement is MERGE. Merge […]

update2

SQL – UPDATE Statement part-2

Hi Geeks, In previous blog on UPDATE statement, we learnt, how can we update a column value with a string […]

update

SQL – UPDATE Statement part-1

Hi Geeks, We already have a separate blog on INSERT INTO statement. So today, we will talk about UPDATE statement […]

Leave a Reply

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