What is the difference between where and having?

14

Doing some testing 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 difference between where and having ?

    
asked by anonymous 09.06.2017 / 21:06

2 answers

15

Both function as if they were if , that is, they filter database rows.

WHERE works directly on the line, HAVING works on line aggregator results, the most used is GROUP BY .

Researching I concluded that it is actually expected that its use without an aggregator functions as a WHERE , 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 comment below, doing so is not portable among the SQL standard.

Some people even prefer to avoid it, using subquery and other mechanisms, by applying WHERE to that result.

It is really important to ask why this is one of the cases that work may not be right, although I can not imagine what problem might cause in that case. Already using WHERE when you want to filter the aggregate does not work.

    
09.06.2017 / 21:14
5

The difference is that HAVING is used together with GROUP BY, for example:

SELECT a.id, COUNT(a.id) qtde FROM sua_tabela a
WHERE a.um_campo_da_sua_tabela = 'um_valor_qualquer'
GROUP BY a.categoria
HAVING qtde > 5

In this query above, the WHERE is doing its normal role, which is to filter the query. GROUP BY is grouping the results according to the category. And HAVING is filtering the qtde field.

    
09.06.2017 / 21:17