Pages

Wednesday, October 13, 2010

In my presentation and paper about grouping last year, I mentioned that an aggregation query without a group by clause is the same as grouping by the empty grouping set. So this query:

SQL> select count(*) 2 from emp 3 /

COUNT(*)---------- 14

1 rij is geselecteerd.

is the same as:

SQL> select count(*) 2 from emp 3 group by () 4 /

COUNT(*)---------- 14

1 rij is geselecteerd.

However, this is not always true (also known as "false"). It is not true when you aggregate over zero rows. If I repeat the query above, but filter on deptno = 40 (there are no employees in that department), then there is an odd difference:

SQL> select count(*) 2 from emp 3 where deptno = 40 4 /

COUNT(*)---------- 0

1 rij is geselecteerd.

SQL> select count(*) 2 from emp 3 where deptno = 40 4 group by () 5 /

Er zijn geen rijen geselecteerd.

It's not that the empty grouping set is weird or anything, it's the fact that a group by clause is present. The documentation states:

"In a query containing a GROUP BY clause, the elements of the select list can be aggregate functions, GROUP BY expressions, constants, or expressions involving one of these. Oracle applies the aggregate functions to each group of rows and returns a single result row for each group.

If you omit the GROUP BY clause, then Oracle applies aggregate functions in the select list to all the rows in the queried table or view."

So it seems that the internal implementation looks something like this in pseudocode:

if not "group by clause present"then return exactly one rowelse return as much rows as there are groupsend if

Even if we are grouping by a regular column instead of the empty grouping set, we get a "No rows selected":