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:

1SELECT COUNT(*)
2FROM employee;

Result:

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

1SELECT COUNT(*)
2FROM employee
3WHERE 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:

1UPDATE employee
2SET salary = salary * 1.1
3WHERE 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.

1DELETE FROM employee
2WHERE 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.

1SELECT department_id,
2SUM(salary) AS total_sal
3FROM employee
4GROUP BY department_id
5ORDER BY department_id;

Result:

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.

1SELECT department_id,
2SUM(salary) AS total_sal
3FROM employee
4WHERE hire_date > '01-JAN-2013'
5GROUP BY department_id
6ORDER BY department_id;

Result:

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.

1SELECT department_id,
2SUM(salary) AS total_sal
3FROM employee
4WHERE hire_date > '01-JAN-2013'
5GROUP BY department_id
6HAVING SUM(salary) > 1000000
7ORDER BY department_id;

Result:

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.

1SELECT department_id,
2SUM(salary) AS total_sal
3FROM employee
4WHERE hire_date > '01-JAN-2013'
5GROUP BY department_id
6HAVING SUM(salary) > 1000000
7ORDER BY department_id;

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

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

However, we get an ORA-00979 error:

1ORA-00979: not a GROUP BY expression
200979. 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:

1SELECT department_id,
2SUM(salary) AS total_sal
3FROM employee
4GROUP BY department_id, hire_date
5HAVING SUM(salary) > 1000000
6AND hire_date > '01-JAN-2013'
7ORDER BY department_id;

Results:

1No 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.