Wednesday 29 September 2010

Full outer join example

Recently I came across a scenario where I thought "say, a full outer join might hit the spot here"

It was a simple problem - we need to check that an old mapping table mapped all the old values to new values.

I figured doing a full outer join would assist making sure all values were accounted for.

Below I've cut down the actual output, but it highlights to me that the Yilgarn & Avon-Mortlock districts are not mapped to any new records, and there are number of new records that don't have a presence in the old tables.

SQL> l
  1  select distinct
  2    o.mapped_val
  3   ,o.old_district
  4   ,d.dst_name
  5   ,d.rgn_name
  6  from old_mapping_table o
  7  full outer join new_region_list d
  8  on d.dst_id = o.mapped_val
 
MAPPED_VAL        OLD_DISTRICT         DST_NAME             RGN_NAME
----------------- -------------------- -------------------- --------------------
               92 AVON-MORTLOCK
               93 YILGARN
               16 ALBANY               ALBANY               SOUTH COAST
               17 ESPERANCE            ESPERANCE            SOUTH COAST
               69 GERALDTON            GERALDTON            MIDWEST
               94 GREAT SOUTHERN       GREAT SOUTHERN       WHEATBELT
               14 KALGOORLIE           KALGOORLIE           GOLDFIELDS
               71 PERTH HILLS          PERTH HILLS          SWAN
               73 SHARK BAY            SHARK BAY            MIDWEST
                                       BUNBURY              SOUTH WEST
                                       GASCOYNE             MIDWEST
                                       GOLDFIELDS           GOLDFIELDS

Nothing more, just thought I'd share.

No comments: