Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

4 Answers 4

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.

share|improve this answer
2  
Another example: SELECT MIN(a) AS mina, MAX(a) As maxa FROM mytable HAVING MIN(a) < MAX(a); –  ypercube Jan 24 at 9: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 at 9: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 at 9: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 at 9: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 at 9:26

Having is used to filter groups .

where clause is used to filter rows.

share|improve this answer
    
    
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 at 8:52

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.

share|improve this answer

As HAVING is used to test conditions of groups, there have to be a GROUP BY in the query, in other case, HAVING clause would have no sense.
So answering your question, yes, they have to co-exist.

share|improve this answer
1  
Incorrect. See my example. –  Colin 't Hart Jan 24 at 8:44
1  
It has to co-exist if you want your HAVING clause have any sense. Obviously, you can consider your all select as a big GROUP, so you can apply a HAVING condition to them, but it has any sense to do so... –  xarmengol Jan 24 at 9:21
    
Not true. @ypercube came up with the example SELECT MIN(a) AS mina, MAX(a) As maxa FROM mytable HAVING MIN(a) < MAX(a); that contradicts this. –  Colin 't Hart Jan 24 at 9:24
    
@xarmengol When there is a HAVING clause without a GROUP BY, the query is (or should be) interpreted as if it had GROUP BY () (ie. as you say group all the rows into one big group.) –  ypercube Jan 24 at 9:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.