Full Outer Join vs Union All in Teradata

If we want to combine data about same information from two sources, we can think of using two sql approaches:

• Full Outer Join with Coalesce or

• Union All with Qualify.

Full Outer Join shows better performance statistics in all 3 aspects: CPU, IO and Spool.

Let’s say we have orders with customer info from two different data sources. Both are relevant data sets, some orders are only in Set A, some orders are only in Set B and some orders exist in both sets. Rule for combination is to prioritize data from Set A.

Full Outer Join with Coalesce

select 
   coalesce(a1.order_id, a2.order_id) as order_id
  ,coalesce(a1.customer_id, a2.customer_id) as customer_id
 from SetA a1
 full outer join SetB a2
  on a1. order_id = a2. order_id
;

Union All with Qualify

select order_id, party_id from
 (
  select order_id, party_id, 1 as prio from SetA
  union all
  select order_id, party_id, 2 as prio from SetB
  ) a1
 QUALIFY Row_Number() over (PARTITION BY order_id ORDER BY prio) = 1
;

Performance Comparison