[SOLVED] Make new query to have same effect as previous one

Make new query to have same effect as previous one

I have the following table:

Region_id | Region_name
1           Europe
2           Americas
3           Asia
4           Middle East and Africa

And this query:

SELECT region_id, region_name
  FROM hr.regions
 GROUP BY CUBE(region_id, region_name);

Which returns:

Region_id | Region_name
(null)      (null)
(null)      Asia
(null)      Europe
(null)      Americas
(null)      Middle East and Africa
1           (null)
1           Europe
2           (null)
2           Americas
3           (null)
3           Asia
4           (null)
4           Middle East and Africa

The question and problem: How do I make another query that returns the same results as above but using SET operations like union and intersect instead of group by cube(...)?

Solution

You would use:

select region_id, region_name
from hr_regions
union all
select NULL as region_id, region_name
from hr_regions
union all
select region_id, NULL as region_name
from hr_regions
union all
select NULL as region_id, NULL as region_name
from dual;

Your example data has not duplicates in either column. so no aggregation or select distinct is needed. The one exception is the last query. To get only one row with two NULL values, it select from dual.