Oracle Database JOINS FULL OUTER JOIN
Example
A FULL OUTER JOIN performs a join between two tables that requires an explicit join clause but does not exclude unmatched rows in either table. In other words, it returns all the rows in each table.
Example:
SELECT
*
FROM
EMPLOYEE FULL OUTER JOIN DEPT
ON EMPLOYEE.DEPTNO = DEPT.DEPTNO;
Here's an example of Full Outer Join between two tables:
Sample Table: EMPLOYEE
+-----------+---------+
| NAME | DEPTNO |
+-----------+---------+
| A | 2 |
| B | 1 |
| C | 3 |
| D | 2 |
| E | 1 |
| F | 1 |
| G | 4 |
| H | 4 |
+-----------+---------+
Sample Table: DEPT
+---------+--------------+
| DEPTNO | DEPTNAME |
+---------+--------------+
| 1 | ACCOUNTING |
| 2 | FINANCE |
| 5 | MARKETING |
| 6 | HR |
+---------+--------------+
Now, If you execute the query:
SELECT
*
FROM
EMPLOYEE FULL OUTER JOIN DEPT
ON EMPLOYEE.DEPTNO = DEPT.DEPTNO;
Output
+-----------+---------+---------+--------------+
| NAME | DEPTNO | DEPTNO | DEPTNAME |
+-----------+---------+---------+--------------+
| A | 2 | 2 | FINANCE |
| B | 1 | 1 | ACCOUNTING |
| C | 3 | | |
| D | 2 | 2 | FINANCE |
| E | 1 | 1 | ACCOUNTING |
| F | 1 | 1 | ACCOUNTING |
| G | 4 | | |
| H | 4 | | |
| | | 6 | HR |
| | | 5 | MARKETING |
+-----------+---------+---------+--------------+
Here the columns that do not match has been kept NULL.
