Outer Join

Full Outer Join in DBMS

 

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

A join is a query that combines rows from two or more tables, views or materialized views

Definition: Full join returns all the matched records and unmatched records from both left side table and right side table.

A Full join is viewed as a union of an inner join, left outer join and right outer join.

 

Learn more about Joins here on this page.

Full Outer Join in DBMS

How full join works

  • It Returns all the records that are matched based upon the condition (inner join)
  • It Returns all the unmatched records from the right table (right outer)
  • It Returns all the unmatched record from the left table (left outer)
  • It places normal values on unmatched records when there is no data to be appended for some tuples.

 

Syntax:

select 
col1,col2,col3.... 
from  table1
FULL JOIN table2
on table1.common_col=table2.common_col;

Consider two tables

Emp table

EMPNOENAMEJOBMGRHIRE_DATESALCOMMDEPTNO
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 Join SQL query

select rownum,Empno,Ename,Emp.Deptno,Dname, sal*12 annsal,loc,job
from 
Emp
FULL JOIN
Dept
on Emp.Deptno=Dept.deptno and dname='SALES';

O/P

ROWNUMEMPNOENAMEDEPTNODNAMEANNUALLOCJOB
17839KING1060000PRESIDENT
27698BLAKE30SALES34200CHICAGOMANAGER
37782CLARK1029400MANAGER
47566JONES2035700MANAGER
57788SCOTT2036000ANALYST
67902FORD2036000ANALYST
77369SMITH209600CLERK
87499ALLEN30SALES19200CHICAGOSALESMAN
97521WARD30SALES15000CHICAGOSALESMAN
107654MARTIN30SALES15000CHICAGOSALESMAN
117844TURNER30SALES18000CHICAGOSALESMAN
127876ADAMS2013200CLERK
137900JAMES30SALES11400CHICAGOCLERK
147934MILLER1015600CLERK
15OPERATIONSBOSTON
16ACCOUNTINGNEW YORK
17RESEARCHDALLAS

 

  • There are 6 matched records with deptno=30 and name =’SALES’ from dept table with emp table and 3 records from right side table ‘dept’ are unmatched are displayed ,if there is no data to be added on unmatched records null values are added and 9 unmatched records from left side table with null values when there is no data to be appended for tuples
  • 6(inner join matched records)+9(unmatched records from the left table)+3(unmatched records from the right table)=17 records (full outer join )