Full Outer join in SQL

May 10, 2021

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

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

ROWNUM	ENAME	SAL	DEPTNO	DNAME	LOC
1	KING	5000	10	ACCOUNTING	NEW YORK
2	BLAKE	2850	30	SALES	CHICAGO
3	CLARK	2450	10	ACCOUNTING	NEW YORK
4	JONES	2975	20	RESEARCH	DALLAS
5	SCOTT	3000	20	RESEARCH	DALLAS
6	FORD	3000	20	RESEARCH	DALLAS
7	SMITH	800	20	RESEARCH	DALLAS
8	ALLEN	1600	30	SALES	CHICAGO
9	WARD	1250	30	SALES	CHICAGO
10	MARTIN	1250	30	SALES	CHICAGO
11	TURNER	1500	30	SALES	CHICAGO
12	ADAMS	1100	20	RESEARCH	DALLAS
13	JAMES	950	30	SALES	CHICAGO
14	MILLER	1300	10	ACCOUNTING	NEW YORK
15	–	–	40	OPERATIONS	BOSTON

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.