What is a FULL OUTER JOIN?
The FULL OUTER JOIN is the last of the join methods. This join method returns rows from both the first table and the second table regardless of whether or not a match exists. Visualized using a Venn diagram, the FULL OUTER JOIN looks like this:
As an example, we can return the Name column from Production.Product and the subcategory Name from Production.ProductSubCategory regardless of any matches from either table. So, if a ProductSubCategoryID exists in Production.ProductSubCategory but not in Production.Product it will be returned. Similarly, if a ProductSubCategoryID exists in Production.Product but not in Production.ProductSubCategory, that row will also be returned:
SELECT P.Name AS [Product Name], PS.Name AS [Product Subcategory Name]
FROM Person.Person P
FULL OUTER JOIN Production.ProductSubCategory PS
ON P.ProductSubCategoryID= PS.ProductSubCategoryID
I would argue that this type of join is the least commonly used. It does, however, have its uses in special circumstances. When we get to some of the functions involving the COALESCE function, some additional uses for the FULL OUTER JOIN might be highlighted.