











Outer Join
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


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 |
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 )
Login/Signup to comment