Optimize a query containing UNION

Database
N/A

Optimize a query containing UNION

Table1:
Fields - Reg_no, subject1, subject2, subject3, subject4, subject5, subject6,source.
Table2:
Fields - Reg_no, subject_nm

I need to load data from the above 2 tables into a new table with only 2 fields - Reg_no and subject. I use the following select statement to pull data from table1 and table2.

select Reg_no,subject1
from table1
where subject1 is not null
and source = 'IS';

UNION

select Reg_no,subject2
from table1
where subject2 is not null
and source = 'IS';

UNION

select Reg_no,subject3
from table1
where subject3 is not null
and source = 'IS';

UNION

select Reg_no,subject4
from table1
where subject4 is not null
and source = 'IS';

UNION

select Reg_no,subject5
from table1
where subject5 is not null
and source = 'IS';

UNION

select Reg_no,subject6
from table1
where subject6 is not null
and source = 'IS';

UNION

select Reg_no,subject_nm
from table2;

Is there a way to optimize this query?
2 REPLIES
Teradata Employee

Re: Optimize a query containing UNION

Hello,

Create a VT table (suppose vtTable1) with 1 column and insert all the subject names in it, e.g.:

Then use it in cross-join with the select statement as:

select a.Reg_no, b.subject
from table1 a cross join vtTable1 b
where a.subject1 is not null
and a.source = 'IS';

I haven't tried it so do match the results and let know if they are exactly as you like. :)

Regards,

Adeel
Teradata Employee

Re: Optimize a query containing UNION

Little change:

select a.Reg_no, b.subject
from table1 a cross join vtTable1 b
where a.subject1 is not null
AND a.subject2 is not null
AND a.subject3 is not null
AND a.subject4 is not null
AND a.subject5 is not null
and a.source = 'IS';

HTH

Regards,

Adeel