At times we are faced with a complex GROUP BY query written by someone else and figuring out the equivalent UNION ALL query can help us better understand its results. This is not as easy as it first may seem. A query like this, for example,
set null (null) select grp_a , grp_b , nvl( grp_b, grp_a ) as nvl_grp_a_b , nvl2( grp_b, 1, 0 ) as nvl2_grp_b , count(*) from t GROUP BY ROLLUP ( GRP_A, GRP_B ) order by grp_a , grp_b ;
GRP_A GRP_B NVL_GRP_A_ NVL2_GRP_B COUNT(*) ---------- ---------- ---------- ---------- ---------- a1 b1 b1 1 2 a1 b2 b2 1 3 a1 (null) a1 0 5 a2 b3 b3 1 3 a2 (null) a2 0 3 (null) (null) (null) 0 8
is not simply the result of unioning together three identical subqueries with different GROUP BY clauses.
set null '(null)' select grp_a , grp_b , nvl( grp_b, grp_a ) as nvl_grp_a_b , nvl2( grp_b, 1, 0 ) as nvl2_grp_b , count(*) from t GROUP BY () UNION ALL select grp_a , grp_b , nvl( grp_b, grp_a ) as nvl_grp_a_b , nvl2( grp_b, 1, 0 ) as nvl2_grp_b , count(*) from t GROUP BY ( GRP_A ) UNION ALL select grp_a , grp_b , nvl( grp_b, grp_a ) as nvl_grp_a_b , nvl2( grp_b, 1, 0 ) as nvl2_grp_b , count(*) from t GROUP BY ( GRP_A, GRP_B ) order by grp_a , grp_b ; grp_a * ERROR at line 2: ORA-00979: not a GROUP BY expression
As you can see, such a query produces an error because the first and second subqueries select columns that are not in the GROUP BY clause. To determine the real equivalent UNION query we can use the following algorithm.
Step 1
Replace any ROLLUP or CUBE operators with their equivalent GROUPING SETS operator. In our example the query
select grp_a , grp_b , nvl( grp_b, grp_a ) as nvl_grp_a_b , nvl2( grp_b, 1, 0 ) as nvl2_grp_b , count(*) from t GROUP BY ROLLUP ( GRP_A, GRP_B ) order by grp_a , grp_b ;
GRP_A GRP_B NVL_GRP_A_ NVL2_GRP_B COUNT(*) ---------- ---------- ---------- ---------- ---------- a1 b1 b1 1 2 a1 b2 b2 1 3 a1 (null) a1 0 5 a2 b3 b3 1 3 a2 (null) a2 0 3 (null) (null) (null) 0 8
is replaced with
select
grp_a
, grp_b
, nvl( grp_b, grp_a ) as nvl_grp_a_b
, nvl2( grp_b, 1, 0 ) as nvl2_grp_b
, count(*)
from
t
GROUP BY GROUPING SETS
(
()
, ( GRP_A )
, ( GRP_A, GRP_B )
)
order by
grp_a
, grp_b
;
GRP_A GRP_B NVL_GRP_A_ NVL2_GRP_B COUNT(*) ---------- ---------- ---------- ---------- ---------- a1 b1 b1 1 2 a1 b2 b2 1 3 a1 (null) a1 0 5 a2 b3 b3 1 3 a2 (null) a2 0 3 (null) (null) (null) 0 8
Step 2a
Next start with a query that groups by only the first term in the GROUPING SETS clause, which is an empty set in our example.
select grp_a , grp_b , nvl( grp_b, grp_a ) as nvl_grp_a_b , nvl2( grp_b, 1, 0 ) as nvl2_grp_b , count(*) from t GROUP BY () ;
If the SELECT list contains columns that are not in the GROUP BY clause then replace those columns with NULL. In the query above both GRP_A and GRP_B are absent from the GROUP BY clause so we replace all occurrences of these columns in the SELECT list with NULL.
column grp_a format a6 column grp_b format a6 column nvl_grp_a_b format a11 column nvl2_grp_b format 999999999 select NULL as grp_a , NULL as grp_b , nvl( NULL, NULL ) as nvl_grp_a_b , nvl2( NULL, 1, 0 ) as nvl2_grp_b , count(*) from t GROUP BY () ;
GRP_A GRP_B NVL_GRP_A_B NVL2_GRP_B COUNT(*) ------ ------ ----------- ---------- ---------- (null) (null) (null) 0 8
Step 2b
Now we repeat the first step using the second term in the GROUPING SETS clause, ( GRP_A ).
select grp_a , grp_b , nvl( grp_b, grp_a ) as nvl_grp_a_b , nvl2( grp_b, 1, 0 ) as nvl2_grp_b , count(*) from t GROUP BY ( GRP_A ) ;
This time GRP_B is in the SELECT list but it is not in the GROUP BY list. We therefore need to replace GRP_B with NULL.
select grp_a , NULL as grp_b , nvl( NULL, grp_a ) as nvl_grp_a_b , nvl2( NULL, 1, 0 ) as nvl2_grp_b , count(*) from t GROUP BY ( GRP_A ) ;
GRP_A GRP_B NVL_GRP_A_B NVL2_GRP_B COUNT(*) ------ ------ ----------- ---------- ---------- a1 (null) a1 0 5 a2 (null) a2 0 3
Step 2c
For the last set in the GROUPING SETS clause all selected columns are listed in the GROUP BY clause so no further transformation is needed. We can use the original SELECT list as-is.
select grp_a , grp_b , nvl( grp_b, grp_a ) as nvl_grp_a_b , nvl2( grp_b, 1, 0 ) as nvl2_grp_b , count(*) from t GROUP BY ( GRP_A, GRP_B ) ;
GRP_A GRP_B NVL_GRP_A_B NVL2_GRP_B COUNT(*) ------ ------ ----------- ---------- ---------- a1 b1 b1 1 2 a1 b2 b2 1 3 a2 b3 b3 1 3
Step 3
The next step is to combine the three step 2 queries with UNION ALL and add an ORDER BY clause.
select NULL as grp_a , NULL as grp_b , nvl( NULL, NULL ) as nvl_grp_a_b , nvl2( NULL, 1, 0 ) as nvl2_grp_b , count(*) from t group by () UNION ALL select grp_a , NULL as grp_b , nvl( NULL, grp_a ) as nvl_grp_a_b , nvl2( NULL, 1, 0 ) as nvl2_grp_b , count(*) from t group by ( grp_a ) UNION ALL select grp_a , grp_b , nvl( grp_b, grp_a ) as nvl_grp_a_b , nvl2( grp_b, 1, 0 ) as nvl2_grp_b , count(*) from t group by ( grp_a, grp_b ) ORDER BY GRP_A , GRP_B ;
GRP_A GRP_B NVL_GRP_A_B NVL2_GRP_B COUNT(*) ------ ------ ----------- ---------- ---------- a1 b1 b1 1 2 a1 b2 b2 1 3 a1 (null) a1 0 5 a2 b3 b3 1 3 a2 (null) a2 0 3 (null) (null) (null) 0 8
Step 4 (Optional)
Lastly we reduce expressions like
nvl( NULL, NULL )
and
nvl2( NULL , 1, 0 )
to simpler, equivalent terms.
select null as grp_a , null as grp_b , NULL AS NVL_GRP_A_B , 0 AS NVL2_GRP_B , count(*) from t group by () union all select grp_a , null as grp_b , GRP_A AS NVL_GRP_A_B , 0 AS NVL2_GRP_B , count(*) from t group by ( grp_a ) union all select grp_a , grp_b , nvl( grp_b, grp_a ) as nvl_grp_a_b , nvl2( grp_b, 1, 0 ) as nvl2_grp_b , count(*) from t group by ( grp_a, grp_b ) order by grp_a , grp_b ;
GRP_A GRP_B NVL_GRP_A_B NVL2_GRP_B COUNT(*) ------ ------ ----------- ---------- ---------- a1 b1 b1 1 2 a1 b2 b2 1 3 a1 (null) a1 0 5 a2 b3 b3 1 3 a2 (null) a2 0 3 (null) (null) (null) 0 8
Result
The end result of the last step is a query which returns the same rows as the original GROUPING SETS query, which is repeated below for your convenience.
select grp_a , grp_b , nvl( grp_b, grp_a ) as nvl_grp_a_b , nvl2( grp_b, 1, 0 ) as nvl2_grp_b , count(*) from t GROUP BY ROLLUP ( GRP_A, GRP_B ) order by grp_a , grp_b ;
GRP_A GRP_B NVL_GRP_A_B NVL2_GRP_B COUNT(*) ------ ------ ----------- ---------- ---------- a1 b1 b1 1 2 a1 b2 b2 1 3 a1 (null) a1 0 5 a2 b3 b3 1 3 a2 (null) a2 0 3 (null) (null) (null) 0 8