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’.