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

Re: difference between 'where' and 'having'

From: Emil Obermayr <nobs(at)nobswolf(dot)info>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: difference between 'where' and 'having'
Date: 2008-04-28 20:41:08
Message-ID: 200804282241.09116.nobs@nobswolf.info (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

pgsql-novice by date

Next:From: Adam ŠindelářDate: 2008-04-28 21:10:49
Subject: Re: difference between 'where' and 'having'
Previous:From: Tom LaneDate: 2008-04-28 19:56:57
Subject: Re: difference between 'where' and 'having'

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