Search code examples
oracle-databasegroup-byhaving

Oracle - Why HAVING clause can be before GROUP BY

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.

Solution

  • 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)