Hi all,

Can someone specify the situation at when to use FULL OUTER JOIN in a query?

__________________
-Murali..

Hi,

A common situation where you need to "merge" the data from two tables, and rows may or may not exist in either table. For example, suppose we have budgets in one table and actuals in another and we wish to present them side by side. Many people would use a FULL OUTER JOIN to accomplish this, perhaps writing something like this:

SELECT
coalesce(a.company,b.company) as company,
coalesce(a.account, b.account) as account,
coalesce(a.year,b.year) as year,
coalesce(a.month, b.month) as month,
coalesce(a.amount,0) as Actual,
coalesce(b.amount,0) as Budget
FROM
Actuals a
FULL OUTER JOIN
Budgets b on
a.company = b.company and
a.account = b.account and
a.year = b.year and
a.month = b.month

The above FULL OUTER JOIN will effectively "merge" the two tables and allow you to see all actuals and budgets for every company/account/year/month, and it will show values from either table even if there is not a matching value in the other table.