FULL OUTER JOIN in Teradata

A full outer join combines the effect of applying both left and right outer joins. Where records in the FULL OUTER JOINed tables do not match, the result set will have NULL values for every column of the table that lacks a matching row. For those records that do match, a single row will be produced in the result set (containing fields populated from both tables).

SELECT *
FROM employee
FULL OUTER JOIN department ON 
employee.DepartmentID = department.DepartmentID;


Consider the following two tables,
Employee table
LastNameDepartmentID
Rafferty31
Jones33
Steinberg33
Robert34
Smith34
JohnNULL


Department table
DepartmentIDDepartmentName
31Sales
33Engineering
34Clerical
35Marketing


Output:
Employee.LastNameEmployee.DepartmentIDDepartment.DepartmentNameDepartment.DepartmentID
Smith34Clerical34
Jones33Engineering33
Robert34Clerical34
JohnNULLNULLNULL
Steinberg33Engineering33
Rafferty31Sales31
NULLNULLMarketing35