Single select to fetch from two tables

sijayapal · New User Joined: 23 Dec 2008 Posts: 69 Location: India

There are two tables. Table 1 and table 2. With identical column called "name"
In a single select i must get names from both the tables.
one way is

Select name from table1
Where condition
union all
select name from table2
Where conditon

But i dont want to do in union.

I tried with

Select case when t1.name = '' then t2.name
when t1.name is null then t2.name
else t1.name
end as name
from table1 t1, table2 t2
Where conditions.

but this gave me results only from table1.. its skipping table2 since null and '' conditions are never met...
So i need to include some condition to check end of rows in table 1. I need to know how to find the end of rows in table1.

bhairon singh rathore · New User Joined: 19 Jun 2008 Posts: 91 Location: banglore

why you dont want to use union .....please clarify

sijayapal · New User Joined: 23 Dec 2008 Posts: 69 Location: India

Already Union is being used.
My work is to tune long running jobs.
This job takes around 300minutes of elapsed time and just 2 mins of cpu time. Most of the elapsed time is in the query which used union.
So i am trying other methods to replace the query.

vinodgampa · New User Joined: 20 Apr 2005 Posts: 1 Location: Hyderabad

use outer joins for this one

enrico-sorichetti · Posted: Wed Aug 26, 2009 2:02 pm

just to find out if the time ( elapsed/cpu ) is reasonable

run a query on table 1, again on table 2

and if the sum of the two times is comparable ( within a reasonable range ) to the query with the union...

then it' s not a UNION issue... simply there is a lot of work to do

sijayapal · New User Joined: 23 Dec 2008 Posts: 69 Location: India

Let me give the actual query.
Is there any possiblity of removing the union?
In the mean time let me try with splitting the query

EXEC SQL
DECLARE CUSTPRD-CRS CURSOR FOR
SELECT ACTG_ID
,ACTG_VAL_TXT
FROM WALT_ACTG_VAL_REL
WHERE NTRL_ACCT_ID = :WAL-NTRL-ACCT-ID
UNION ALL
SELECT A.ACTG_ID
,A.ACTG_ITM_CDE ||' '||
B.ACTG_ITM_TXT
||' '
AS ACTG_VAL_TXT
FROM WAKT_ACTG_ITM_REL A
,WAHT_ACTG_ITM B
WHERE A.NTRL_ACCT_ID = :WAK-NTRL-ACCT-ID
AND A.ACTG_ID = B.ACTG_ID
AND A.ACTG_ITM_CDE = B.ACTG_ITM_CDE
FOR FETCH ONLY
END-EXEC.

apandey · New User Joined: 31 Aug 2009 Posts: 73 Location: Mumbai

The better way is to use joins. Joins are more efficient if you want data from both the tables. Outer Join will be give u records from both the table.
Using union, DB2 internally created resultset of 2 tables merges it to single and them DISPLAY the results, It also checks for duplicate fields,if duplicate field found then it is skipped. While in Outer Join,the records will
be Displayed as all without removing duplicates and creating single resultset of both the tables

GuyC · Posted: Mon Aug 31, 2009 9:06 pm

apandey · New User Joined: 31 Aug 2009 Posts: 73 Location: Mumbai

Dude,read my statements clearly, I have spoken abt UNION not abt
UNION ALL, the points which u have mentioned abt Union all, i am aware of that points. Regarding Outer join, i have tested the difference between
JOINS and UNION ALL, i found JOINS are much more effective in terms
of performance wise.

GuyC · Posted: Tue Sep 01, 2009 1:48 pm

dude, read the sql it says 'union all' so your "advice" was ,although interesting, besides the point.
I'm glad your joins are faster then union alls, but performance has nothing to do with union all vs outer joins, but everything with available indexes.

If you really want to test something, test this :
- Table A PK&ix on column A1 clustering ix on column A2
- Table B PK&&clustering ix on column B1,B2 ; ix on column A1