The Where clause is one of the mostly commonly used clauses in the SQL language. On the other hand, the use of the Having clause is somewhat confusing for many users. Although Where and Having can be used to filter data in SQL, they should be used in distinct situations.
Where can be used to compare the values of column and of literal values. It filters data before its aggregation (when Group by is used) and before the computation of aggregate functions (like sum, count and average).
On the other hand, the Having clause is applied after data aggregation and the computation of aggregate functions. In fact, having should be used to compare the results of aggregate functions with literal values or to compare the results from aggregate functions.
Let’s consider the following table that stores data about company’s employees:
In the following query, we look for the number of employees and the average salary per department, considering only the employees whose salary is greater than 85.000.
The execution result of query A would have two lines as follows:
Now, let’s consider the use of Having to filter na average salary, like represented in Query B.
The execution result of query B would be as represented in the floowing image.
In query A, only the lines of employees whose salaries are greater than 85000 are considered in the Count and Avg functions. Therefore, John and Mary are not considered and the average salary per department is computed considering only the other three lines in the table. The query resultset have two lines, one for each department and presents that a total of 3 employees (two from IT and one from Sales) have a salary greater than 85000.
In query B, the having is used to filter data the department whose average salary is greater than 85000. Hence, all 5 lines (even the ones in which salary is lower than 85000) are considered in order to compute Count and Avg. After that, the result of Avg(salary) of each department is compared to 85000. In this case, only the IT department has an average salary greater than 85000. But in the query results, the count function present that IT has 3 employees, which also considers the one whose salary is lower than 85000.