Use of HAVING without GROUP BY in SQL queries

Question

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?

Colin 't Hart · Accepted Answer · 2014-01-24T08:55:11.470

33

No.

They don't have to coexist, as proved by the fact that the following query in Oracle works:

select * from dual having 1 = 1;

Similarly, in PostgreSQL the following query works:

select 1 having 1 = 1;

So having doesn't require group by.

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:

select a, count(*) as c
from mytable
group by a
where c > 1;

You need to replace where with having in this case, as follows:

select a, count(*) as c
from mytable
group by a
having c > 1;

NB The following query form will also work:

select *
from (
  select a, count(*) as c
  from mytable
  group by a
)
where c > 1;

You can see that using having is simply a shorthand version of this last query.

In summary, having is applied after the group by phase whereas where is applied before the group by phase.

edited Jan 24 '14 at 08:55

answered Jan 24 '14 at 08:42

Colin 't Hart

9,323
15
35
43

2

Another example: SELECT MIN(a) AS mina, MAX(a) As maxa FROM mytable HAVING MIN(a) < MAX(a); – ypercubeᵀᴹ Jan 24 '14 at 09:16
1

Yes. And PostgreSQL allows the following construct select 1 having count(*) = 1; which I have yet to grasp. – Colin 't Hart Jan 24 '14 at 09:22
SQL Server allows too, SELECT 1 AS id, 'Colin' AS name; while others like Oracle have a special dual table. I don't think that either of these syntaxes is ANSI/ISO SQL (which requires FROM). – ypercubeᵀᴹ Jan 24 '14 at 09:23
I didn't mean the lack of from but the reference to count(*) in the having clause without any indication as to over which columns this is being aggregated. Presumably it aggregates over all columns in the select clause. – Colin 't Hart Jan 24 '14 at 09:25
Ah ok. Yes, I agree that that's what it does (aggregate over all rows you mean - over all rows of an empty table). – ypercubeᵀᴹ Jan 24 '14 at 09:26
Related question: What is the correct result for this query? – ypercubeᵀᴹ Jan 24 '14 at 09:34
But select * from foo having id = 1 would not work. – Jan 26 '14 at 08:53

score 7 · Answer 2 · answered Jan 24 '14 at 07:08

7

Having is used to filter groups .

where clause is used to filter rows.

answered Jan 24 '14 at 07:08

sqlengineer

109
5

http://blog.sqlauthority.com/2007/07/04/sql-server-definition-comparison-and-difference-between-having-and-where-clause/ – sqlengineer Jan 24 '14 at 07:11
1

Your first statement is not correct. having is applied after the aggregation phase so can be used to filter groups. – Colin 't Hart Jan 24 '14 at 08:52

score 4 · Answer 3 · answered Nov 21 '14 at 16:36

4

In the absence of GROUP BY clause the query considers the whole relation as one group.

e.g.

     select count(*)
     from dual
     having count(*) > 5;

answered Nov 21 '14 at 16:36

Vikrant Singh

141
1

score 3 · Answer 4 · answered Jan 26 '14 at 07:32

3

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.

answered Jan 26 '14 at 07:32

msi77

1,135
1
7
9

Use of HAVING without GROUP BY in SQL queries

4 Answers4