Outer Join

July 23, 2019

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

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
Full Join outer join in DBMS

Syntax:

select 
col1,col2,col3.... 
from  
FULL JOIN table2
on
.commoncolname>=name> Consider two tables Emp table
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7839 KING PRESIDENT – 17-NOV-81 5000 – 10
7698 BLAKE MANAGER 7839 01-MAY-81 2850 – 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 – 10
7566 JONES MANAGER 7839 02-APR-81 2975 – 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 – 20
7902 FORD ANALYST 7566 03-DEC-81 3000 – 20
7369 SMITH CLERK 7902 17-DEC-80 800 – 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 – 20
7900 JAMES CLERK 7698 03-DEC-81 950 – 30
7934 MILLER CLERK 7782 23-JAN-82 1300 – 10
Dept table
DEPTNO DNAME LOC
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

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
ROWNUM EMPNO ENAME DEPTNO DNAME ANNSAL 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 )