If "HAVING" is more versatile than "WHERE" in SQL then why bother with it?

SELECT AVG(salary) AS avg_salary 
FROM employees
WHERE salary > 50000;

SELECT salary 
FROM employees
HAVING AVG(salary) > 50000;


SELECT *
FROM employees
HAVING department = 'IT';

If "HAVING" is more versatile than "WHERE" in SQL then why bother with it?

SELECT AVG(salary) AS avg_salary 
FROM employees
WHERE salary > 50000;

SELECT salary 
FROM employees
HAVING AVG(salary) > 50000;


SELECT *
FROM employees
HAVING department = 'IT';

• sql
• mysql
• having

• Because internally they work differently and generally (see for instance stackoverflow.com/questions/328636/…) the WHERE will be faster than the HAVING – derpirscher Commented Feb 8 at 9:23
• the WHERE statement is faster than HAVING because it eliminates rows before aggregation making it significantly faster than HAVING which filters after aggregation. if you want to do filtering after an aggregation HAVING is much "versatile" but in general, wherever possible, try to filter before aggregating for more efficient queries. – jeffreyohene Commented 2 hours ago

1 Answer 1

Using HAVING in a SQL query which does not have GROUP BY is a language extension particular to MySQL, and is not part of the ANSI standard. The main benefit from using HAVING this way is that it admits referring to an alias defined earlier in the SELECT clause. Note that it is strictly not allowed to refer to an alias in a WHERE clause.

A valid example of using HAVING this way would be:

SELECT salary, salary / 12 AS monthly_salary
FROM employees
HAVING monthly_salary > 5000;

Here we could not use WHERE the same way:

SELECT salary, salary / 12 AS monthly_salary
FROM employees
WHERE monthly_salary > 5000;  -- error; not allowed

Note that one your example queries would generate an aggregation error when MySQL operates in ONLY_FULL_GROUP_BY mode:

SELECT salary 
FROM employees
HAVING AVG(salary) > 50000;

The problem with the above is that AVG(salary) is taken over the entire table, and hence it is not clear which salary value you want to select.