Full Outer join in SQL

Full join Mechanism

• It Returns all matched records i.e where the join condition is satisfied on both tables
• It Returns all rows from right side table (unmatched right side rows)
• It Returns all rows from the left side table(unmatched left side rows)
• This join returns null values in place of nonmatching tuples in another table

Syntax

select col1,col2.... 
from 
tab1 
FULL JOIN 
tab2 
on tab1.colname=tab2.colname

• Consider two tables

Emp table Dept table

Full outer join SQL query

select ename,sal,d.deptno,dname,loc
from 
emp e 
FULL join
dept d
on e.deptno=d.deptno;

o/p 14 records are matched with the join condition and remaining one record deptno=40 ,will not have any match from emp table hence it is appended with null values.