











Full Outer join in SQL
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.
Login/Signup to comment