When is a good situation to use a full outer join?

Question

I'm always discouraged from using one, but is there a circumstance when it's the best approach?

Answer 1

It's rare, but I have a few cases where it's used. Typically in exception reports or ETL or other very peculiar situations where both sides have data you are trying to combine. The alternative is to use an INNER JOIN, a LEFT JOIN (with right side IS NULL) and a RIGHT JOIN (with left side IS NULL) and do a UNION - sometimes this approach is better because you can customize each individual join more obviously (and add a derived column to indicate which side is found or whether it's found in both and which one is going to win).

Answer 2

I noticed that the wikipedia page provides an example.

For example, this allows us to see each employee who is in a department and each department that has an employee, but also see each employee who is not part of a department and each department which doesn't have an employee.

Note that I never encountered the need of an outer join in practice...

Answer 3

I've used full outer joins when attempting to find mismatched, orphaned data, from both of my tables and wanted all of my result set, not just matches.

Answer 4

The rare times i have used it has been around testing for NULLs on both sides of the join in case i think data is missing from the initial INNER JOIN used in the SQL i'm testing on.

Answer 5

They're handy for finding orphaned data but I rarely use then in production code. I wouldn't be "always discouraged from using one" but I think in the real world they are less frequently the best solution compared to inners and left/right outers.