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.


SELECT a.col1, a.col2, b.col1, b.col2 FROM
left_table1 a FULL OUTER JOIN right_table2 b
<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.

    a.*, --all columns from EMP table
    b.department_name, --department_name from DEPARTMENTS table
    b.department_id as Dept_ID
    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.

70204HermannPublic Relations70
(null)(null)(null)Human Resources40

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


Oracle_DUAL table

Hey geeks, DUAL table is a very important table available in the Oracle database. It gets created automatically during the […]

Concatenation Operator

Oracle_Concatenation Operator

Hey geeks, This is another chapter of Operators. Concatenation Operator helps to concatenate two or more strings or columns. The […]


Parentheses in SQL

Hey time travellers, Though Parentheses are used pretty much everywhere in SQL but in this blog, we will talk about […]

Leave a Reply

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