Systems Engineering and RDBMS

Archive for April 5th, 2007

Full Outer Join

Posted by decipherinfosys on April 5, 2007

As you know, full outer joins returns all the rows from the two tables (or sets) that are joined together – the matching records as well as the non matching records from the two sets based on the join condition. A where clause can be used to further restrict the output and sho, say only that data that does not match between the two tables (or sets).

We used this recently in a scenario where we needed to do a data- difference between the data recorded the previous day vs the data record for the current day after consuming the interface data that was received from the third party vendor. Historical data was archived into a history table on a daily basis to capture the snapshot of the data as it existed the day before. The next day’s run could update some of those values and a report needed to be run to show the differences. For the sake of simplicity, let’s assume that there is a HIST_TABLEA table and a TABLEA table. and here is the data from these tables:


———– ———- ———-
1 A B
2 A B
3 A BB


———– ———- ———-
1 A B
2 AA B
3 A B

As you can see, some values got changed between the day before and today. Here is a simple SQL that will highlight the changes in a side by side fashion using FULL OUTER JOIN clause:

select a.col1 old_col1, a.col2 old_col2, a.col3 old_col3,
b.col1, b.col2, b.col3
from tablea a
full outer join hist_tablea b
on (a.col1 = b.col1 and(a.col2 <> b.col2 or a.col3 <> b.col3))
where a.col1 is not null and b.col1 is not null

old_col1 old_col2 old_col3 col1 col2 col3
———– ———- ———- ———– ———- ———-
2 A B 2 AA B
3 A BB 3 A B

This shows those records that got changed between yesterday and today.

Posted in DB2 LUW, Oracle, SQL Server | Leave a Comment »