Full Outer Join:
The Full Outer Join query after joining returns all the records from the selected fields of both the tables irrespective of the fact that the join condition is met or not.
Syntax:
SELECT expr_1, expr_2, ... expr_n FROM table_1 FULL OUTER JOIN table_2 ON join_predicate;
Parameters:
join_predicate: It is used to specify the joining conditions to be strictly followed for joining.
Example:
Employment Table:
| ID | STATE | RATE |
| 1 | A | 60 |
| 2 | B | 70 |
| 3 | C | 65 |
| 4 | D | 80 |
| 5 | E | 78 |
Department Table:
| ID | NAME | PERCENT |
| 1 | IT | 60 |
| 2 | SALES | 75 |
| 3 | BANK | 50 |
Query:
SELECT “EMPLOYMENT”.“ID”, “EMPLOYMENT”.“STATE”, “DEPARTMENT”.“NAME” FROM “EMPLOYMENT” FULL OUTER JOIN “DEPARTMENT” ON “EMPLOYMENT”.“ID” = “DEPARTMENT”.“ID”; |
Output:
| ID | STATE | NAME |
| 1 | A | IT |
| 2 | B | Sales |
| 3 | C | Bank |
| 4 | D | |
| 5 | E |
Explanation:
The EMPLOYMENT and the DEPARTMENT are the already existing tables that are joined with FULL OUTER JOIN query so that all the records from the EMPLOYMENT table and the DEPARTMENT table for the selected fields is combined in the result set.