where vs having, quite confusing!!!

Question

I find it confusing when to use the HAVING clause versus the WHERE clause in SQL. For example, in a MySQL aggregate function exercise, the solution uses WHERE instead of HAVING, although I learned that HAVING should be used with aggregate functions.

The question is:

What is the total amount of money spent on salaries for all contracts starting after January 1, 1997?

Could you please explain clearly when to use WHERE and when to use HAVING in this context?

Thank you!

Answer 1

Hi Muhammad!
Thanks for reaching out.

The HAVING clause is used because it allows us to apply conditions to groups after they've been created by the GROUP BY clause. This relates to the use of aggregate functions. The WHERE clause is used for individual row conditions before grouping. There are some cases where HAVING should be used, not WHERE. This is the case if we'd like the condition to be applied, after the grouping.

Here's an example:
SELECT e.emp_no AS employee_id, COUNT(s.emp_no) AS salary_count
FROM employees e
JOIN salaries s ON e.emp_no = s.emp_no
GROUP BY e.emp_no
HAVING SUM(s.salary) > 150000 AND salary_count >= 15;

In other situations, WHERE and HAVING can be used interchangeably (indeed, when no aggregate functions are used). For example:
SELECT * FROM employees WHERE emp_no = 10012;
SELECT * FROM employees HAVING emp_no = 10012;

Hope this helps.
Best,
Martin

where vs having, quite confusing!!!

Submit an answer

related questions