GeeCON Prague 2014*
The moose likes JDBC and the fly likes Difference between HAVING and WHERE clause Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "Difference between HAVING and WHERE clause" Watch "Difference between HAVING and WHERE clause" New topic
Author

Difference between HAVING and WHERE clause

Ali Ekber
Ranch Hand

Joined: Jun 12, 2005
Posts: 41
Could someone explain this or point me some online resources? Thanks.
Michael Matola
whippersnapper
Ranch Hand

Joined: Mar 25, 2001
Posts: 1747
    
    2
I'll give this a try.

Short answer:

Use HAVING instead of WHERE when you want to establish a condition that involves a grouping (aggregating) function. (Grouping functions are things like count(), max(), sum(), etc.)

Longer answer:

Either WHERE or HAVING clauses allow you to restrict the data you're selecting by establishing conditions.

(Usually people say stuff like "WHERE allows you to qualify on the original data in the tables and HAVING allows you to qualify on groups," which is indeed how you typically use them, but that's not 100% accurate.)

Conditions in WHERE clauses cannot contain grouping functions.

The conditions you establish in a HAVING clause *must* either (1) contain a grouping function, or (2) if the HAVING clause specifies table fields, those table fields must be in a GROUP BY clause.
Worthy LaFollette
Greenhorn

Joined: Jun 22, 2005
Posts: 1
The difference is when the clause is applied. WHERE is applied much earlier in the creation of the result set and I believe can take advantage of indexes and other optimizations. HAVING is applied much later.

HAVING is usually much slower than using WHERE as the WHERE clause usually is more restrictive earlier on. However, HAVING is very useful when wanting to filter on aggregated or aliased columns as these are created after the application of the WHERE clause.
Ali Ekber
Ranch Hand

Joined: Jun 12, 2005
Posts: 41
Thanks for your responses!
Anshul Agrawal
Greenhorn

Joined: Jul 15, 2008
Posts: 19
HAVING specifies a search condition for a group or an aggregate function used in SELECT statement.
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.

A HAVING clause is like a WHERE clause, but applies only to groups as a whole, whereas the WHERE clause applies to individual rows. A query can contain both a WHERE clause and a HAVING clause. The WHERE clause is applied first to the individual rows in the tables . Only the rows that meet the conditions in the WHERE clause are grouped. The HAVING clause is then applied to the rows in the result set. Only the groups that meet the HAVING conditions appear in the query output. You can apply a HAVING clause only to columns that also appear in the GROUP BY clause or in an aggregate function


Regards
 
GeeCON Prague 2014
 
subject: Difference between HAVING and WHERE clause