Question:
Doing some tests on a database in MySQL, 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, HAVING
works on results of row aggregators, the most used is with GROUP BY
.
Researching I concluded that it is really expected that its use without an aggregator works like a WHERE
, it is not a coincidence, despite being a liberality, there is nothing in the specification that says it should be so. According to Jefferson Almeida in the comment below, doing so is not portable across the SQL standard.
There are those who even prefer to avoid it, using subquery and other mechanisms, applying the WHERE
to this result.
It's really important to ask since this is one of the cases where it might not work, although I can't imagine what problem it might cause in this case. Already using WHERE
when you want to filter the aggregate does not work.