Diff. b/w where and having in Sql Server

• WHERE clause is used for filtering rows and it applies on each and every row, while HAVING clause is used to filter groups.


• We can use WHERE and HAVING clause together in a SELECT query, WHERE clause is applied first on individual rows and only after grouping HAVING clause is applied.


• Syntactically where is used before GROUP BY clause, while having is used after GROUP BY clause.


• We can't use having clause in update, delete queries, allowed only in select query.


• To use Having Clause, we have to use Group By Clause since it filters data that we get from Group By Clause


• We can use aggregate operations with having, but not with where


• We can use Group By Clause with or without Where Clause.


• select count(1) count  from emp having avg(rating)<10

How a HAVING clause works

• The select clause specifies the columns.


• The from clause supplies a set of potential rows for the result.


• The where clause gives a filter for these potential rows.


• The group by clause divide the rows in a table into smaller groups.


• The having clause gives a filter for these group rows.