A simple SQL question

[Cameron Park]

Hi, I have a very uneducated question regarding SQL. What is the difference between HAVING and WHERE?

[Peter den Haan]

Originally posted by Cameron Park:
Hi, I have a very uneducated question regarding SQL. What is the difference between HAVING and WHERE?

The WHERE clause is applied [/I]before[/I] grouping occurs, the HAVING clause after.
Consider
SELECT dept, count(*)
FROM emp
WHERE salary > 100000
GROUP BY dept
This will take the employees whose salary exceeds 100000, and count the number of such employees grouped on a per department basis. Note that the salary condition is applied before any grouping occurs. Contrast this with
SELECT dept, count(*)
FROM emp
GROUP BY dept
HAVING AVG(salary) > 100000
This will first count employees, grouped per department, and from this list show you only those departments where the average salary exceeds 100000. The condition is applied to the groups themselves, not to the records used in constructing the groups.
- Peter

[Cameron Park]

Thank you Peter.