Where vs Having clause in SQL statement

user_11451388 0
Viewed 71

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'
sql
3 Answers

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
user_115656290

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.

user_11440350

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://docs.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

user_59547640
Related