Difference Between ‘WHERE’ and ‘HAVING’ clauses – When & How to Use Which

Posted: April 17, 2013 by MaxFraudy in MS Access, SQL
Tags: ,

There is often a confusion in the minds of most people when it comes to using the HAVING clause. On the surface of it, it looks very similar to how a WHERE clause works, but that is not the case. While WHERE clause filters out records before data aggregation is done, HAVING does it later.

Let’s take an example to understand it better. I have a dataset of employee salaries with details of their departments.

Employee_ID                    Dept                                 Salary
E10002                                HR                                   14000
E10050                                IT                                    20000
E15334                                HR                                   15000
E33336                                IT                                    16500
E35480                                Legal                                30000
E45670                                Legal                                29000
E74016                                HR                                   12000

Suppose I want to find out the average salary of the HR and the IT department. I will write the following query:

          SELECT dept, AVG(salary) AS AVG_SAL
         FROM employees
        WHERE dept IN (“HR”, “IT”)
        GROUP BY dept

What WHERE clause does here is that it excludes those records that do not meet the specified criteria and then calculates averages based on the remaining records.

Employee_ID                    Dept                                Salary            
E10002                                HR                                   14000                    The output is:
E10050                                IT                                    20000                   Dept                            AVG_SAL
E15334                                HR                                   15000                    HR                                  14500
E33336                                IT                                    16500                    IT                                   18250
E35480                                Legal                                30000
E45670                                Legal                                29000
E74016                                HR                                   12000

Now, let’s say I want to see which all departments give an average salary of more than 15,000. In this case, I need to filter on a calculated number. So, my query would be:

          SELECT dept, AVG(salary) AS AVG_SAL
         FROM employees                         
        WHERE dept IN (“HR”, “IT”)
        GROUP BY dept
       HAVING AVG(salary) > 15000

This query works on the result set that we got from the first query and filters out records based on HAVING clause.

Dept                            AVG_SAL                                                  The output is:
HR                                  14500                                                            Dept                 AVG_SAL
IT                                    18250                                                            IT                       18250

Important Note: Please note that the HAVING clause can be used only when there is an aggregation (GROUP BY). It can be used for aggregate functions like SUM, COUNT, AVG, MAX etc.

Advertisements

Share your thoughts/feedback

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s