HAVING and WHERE clause difference in SQL

Question

As I've read everywhere, HAVING clause and WHERE clause performs a sort of similar function but the WHERE clause condition applies before grouping and the HAVING applies after. What exactly does this mean? What if I don't group them before hand? Do I just get the same results?

Answer 1

Most databases do not really support having with no group by. So, the difference between the two is really simple. You can use aggregation functions in having. They cause a syntax error in where. Really simple. Can't make a mistake.

I am guessing that you are using MySQL. This database extends the use of having. It allows it in non-aggregation queries, which is highly non-standard. In doing so, it allows the use of table aliases in the condition.

So this generates an error (assuming that x.foo is not a column):

select x.col as foo
from x
where foo = 3;

However, this is allowed in MySQL:

select x.col as foo
from x
having foo = 3;

In other databases, this would generate a syntax error.

This is a convenience. In most databases, you would use a subquery (which you can still do in MySQL) or a CTE (which is now allowed in MySQL 8.0).