As per SQL standard –
|The HAVING clause without a GROUP BY clause acts like the WHERE clause. If the HAVING clause contains no aggregate functions, use the WHERE clause for faster performance.|
here’s an example of a SQL statement where we use
select column1 from table1 where condition1 having condition2;
isn’t it the same exact thing if we do this:
select column1 from table1 where condition1 AND condition2;
what is the difference between these two?
In your example, they should do the same thing. But
WHERE gets processed before any
GROUP BY, and so it doesn’t have access to aggregated values (that is, the results of
Max(), etc. functions).
HAVING gets processed after
GROUP BY and so can be used to constrain the result set to only those with aggregated values that match a certain predicate.
The where clause applies to all rows in the result set. The having clause is applied to the groups created by a group by clause. So if your group consists of column1 in your example and the conditions were on column 1, then the where is the same as the having, since a row is the same as the “group”
HAVING is for use with aggregates, e.g.,
HAVING SUM(column1) > 200,
WHERE is just for the columns, e.g.,
WHERE column1 < 20.
HAVING is applied after GROUP BY which is applied after WHERE.
in SQL the
WHERE clause is evaluated before the
SELECT clause, therefore the result of a set function is ‘out of scope’ in the
WHERE clause, that’s why we use Having.
HAVING specifies a search condition for a group or an aggregate function used in a SELECT statement.
A HAVING clause is like a WHERE clause, but applies only to groups as a whole, whereas the WHERE clause applies to individual rows.