Full Outer join in SQL

Full Outer  Join in SQL

 

In this article, we will learn about Full Outer Join in SQL.

Join enables data merging from tables. Is used to query data from multiple tables.
A full join is viewed as a result of union operation of an inner join, left join, and right join.

 

Learn more about Joins in SQL here on this page.

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

EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
7839KINGPRESIDENT17-NOV-81500010
7698BLAKEMANAGER783901-MAY-81285030
7782CLARKMANAGER783909-JUN-81245010
7566JONESMANAGER783902-APR-81297520
7788SCOTTANALYST756619-APR-87300020
7902FORDANALYST756603-DEC-81300020
7369SMITHCLERK790217-DEC-8080020
7499ALLENSALESMAN769820-FEB-81160030030
7521WARDSALESMAN769822-FEB-81125050030
7654MARTINSALESMAN769828-SEP-811250140030
7844TURNERSALESMAN769808-SEP-811500030
7876ADAMSCLERK778823-MAY-87110020
7900JAMESCLERK769803-DEC-8195030
7934MILLERCLERK778223-JAN-82130010

 

Dept table

DEPTNODNAMELOC
10ACCOUNTINGNEW YORK
20RESEARCHDALLAS
30SALESCHICAGO
40OPERATIONSBOSTON

 

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

ROWNUMENAMESALDEPTNODNAMELOC
1KING500010ACCOUNTINGNEW YORK
2BLAKE285030SALESCHICAGO
3CLARK245010ACCOUNTINGNEW YORK
4JONES297520RESEARCHDALLAS
5SCOTT300020RESEARCHDALLAS
6FORD300020RESEARCHDALLAS
7SMITH80020RESEARCHDALLAS
8ALLEN160030SALESCHICAGO
9WARD125030SALESCHICAGO
10MARTIN125030SALESCHICAGO
11TURNER150030SALESCHICAGO
12ADAMS110020RESEARCHDALLAS
13JAMES95030SALESCHICAGO
14MILLER130010ACCOUNTINGNEW YORK
1540OPERATIONSBOSTON

 

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.