Обсуждение: 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
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
Have a nice day, people!
Adam