PostgreSQL Full Outer Join

PostgreSQL Full Outer Join returns all rows from both tables of join query.

Following is the ven-diagram for PostgreSQL Full Outer Join.

PostgreSQL Full Outer Join Examples:

Tables used for below examples:

emp table:

dept table:

 deptno |   dname    |   loc
--------+------------+----------
     40 | OPERATIONS | BOSTON
     30 | SALES      | CHICAGO
     10 | ACCOUNTING | NEW YORK
     20 | RESEARCH   | DALLAS

1. Following query performs full outer join on employee and dept tables.

select e.empno,e.ename,e.sal,d.dname,d.loc from emp e full outer join dept d
on e.deptno=d.deptno;

Output:

 empno | ename  |   sal   |   dname    |   loc
-------+--------+---------+------------+----------
       |        |         | OPERATIONS | BOSTON
  7698 | BLAKE  | 2850.00 | SALES      | CHICAGO
  7499 | ALLEN  | 1600.00 | SALES      | CHICAGO
  7654 | MARTIN | 1250.00 | SALES      | CHICAGO
  7521 | WARD   | 1250.00 | SALES      | CHICAGO
  7934 | MILLER | 1800.00 | ACCOUNTING | NEW YORK
  7566 | JONES  | 2975.00 | RESEARCH   | DALLAS
  7369 | SMITH  | 1300.00 | RESEARCH   | DALLAS