Why can't I just use "HAVING" clause all the time instead of "WHERE"? It works the same basically if you use it before/without "GROUP BY"

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';

Solution

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.