When is a good situation to use a full outer join?
SqlSql ServerSql Problem Overview
I'm always discouraged from using one, but is there a circumstance when it's the best approach?
Sql Solutions
Solution 1 - Sql
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).
Solution 2 - Sql
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 a full outer join in practice...
Solution 3 - Sql
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.
Solution 4 - Sql
Just today I had to use Full Outer Join. It is handy in situations where you're comparing two tables. For example, the two tables I was comparing were from different systems so I wanted to get following information:
- Table A has any rows that are not in Table B
- Table B has any rows that are not in Table A
- Duplicates in either Table A or Table B
- For matching rows whether values are different (Example: The table A and Table B both have Acct# 12345, LoanID abc123, but Interest Rate or Loan Amount is different
In addition, I created an additional field in SELECT statement that uses a CASE statement to 'comment' why I am flagging this row. Example: Interest Rate does not match / The Acct doesn't exist in System A, etc.
Then saved it as a view. Now, I can use this view to either create a report and send it to users for data correction/entry or use it to pull specific population by 'comment' field I created using a CASE statement (example: all records with non-matching interest rates) in my stored procedure and automate correction, etc.
If you want to see an example, let me know.
Solution 5 - Sql
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.
Solution 6 - Sql
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.
Solution 7 - Sql
In the rare times that I used Full Outer Join it was for data analysis and comparison purpose such as when comparing two customers tables from different databases to find out duplicates in each table or to compare the two tables structures, or to find out null values in one table compared to the other, or finding missing information in one tables compared to the other.