Where vs Having clause in SQL statement

Question

Both SQL statements return the same result. So what is the difference when using where clause vs using a having clause?

Any idea?

select  
    min(internid), max(internid) 
from  
    v_intern
where   
    trainingterm= 'Fall - September' and trainingyear = '2020'
group by 
    trainingTerm, trainingYear 


select  
    min(internid), max(internid) 
from  
    v_intern
group by 
    trainingTerm, trainingYear 
having  
    trainingterm = 'Fall - September' and trainingyear = '2020'

[Take a look at this post, maybe it'll help u.](https://stackoverflow.com/questions/287474/what-is-the-difference-between-having-and-where-in-sql) — Noah, Dec 17 '20 at 11:46
Does this answer your question? [What is the difference between HAVING and WHERE in SQL?](https://stackoverflow.com/questions/287474/what-is-the-difference-between-having-and-where-in-sql) — Jelle, Dec 18 '20 at 11:36

score 1 · Answer 1 · answered Dec 17 '20 at 11:47

In where clause you can not use the aggregate function.

In the Having clause you can use the aggregate function.

So In your query, If the requirement is something like min(internid) should be greater then X value then you can use It in the HAVING clause as WHERE clause does not serve that purpose as follows(But yes in the HAVING clause you can write the normal conditions too but that conditions are checked after groupping):

select min(internid), max(internid) from v_intern  
where trainingterm= 'Fall - September' and trainingyear ='2020'
group by trainingTerm, trainingYear
having min(internid) > x

Gordon Linoff · Answer 2 · 2020-12-18T11:32:45.453

The difference is how the query is processed. These two queries are functionally equivalent:

select min(internid), max(internid)
from v_intern
where trainingterm = 'Fall - September' and trainingyear = '2020'
group by trainingTerm, trainingYear;

select min(internid), max(internid)
from v_intern
group by trainingTerm, trainingYear
having trainingterm = 'Fall - September' and trainingyear = '2020';

However, the first is probably going to be more efficient. Why? If there are no indexes, the WHERE filters the data before aggregation. Less data should improve performance. Under most circumstances, the HAVING aggregates all the data and then does the filtering. However, some databases might recognize this situation and filter before the aggregation.

If there is an index, the first can use the index to get exactly the data needed for the aggregation.

There is a third version that is similar to the first . . . but subtly different:

select min(internid), max(internid)
from v_intern
where trainingterm = 'Fall - September' and trainingyear = '2020';

That is, the aggregation is not needed. This version always returns one row -- even when no rows match. In that case, the values would be NULL.

Some database systems are capable of promoting predicates from `HAVING` to `WHERE` when they don't depend on the group aggregates, as here, so it may be no difference. — Damien_The_Unbeliever, Dec 17 '20 at 11:50
@Damien_The_Unbeliever . . . Can you provide a reference? I don't think I've specifically seen this optimization. — Gordon Linoff, Dec 17 '20 at 14:37
A simple example in SQL Server is to run the query `select name,COUNT(*) from sys.all_objects group by name having name='objects'` - if you examine the query plan, you'll see that the `objects` comparison is a seek predicate on an index seek, well before aggregation is applied. — Damien_The_Unbeliever, Dec 18 '20 at 08:31

score 0 · Answer 3 · answered Dec 18 '20 at 11:37

The WHERE clause is applied first to the individual rows in the tables or table-valued objects in the Diagram pane. Only the rows that meet the conditions in the WHERE clause are grouped.

The HAVING clause is then applied to the rows in the result set. Only the groups that meet the HAVING conditions appear in the query output. You can apply a HAVING clause only to columns that also appear in the GROUP BY clause or in an aggregate function.

for more details visit : https://learn.microsoft.com/en-us/sql/ssms/visual-db-tools/use-having-and-where-clauses-in-the-same-query-visual-database-tools?view=sql-server-ver15

Where vs Having clause in SQL statement

3 Answers3