Skip site navigation (1) Skip section navigation (2)

Re: difference between 'where' and 'having'

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Adam Šindelář <adam(dot)sindelar(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: difference between 'where' and 'having'
Date: 2008-04-28 19:56:57
Message-ID: 15889.1209412617@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
"=?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

In response to

pgsql-novice by date

Next:From: Emil ObermayrDate: 2008-04-28 20:41:08
Subject: Re: difference between 'where' and 'having'
Previous:From: Richard BroersmaDate: 2008-04-28 19:45:12
Subject: Re: difference between 'where' and 'having'

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group