All these three Clauses are a part/extensions of a SQL Query, are used to Filter, Group & re-Filter rows returned by a Query respectively, and are optional. Being Optional they play very crucial role while Querying a database.
1. WHERE clause specifies search conditions for the rows returned by the Query and limits rows to a meaningful set.
2. GROUP BY clause works on the rows returned by the previous step #1. This clause summaries identical rows into a single/distinct group and returns a single row with the summary for each group, by using appropriate Aggregate function in the SELECT list, like COUNT(), SUM(), MIN(), MAX(), AVG(), etc.
3. HAVING clause works as a Filter on top of the Grouped rows returned by the previous step #2. This clause cannot be replaced by a WHERE clause and vice-versa.
This Query returns around 32k (thousand) rows form SalesOrderHeader table. Thus, if somebody wants to do some analysis on this big row-set it would be very difficult and time consuming for him.
–> Use Case: Let’s say a Sales department wants to get a list of such Customers who bought more number of items last year, so that they can sell more some stuff to them this year. How they will go ahead?
This Query still gives ~12k records and its still difficult to identify such Customers who have more orders.
This query still returns ~10k records, and I’ve go through the entire list of records to identify such records. Is there any way where I can still filter out the unwanted records with lesser count?
Thus, by using all these these clauses we can reduce and narrow down the row-set to do some quick analysis.
Check this video tutorial on WHERE clause and difference with GROUP BY & HAVING clause.
Does the HAVING clause make use of indexes of the underlying table or is it actually performed on the (materialised) data from the GROUP BY output?