In order to use
HAVING in SQL queries , must there be a
GROUP BY to aggregate the column names?
Are there any special cases where it is possible to use
HAVING without a
GROUP BY in SQL queries?
Must they co-exist at the same time?
They don't have to coexist, as proved by the fact that the following query in Oracle works:
Similarly, in PostgreSQL the following query works:
Having is applied after the aggregation phase and must be used if you want to filter aggregate results. So the reverse isn't true, and the following won't work:
You need to replace
NB The following query form will also work:
You can see that using
HAVING is filtering the groups. If you have not GROUP BY cause, all rows presents one group. So, if predicate in HAVING evaluates as true, you get one row, otherwise no rows.
In the absence of GROUP BY clause the query considers the whole relation as one group.