What’s the difference between Where and having?

Question

Doing some tests on a database that 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 1

Both work as one if, that is, filter lines from the database.

The WHERE works directly on the line, already the HAVING works in row aggregator results, the most used is with the GROUP BY.

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

There are those who prefer to avoid it, using subquery and other mechanisms, applying the WHERE in that outcome.

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

Answer 2

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, WHERE is playing its normal role, which is to filter the query. GROUP BY is grouping the results according to the category. And HAVING is filtering the field Qtde.

Answer 3

The fundamental difference between the two clauses is that Where perform filtering in the source tables as the first step of the query (query), while Having perform the filtering in the resulting table as the last step of the query. From this fundamental difference, the remaining differences in specification and functionality between the two clauses derive, in particular:

1. References to variables;
2. Use at table junction (Join);
3. Filtering based on aggregators;

1. References to variables:

In the clause Where the names of the variables used as filter conditions must be equal to the names of the source table. Already in the clause Having the names of the variables must be equal to the names of the resulting table. In the cited example, suppose you change the name of the variable of uuid for usuario. In the clause Where you will have to use the old name; in the clause Having you will use the new name:

SELECT uuid as usuario, password as senha  From client WHERE uuid = '1kvsg4oracxq';

SELECT uuid as usuario, password as senha From client HAVING usuario = '1kvsg4oracxq';

2. Joining of tables:

The clause Where is quite useful at the junction of tables. By filtering directly into the source table, it makes the junction of large databases more agile:

SELECT t1.*, t2.* From client as t1
LEFT JOIN company as t2 on t1.comp_id = t2.comp_id
WHERE t1.uuid = '1kvsg4oracxq';

Note that if the variable uuid is present in more than one source table, you need to reference the table in which the filtering will be performed, in the example above is the t1 (or client).

You could still get the same result with the clause Having, but at a much higher computational cost. In this second option, the software will join all cases of the two tables first and then perform the filtering. If your tables are large, it will take much longer, in addition to being subject to write space limitations of temporary files.

SELECT t1.*, t2.* From client 
LEFT JOIN company on t1.comp_id = t2.comp_id
HAVING uuid = '1kvsg4oracxq';

When we use the clause Having, does not make sense to indicate the source table of the variable, because the filtering is performed in the final table (resulting).

3. Aggregator-based filtration:

Finally, the main, and already cited, functionality of the clause Having is the filtering based on aggregators and new variables produced in the query. Since clause Where performs the filtering in the original tables, it has no ability to reference the aggregators created in the query itself. Already the clause Having, by filtering in the last step of the query, has the ability to interact with elements created during the query.

The clause Having together with Group by is very useful for eliminating duplicates conditionally. For example, if each client in your table has many records and each record has a different date (for example, in the date variable DT_LOG), you can get a new table only with the latest record (or the maximum value of DT_LOG) for each customer:

CREATE TABLE LOG_MAIS_RECENTE as
SELECT DISTINCT * From client 
GROUP BY uuid
HAVING DT_LOG = MAX(DT_LOG);