Pages

Tuesday, August 5, 2014

Tricks: UNION and UNION ALL

While you are making union in Teradata it’s very important to follow below rules:

1) Select statement should have equal number of columns
For example:
select city_code, city_name, city_type
From CITY
union
select EMP_CITY_CODE as city_code, EMP_CITY as city_name, EMP_city_type as City_type
from CITY_EMP

2) Type should be same
If data type is different; then it should be cast and then make union, if you don’t define/cast the type then value will be truncated. So when you define type consider maximum size of data type.
For example:
select cast (city_code as char(10)), city_name, city_type
From CITY
union
select cast(EMP_CITY_CODE as char(10)) as city_code, EMP_CITY as city_name, EMP_city_type as City_type
from CITY_EMP

3) Where clause at UNION
At the time you are selecting if you cast a type , always remember to do the same when you filter at where clause.
For example:
insert DW_EMP_CITY (city_code,city_name,city_type)
select cast (city_code as char(10)), city_name, city_type
From CITY
union
select cast(EMP_CITY_CODE as char(10)) as city_code, EMP_CITY as city_name, EMP_city_type as City_type
from CITY_EMP
where not exists (select 1 from DW_EMP_CITY as EDW_CITY where EDW_CITY.city_code=cast(CITY_EMP.EMP_CITY_CODE as char(10))

4) When to use UNION/UNION ALL

If you want to avoid duplicates then use ‘UNION’ otherwise you use ‘UNION ALL’.

No comments: