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!