CODEWITHSUNDEEP
sqlsql-server
Nick
Nick

Reputation: 3643

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

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

Upvotes: 64

Views: 79106

Answers (9)

Ahmed Elewa
Ahmed Elewa

Reputation: 11

Imagine you're a detective investigating a mysterious case of missing connections between your inventory and suppliers. In your inventory database, you have a list of products, and in your supplier database, you have a list of suppliers. To crack the case and uncover any missing links, you decide to perform a "Full Outer Join" operation using the secret code known as the product codes. This powerful technique merges both databases, revealing a treasure trove of information! Suddenly, hidden before your eyes, you uncover products without their loyal suppliers and suppliers without their dedicated products. This revelation helps you shine a spotlight on the gaps and missing data in either table, bringing you closer to solving the case and restoring the missing connections in your inventory kingdom.

Upvotes: 1

donDrey
donDrey

Reputation: 41

For example, suppose you have two tables: one containing customer data and another containing order data. A full outer join would allow you to see all customers and all orders, even if some customers have no orders or some orders have no corresponding customer. This can help you identify any gaps in the data and ensure that all relevant information is included in the result set.

It's important to note that a full outer join can produce a huge result set since it includes all rows from both tables. This can be inefficient in terms of performance, so it's best to use a full outer join only when it is necessary to include all rows from both tables.

SELECT *
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name;

This will return all rows from both table1 and table2, filling in NULL values for missing matches on either side.

Upvotes: 0

Cade Roux
Cade Roux

Reputation: 89671

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).

Upvotes: 66

Ashraf Sada
Ashraf Sada

Reputation: 4905

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.

Upvotes: 6

ewernli
ewernli

Reputation: 38615

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...

Upvotes: 35

NonProgrammer
NonProgrammer

Reputation: 1387

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:

  1. Table A has any rows that are not in Table B
  2. Table B has any rows that are not in Table A
  3. Duplicates in either Table A or Table B
  4. 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.

Upvotes: 11

George Johnston
George Johnston

Reputation: 32258

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.

Upvotes: 29

gingerbreadboy
gingerbreadboy

Reputation: 7769

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.

Upvotes: 5

kevchadders
kevchadders

Reputation: 8335

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.

Upvotes: 9

Related Questions