0

I would like to combine two result set together, but there is the case where one, or the other result set is empty. What is happening is that if one of the result set is empty then nothing get return. The way I am doing this now is based off of this SO question. So basically my sql looks like this

Select * from (Select err.is,... FROM table1 err), (Select arr.id,... FROM table2)

Is there a work around?

Community
  • 1
  • 1
Jack Thor
  • 1,554
  • 4
  • 24
  • 53

1 Answers1

0

You probably want union all and not a join, so maybe this will do what you want:

(Select err.is,... FROM table1 err)
union all
(Select arr.id,... FROM table2)

Otherwise, if you really do want the cross join, something like this might work:

with t1 as (
     Select err.is,... FROM table1 err
    ),
     t2 as (
     Select arr.id,... FROM table2
    )
select t1.*, t2.*
from t1 cross join t2
union all
select t1.*, t2.*
from t1 left outer join
     t2
     on 1 = 0
where not exists (select 1 from t2)
union all
select t1.*, t2.*
from t2 left outer join
     t1
     on 1 = 0
where not exists (select 1 from t1);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I tried the union all and I got the error `All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists` haven;t tried the cross join – Jack Thor Jul 17 '14 at 23:28