An outer join occurs when you connect two tables and designate one of the tables as the main table. Executing an outer join returns the same matching records as an inner join, and also returns all the non-matching records from the main table. A full outer join returns all the records from each table in the join. Wherever data is unavailable, the results return a null value.
In our example, an outer join with the personnel table as the main table returns records of employees who are in the engineering departments, and also records of employees outside the engineering organization. The records of the non-engineering employees contain nulls in the department and project columns.
A full outer join on the personnel table and the engineering organization table in our example returns every record in each table, but combines the connected columns. There are three varieties of records returned:
Non-engineering employees have nulls in the department and project columns.
Engineering employees have information in the personnel columns and the organizational columns.
People who work in the engineering organization but are not company employees have information in the organizational columns and nulls in the personnel columns.
