difference between usage of having clause and where clause [duplicate]

This question already has answers here: 开发者_开发知识库 Closed 11 years ago.

Possible Duplicate:

SQL: What's the difference between HAVING and WHERE?

What is the difference between using having clause and where clause. Could any one explain in detail.

---

HAVING filters grouped elements, WHERE filters ungrouped elements.

Example 1:

 SELECT col1, col2 FROM table
 WHERE col1 = @id

Example 2:

 SELECT SUM(col1), col2 FROM table
 GROUP BY col2
 HAVING SUM(col1) > 10

Because the HAVING condition can only be applied in the second example AFTER the grouping has occurred, you could not rewrite it as a WHERE clause.

Example 3:

 SELECT SUM(col1), col2 FROM table
 WHERE col1 = @id
 GROUP BY col2
 HAVING SUM(col1) > 10

demonstrates how you might use both WHERE and HAVING together:

The table data is first filtered by col1 = @id then the filtered data is grouped then the grouped data is filtered again by SUM(col1) > 10

---

WHERE filters rows before they are grouped in GROUP BY clause 
while HAVING filters the aggregate values after GROUP BY takes place

---

HAVING specifies a search for something used in the SELECT statement.

In other words.

HAVING applies to groups.

WHERE applies to rows.

---

Without a GROUP BY, there is no difference (but HAVING looks strange then)

With a GROUP BY

• HAVING is for testing condition on the aggregate (MAX, SUM, COUNT etc)
• HAVING column = 1 is the same as WHERE column = 1 (no aggregate on column )
• WHERE COUNT(*) = 1 is not allowed.
• HAVING COUNT(*) = 1 is allowed

---

Having is for use with an aggregate such as Sum. Where is for all other cases.

---

They specify a search condition for a group or an aggregate. But the difference is that HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. Having Clause is basically used only with the GROUP BY function in a query whereas WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.

---

As other already said, having is used with group by. The reason is the order of execution - where is executed before group by, having is executed after it

---

Think of it as a matter of where the filtering happens.

When you specify a where clause you filter input rows to your aggregate function (ie: I only want to get the average age on persons living in a specific city.) When you specify a having constraint you specify that you only want a certain subset of the averages. (I only want to see cities with an average age of 70 years or above.)

---

Having is for aggregate functions, e.g.

SELECT * 
FROM foo
GROUP BY baz
HAVING COUNT(*) > 8