Thread: difference between 'where' and 'having'

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? I read the documentation where it states that:

Expressions in the HAVING clause can refer both to grouped expressions and to ungrouped expressions (which necessarily involve an aggregate function).

I've been happily working with Postgres for a long time, and not once have I ever used a HAVING in my queries, even in fairly complex ones, and I just can't bear the suspense anymore! :)

Thanks in advance for clearing this up for me.

Adam

On Mon, Apr 28, 2008 at 12:22 PM, Adam Šindelář <adam.sindelar@gmail.com> wrote:

> Expressions in the HAVING clause can refer both to grouped expressions and
> to ungrouped expressions (which necessarily involve an aggregate function).

True, however, when constraing data in a having clause, alot more data
will be processed before it is grouped.  This mean that putting all of
the costraints in the having clause will result in poorly performing
queries.

> I've been happily working with Postgres for a long time, and not once have I
> ever used a HAVING in my queries, even in fairly complex ones, and I just
> can't bear the suspense anymore! :)

I only use the HAVING clause in cases where the where clause cannot
easy constrain the data without a sub-query.

SELECT AVG( X ), y
  FROM Thetable
 WHERE y > 10
GROUP BY Y
 HAVING AVG( X ) > 3 AND COUNT( * ) > 10 AND MAX( X ) < 10;


--
Regards,
Richard Broersma Jr.

"=?UTF-8?Q?Adam_=C5=A0indel=C3=A1=C5=99?=" <adam.sindelar@gmail.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

Am Montag, 28. April 2008 schrieb Adam Šindelář:
> ... what difference there is between a WHERE clause and a
> HAVING clause besides the syntax?

That's a classic :)

"where" is a selection directly on the data in the database

"having" is a selection on the result

in simple selects, the database-data and the result-data basically is the same

but there are quite simple things you can't do with a "where" like selecting
on the number of records aggregated by a "group by":

select city, count(*) from customers group by city having count(*) > 10;

to see in which cities you have more than 10 customers.

Some databases allow all selections allowed in where also in having. Mostly
this is stupid because it costs performance. having-selections are not
optimized during the database-scan. You should use them only on aggregated
results.

Hope this helps a bit

Ok, thanks, that clears that up.
Have a nice day, people!

Adam