mysql – What difference between where and having?

Question:

Doing some tests on a MySQL database, I realized that:

SELECT * From client WHERE uuid = '1kvsg4oracxq'

returns the same result as:

SELECT * From client HAVING uuid = '1kvsg4oracxq'

What exactly is the real difference between where and having ?

Answer:

Both work as if they were an if , that is, they filter rows from the database.

WHERE works directly on the line, while HAVING works on results from line aggregators, the most used is with GROUP BY .

Researching I concluded that it is really expected that its use without an aggregator works as a WHERE , it is not a coincidence, although it is a liberality, there is nothing in the specification that says it should be so. According to Jefferson Almeida in a comment below, doing so is not portable between the SQL standard.

There are those who prefer to even avoid it, using subquery and other mechanisms, applying the WHERE in this result.

It's really important to ask as this is one of the cases where working might not be right, although I can't imagine what problem it might cause in this case. Using WHERE when you want to filter the aggregate doesn't work.

Scroll to Top