Re: difference between 'where' and 'having'

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