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:
- References to variables;
- Use at table junction (Join);
- 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);
Interesting link HAVING specifies a search condition for a group or an aggregate function used in an SQL query
– Artur Trapp