Re: difference between 'where' and 'having'

"=?UTF-8?Q?Adam_=C5=A0indel=C3=A1=C5=99?=" <adam(dot)sindelar(at)gmail(dot)com> writes:
> Hi, I have a question, that's probably really stupid, but could someone
> please explain to me what difference there is between a WHERE clause and a
> HAVING clause besides the syntax?

If you're using grouped aggregates, the WHERE clause filters rows before
they go into the aggregates, and the HAVING clause filters afterwards
(ie, it acts on the group rows). Consider

select x, sum(y) from tab
where z = 42
group by x
having sum(y) > 100

Only table rows having z = 42 will be included in the sums, and only
sums over 100 will be printed.

In this example, you could not put the sum() condition into WHERE
(because aggregates haven't been computed yet) and you could not put the
z=42 condition into HAVING, because in HAVING you're talking about
grouped rows that don't have any specific value of z.

If you wanted to restrict the value(s) of x that you were computing
results for, you could do that either in WHERE or HAVING, since the
GROUP BY condition means the results would be the same. Usually people
do it in WHERE, since there's little point in computing sums at all for
x values that you'd only throw away again.

regards, tom lane