0

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.

Community
  • 1
  • 1
Pearl
  • 516
  • 3
  • 9
  • 13

9 Answers9

2

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

Adriano Carneiro
  • 55,739
  • 12
  • 86
  • 122
BonyT
  • 10,480
  • 5
  • 29
  • 51
1
WHERE filters rows before they are grouped in GROUP BY clause 
while HAVING filters the aggregate values after GROUP BY takes place
niktrs
  • 9,348
  • 1
  • 26
  • 28
1

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

In other words.

HAVING applies to groups.

WHERE applies to rows.

sealz
  • 5,262
  • 5
  • 38
  • 70
1

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
gbn
  • 408,740
  • 77
  • 567
  • 659
0

Having is for aggregate functions, e.g.

SELECT * 
FROM foo
GROUP BY baz
HAVING COUNT(*) > 8
marc_s
  • 704,970
  • 168
  • 1,303
  • 1,425
Tom Squires
  • 8,536
  • 10
  • 45
  • 71
  • Is it that the having clause is for checking condition with groupby clause.? – Pearl Jun 24 '11 at 11:51
  • If you post code, XML or data samples, **please** highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it! – marc_s Jun 24 '11 at 12:06
0

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

Magnus
  • 43,221
  • 7
  • 76
  • 112
0

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.

Gaurav Agrawal
  • 4,244
  • 9
  • 39
  • 59
0

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

Maxim Krizhanovsky
  • 25,260
  • 5
  • 51
  • 86
0

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.)

faester
  • 14,578
  • 5
  • 43
  • 55