Outer Join

May 6, 2021

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.

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.

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

Consider two tables

Emp table

Dept table

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

ROWNUM	EMPNO	ENAME	DEPTNO	DNAME	ANNUAL	LOC	JOB
1	7839	KING	10	–	60000	–	PRESIDENT
2	7698	BLAKE	30	SALES	34200	CHICAGO	MANAGER
3	7782	CLARK	10	–	29400	–	MANAGER
4	7566	JONES	20	–	35700	–	MANAGER
5	7788	SCOTT	20	–	36000	–	ANALYST
6	7902	FORD	20	–	36000	–	ANALYST
7	7369	SMITH	20	–	9600	–	CLERK
8	7499	ALLEN	30	SALES	19200	CHICAGO	SALESMAN
9	7521	WARD	30	SALES	15000	CHICAGO	SALESMAN
10	7654	MARTIN	30	SALES	15000	CHICAGO	SALESMAN
11	7844	TURNER	30	SALES	18000	CHICAGO	SALESMAN
12	7876	ADAMS	20	–	13200	–	CLERK
13	7900	JAMES	30	SALES	11400	CHICAGO	CLERK
14	7934	MILLER	10	–	15600	–	CLERK
15	–	–	–	OPERATIONS	–	BOSTON	–
16	–	–	–	ACCOUNTING	–	NEW YORK	–
17	–	–	–	RESEARCH	–	DALLAS	–

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 )