A FULL OUTER JOIN (or FULL JOIN) renders ALL records when a match exists in either table’s (table 1 or table 2) records. It will also render all rows whether there is matching data from the other table or not. In cases where a table does not have a match from the join, the result set will render a “null” value in the field.
FULL OUTER JOIN Syntax:
SELECT table_1.field1, table_1.field2, table_2.field1, table_2.fieldn....
FROM Table_1
FULL OUTER JOIN table_2
ON table_1.matching_field = table2.matching_field;
FULL OUTER JOIN Example:
Table 1
Table 2
FULL OUTER JOIN Query:
Result Set:
Notice how the result set outputs 'null' results for Kelly Radio's record. If there are rows in the "employees" table that do not have matches in the "employee_address" table or vice versa, the records will still be listed regardless. An example of this may be that Kelly Radio was just hired as a new employee and has a row in the employee's table but has not yet submitted his address information. Therefore, Kelly's address id does not yet have information stored in the employee_address table to populate with a query. However, the record will not be left out regardless of lacking a total match.