Category: General

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

Full Outer Join with CoalesceUnion All with Qualify
Row Count23 289 75523 289 755
CPU88183
IO41 514111 158
Spool1 118 400 5122 005 872 640

Replace string by using Regular Expression

By parsing JSON array, you get values in brackets and double quotas and might need to make the string more human readable. Regular expression REGEXP_REPLACE can be used for it in your sql.

Input string: [“Blue”], [“Pink”], [“Black”]

Output string: Blue; Pink; Black

Action: Get rid of brackets with double quotas and replace comma by semicolon.

Code:

SELECT
 '["Blue"], ["Pink"], ["Black"]' as original_string,
 REGEXP_REPLACE(original_string, '[^A-Za-z,]', '') AS alpha_and_comma_chars_only,
 REGEXP_REPLACE(alpha_and_comma_chars_only, ',', '; ') AS replaced_comma
;

Result:

original_stringalpha_and_comma_chars_onlyreplaced_comma
[“Blue”], [“Pink”], [“Black”]Blue,Pink,BlackBlue; Pink; Black

Explanation: