Alternative for union in Oracle

Question

Is there any alternate way to fetch the following data without using union?

select A.name,A.age,B.Address,C.phoneNo from table1 A,Table2 B,Table3 C where a.pkId = b.FkId and b.pkId = c.FkId
union
select A.name,A.age,B.Address,C.phoneNo from table4 A,Table5 B,Table3 C where a.pkId = b.FkId and b.pkId = c.FkId

I am using this in Hibernate and unfortunately hibernate doesnt support Union. I was just wondering if there is any other way to achieve it else ill have to write it in a procedure and save the data in temp table and fire a sql to read data from that temp table

Actually I am using this in Hibernate and unfortunately hibernate doesnt support Union. I was just wondering if there is any other way to achieve it else ill have to write it in a procedure and save the data in temp table and fire a sql to read data from that temp table — user2093576, Jun 29 '16 at 11:14

score 1 · Answer 1 · answered Jun 29 '16 at 11:04

There is an alternative for union, but it is not pretty:

select distinct coalesce(x1.name, x2.name) as name,
       coalesce(x1.age, x2.age) as age,
       coalesce(x1.Address, x2.Address) as age,
       coalesce(x1.phoneNo, x2.phoneNo) as age,
from (select A.name, A.age, B.Address, C.phoneNo
      from table1 A join
           Table2 B
           on a.pkId = b.FkId join
           Table3 C 
           on b.pkId = c.FkId
     ) x1 full outer join
     (select A.name, A.age, B.Address, C.phoneNo
      from table4 A join
           Table5 B
           on a.pkId = b.FkId join
           Table3 C 
           on b.pkId = c.FkId
     ) x2
     on 1 = 0;  -- always false

I can't imagine why you would want to express a union like this. I would highly recommend, though, that you start using proper, explicit join syntax.

score 0 · Answer 2 · answered Jun 29 '16 at 11:35

is this working ?

SELECT
    CASE DISTINCT_FLG WHEN 1 THEN nameA ELSE nameB END name,
    CASE DISTINCT_FLG WHEN 1 THEN ageA ELSE ageB END age,
    CASE DISTINCT_FLG WHEN 1 THEN AddressA ELSE AddressB END Address,
    CASE DISTINCT_FLG WHEN 1 THEN phoneNoA ELSE phoneNoB END phoneNo
FROM (
    SELECT
        T1.name AS nameA, T1.age AS ageA, T2.Address AS AddressA, T3.phoneNo AS phoneNoA,
        T4.name AS nameB, T4.age AS ageB, T5.Address AS AddressB, T3.phoneNo AS phoneNoB,
        ROW_NUMBER() OVER(PARTITION BY T1.name, T1.age, T2.Address, T4.name, T4.age, T5.Address, T3.phoneNo ORDER BY NULL) AS DISTINCT_FLG
    FROM
        table1 T1,
        table2 T2,
        table4 T4,
        table5 T5,
        table3 T3
    WHERE
        T1.pkId = T2.FkId AND
        T4.pkId = T5.FkId AND
        (
            T2.pkId = T3.FkId OR
            T5.pkId = T3.FkId
        )
) WHERE DISTINCT_FLG IN (1, 2)

score 0 · Answer 3 · edited May 23 '17 at 12:32

Those two UNION parts have Table3 C in common, so we can join the rest to it. To emulate UNION records from the source table can be replicated through unconditional cross join of an auxiliary table with the required number or rows:

Select Distinct
    CASE R.r WHEN 1 THEN A1.name    ELSE A2.name    END As name   ,
    CASE R.r WHEN 1 THEN A1.age     ELSE A2.age     END As age    ,
    CASE R.r WHEN 1 THEN B1.Address ELSE B2.Address END As Address,
    C.phoneNo
From Table3 C,                                          --< Start at common Table3
  (Select Rownum r From USER_TABLES Where Rownum < 3) R --< Two rows to replicate Table3 C
--   Any table with more than one row will do
--   USER_TABLES should have enough rows in this particular case
Left Join Table2 B1 On R.r = 1 AND B1.pkId = C.FkId     --< Left Join branch one
Left Join table1 A1 On R.r = 1 AND A1.pkId = B1.FkId
Left Join Table5 B2 On R.r = 2 AND B2.pkId = C.FkId     --< Left Join branch two
Left Join table4 A2 On R.r = 2 AND A2.pkId = B2.FkId
Where (R.r = 1 AND A1.pkId Is NOT NULL)                 --/ Make sure we have values
   OR (R.r = 2 AND A2.pkId Is NOT NULL)                 --\ for the branch

But really, consider a view.

Alternative for union in Oracle

3 Answers3

Linked