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
  • 526
  • 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
  • 57,693
  • 12
  • 90
  • 123
BonyT
  • 10,750
  • 5
  • 31
  • 52
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,858
  • 1
  • 30
  • 30
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,348
  • 5
  • 40
  • 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
  • 422,506
  • 82
  • 585
  • 676
0

Having is for aggregate functions, e.g.

SELECT * 
FROM foo
GROUP BY baz
HAVING COUNT(*) > 8
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Tom Squires
  • 8,848
  • 12
  • 46
  • 72
  • 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
  • 45,362
  • 8
  • 80
  • 118
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,355
  • 10
  • 42
  • 61
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
  • 26,265
  • 5
  • 59
  • 89
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,886
  • 5
  • 45
  • 56