FB

The Difference Between WHERE and HAVING Clause

What is the difference between WHERE and HAVING clause in SQL? Learn what they are and the differences in this article.

Difference Between WHERE and HAVING Clause

Both the WHERE and the HAVING clauses are very similar and are used to restrict the rows that are returned in a SELECT query.

The difference between WHERE and HAVING clauses are:

  • The WHERE clause is used to filter rows before the grouping is performed.
  • The HAVING clause is used to filter rows after the grouping is performed. It often includes the result of aggregate functions and is used with GROUP BY.

Let’s take a look at the WHERE and HAVING clauses in more detail.

 

The WHERE Clause

The WHERE clause in SQL is used to filter records. You can use it with SELECT, DELETE and UPDATE queries.

For example, this query on our sample data:

SELECT COUNT(*)
FROM employee;

Result:

COUNT(*)
200

This shows 200 records. If we add a WHERE clause:

SELECT COUNT(*)
FROM employee
WHERE salary > 50000;

Result:

COUNT(*)
134

We get 134 rows. So the WHERE clause has only considered records where the salary > 50000.

We can also use the WHERE clause in UPDATE statements:

UPDATE employee
SET salary = salary * 1.1
WHERE department_id = 5;

Using a WHERE clause in an UPDATE statement is almost always required, because it’s not very often you want to update all records in a table.

The same thing can be said for the DELETE statement.

DELETE FROM employee
WHERE department_id = 7;

You’re probably more likely to delete some data from a table, instead of every record.

 

The HAVING Clause

The HAVING clause, on the other hand, is used to filter data after the grouping has been applied in the GROUP BY clause.

You can use the WHERE clause with groups as well. But there are some differences when writing SQL HAVING vs WHERE clauses.

Let’s say you wanted to find the SUM of salaries per department.

SELECT department_id,
SUM(salary) AS total_sal
FROM employee
GROUP BY department_id
ORDER BY department_id;

Result:

DEPARTMENT_ID TOTAL_SAL
1 305000
2 2142000
3 2233000
4 1243000
5 2491000
6 1733000
7 1587000
8 1560000
 (null) 212000

You could also filter out records using WHERE, so that the SUM only considers records that match the WHERE clause.

This WHERE clause is run before the grouping.

SELECT department_id,
SUM(salary) AS total_sal
FROM employee
WHERE hire_date > '01-JAN-2013'
GROUP BY department_id
ORDER BY department_id;

Result:

DEPARTMENT_ID TOTAL_SAL
1 82000
2 1347000
3 1256000
4 966000
5 1102000
6 925000
7 809000
8 630000
212000

This shows the SUM of salary per department for employees hired after 1st Jan, 2013.

Now, what if we wanted to show only departments that had a SUM of salary greater than 1,000,000?

We would need to use HAVING. This is because the value we want to filter on (the SUM) is an aggregate function and is calculated after the GROUP BY.

So, we add a HAVING clause.

SELECT department_id,
SUM(salary) AS total_sal
FROM employee
WHERE hire_date > '01-JAN-2013'
GROUP BY department_id
HAVING SUM(salary) > 1000000
ORDER BY department_id;

Result:

DEPARTMENT_ID TOTAL_SAL
2 1347000
3 1256000
5 1102000

This now only shows the departments that match the HAVING criteria.

 

Can I Use HAVING Instead Of WHERE?

Now we’ve looked at the difference between WHERE and HAVING clause.

If they both filter records, can I just use HAVING instead of WHERE?

Well, sometimes you can, and sometimes you can’t.

It depends on what you’re filtering on.

Take our earlier query, for example.

SELECT department_id,
SUM(salary) AS total_sal
FROM employee
WHERE hire_date > '01-JAN-2013'
GROUP BY department_id
HAVING SUM(salary) > 1000000
ORDER BY department_id;

If we wanted to remove the WHERE and just use HAVING, we could try that:

SELECT department_id, SUM(salary) AS total_sal
FROM employee
GROUP BY department_id
HAVING SUM(salary) > 1000000
AND hire_date > '01-JAN-2013'
ORDER BY department_id;

However, we get an ORA-00979 error:

ORA-00979: not a GROUP BY expression
00979. 00000 -  "not a GROUP BY expression"

This is because we’re trying to use the HAVING clause on a field (hire_date) that is not in the GROUP BY section.

You’ll get similar errors in other databases (e.g. SQL Server).

If we add hire_date to the GROUP BY section, it will cause more issues:

SELECT department_id,
SUM(salary) AS total_sal
FROM employee
GROUP BY department_id, hire_date
HAVING SUM(salary) > 1000000
AND hire_date > '01-JAN-2013'
ORDER BY department_id;

Results:

No results.

There are no results shown because our GROUP BY structure has changed, and there are now no records that have a SUM greater than 1,000,000.

So, in many cases, you can’t simply replace the WHERE with a HAVING. The SQL HAVING vs WHERE clauses are similar but there are some differences and restrictions.

 

I hope this article explains the difference between WHERE and HAVING clause for you. If you have any questions, leave a comment below.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

9 thoughts on “The Difference Between WHERE and HAVING Clause”

    1. Why did this query return no results? Can you please explain in more details.

      There are no results shown because our GROUP BY structure has changed, and there are now no records that have a SUM greater than 1,000,000.

      Please explain further.

      SELECT department_id,
      SUM(salary) AS total_sal
      FROM employee
      GROUP BY department_id, hire_date
      HAVING SUM(salary) > 1000000
      AND hire_date > ’01-JAN-2013′
      ORDER BY department_id;

      1. Hi Miranda, without knowing your data I can take a guess here. I think it’s because the data is being grouped by combinations of department_id and hire_date, and the SUM is calculated on that group. So, even if one department_id has a SUM of greater than 1,000,000, I think it’s not showing because the SUMs are being calculated for each hire_date as well.
        You could try adding the hire_date column to the query to see what the data looks like:
        SELECT department_id,
        hire_date,
        SUM(salary) AS total_sal
        FROM employee
        GROUP BY department_id, hire_date
        HAVING SUM(salary) > 1000000
        AND hire_date > ’01-JAN-2013′
        ORDER BY department_id;

  1. I LOVE that I have found this resource. I’m an IT newbie having moved from HR as I work on the HR system but the work I do fits better in IT. I was sent on a SQL course and it has changed my life! I constantly get requests for data but sometimes I need to reassure myself of the best way to build a query or remind myself of basic concepts! Thankyou for taking the time to put it into simplistics so newbies like me can learn!

  2. select dept_no,emp_name,sum(salary)
    from emp
    group by dept_no
    having sum(salary)>2000;

    why it is not posssible to use more columns in select statement while using group by?

    1. This is because the database won’t know what you want to show.
      In your query, what does it show when there are two different emp_name values? If it shows both emp_name values, then the data isn’t only being grouped by dept_no.
      There are ways to show this kind of result, using window functions, but using a group by means the select clause needs to be similar to the group by clause.

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.