4

Why/How HAVING clause can be before GROUP BY

select count(1) from tableA
having id >1
group by id
order by count(1)

I know HAVING clause can be without GROUP BY clause,

But when defining GROUP BY why HAVING isn't forced so to be after, as ORDER BY clause?

Derby processes a SelectExpression in the following order:

  • FROM clause
  • WHERE clause
  • GROUP BY (or implicit GROUP BY)
  • HAVING clause
  • ORDER BY clause

In all docs it appears after GROUP BY:

GROUP BY WORKDEPT 
HAVING MAX(SALARY) < (SELECT AVG(SALARY)
                         FROM EMPLOYEE
                         WHERE NOT WORKDEPT = EMP_COR.WORKDEPT)

EDIT Oreilly article states order is not important (but not why)

The order of the GROUP BY clause and the HAVING clause in a SELECT statement is not important. We can specify the GROUP BY clause before the HAVING clause, or vice versa

I suspect because HAVING can be without GROUP BY clause then order isn't enforced.

Ori Marko
  • 56,308
  • 23
  • 131
  • 233
  • This must be the overloaded `HAVING` clause, similar to MySQL. Your `HAVING` clause is actually being evaluated as a `WHERE` clause. – Tim Biegeleisen May 19 '19 at 11:04
  • @TimBiegeleisen It seems that the `HAVING` clause is executed after `GROUP BY` – Ori Marko May 19 '19 at 11:06
  • It doesn't matter for a restriction on `id`, which is also the column by which you are grouping. Throwing away the entire group after `GROUP BY`, or throwing way all records in the group before `GROUP BY` is the same thing. – Tim Biegeleisen May 19 '19 at 11:10
  • Logically I agree `having` belongs after `group by`. However, you can write it back to front if you want. – William Robertson May 19 '19 at 18:31

1 Answers1

1

because the having clause work on the result (filter the result of the select )

a query select could be base only on the select or if is an aggreagtion query
the result is produced after the GROUP BY this way sematically the HAVING is placed ad the end of the query CLAUSE .. just before the presentation clause (ORDER BY)

ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • 1
    This is not a complete answer, provides no doc references, etc. – Tim Biegeleisen May 19 '19 at 11:14
  • The link is to something called "Java DB". It seems to have the same syntax as normal Oracle SQL though, whatever it is. – William Robertson May 19 '19 at 18:25
  • 1
    @WilliamRobertson - "Java DB" is Oracle's distribution of [Apache Derby](https://en.wikipedia.org/wiki/Apache_Derby), a relational database written in (surprise, surprise) Java. IIRC Derby (originally JBMS; also known as Cloudscape) had its statement syntax (but not datatypes) modeled after Oracle. – Bob Jarvis - Слава Україні May 19 '19 at 19:23