SQL SERVER – Definition, Comparison and Difference between HAVING and WHERE Clause

In recent interview sessions in hiring process I asked this question to every prospect who said they know basic SQL. Surprisingly, none answered me correct. They knew lots of things in details but not this simple one. One prospect said he does not know cause it is not on this Blog. Well, here we are with same topic online.

Answer in one line is : 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. (Reference :BOL)

Example of HAVING and WHERE in one query:

SELECT titles.pub_id, AVG(titles.price)
FROM titles INNER JOIN publishers
ON titles.pub_id = publishers.pub_id
WHERE publishers.state = 'CA'
GROUP BY titles.pub_id
HAVING AVG(titles.price) > 10

Sometimes you can specify the same set of rows using either a WHERE clause or a HAVING clause. In such cases, one method is not more or less efficient than the other. The optimizer always automatically analyzes each statement you enter and selects an efficient means of executing it. It is best to use the syntax that most clearly describes the desired result. In general, that means eliminating undesired rows in earlier clauses.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

123 thoughts on “SQL SERVER – Definition, Comparison and Difference between HAVING and WHERE Clause

  1. That was a very helpful explanation. This was asked to me as well. Though I answered correctly, I could not explain why I said so !!!

  2. Thanks.

    i knew the difference between these two.
    can u tell me the details and difference between CUBE & ROLLUP

    with regards
    Ganesan.M

  3. Thanks for the explaination!

    I really enjoy reading your articles/posts because they’re so well articulated with such simplicity that even *I* can understand it!! Thank you!

  4. Your post was really helpful. I appreciate the way it was written and presented. It becomes very easy for a layman to understand it. Thanks once again.

  5. will there be any difference is performance if we use HAVING in place of WHERE in a query which doesnot has any GROUPING clauses

  6. Normally when we are looking for something on internet the
    percentage of usefull info is verry less..wht we get is normally crap.But this is one of the best explanations i have came
    across…thanks a lot and keep it up.

  7. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  8. Pingback: SQL SERVER - 2008 - Interview Questions and Answers - Part 3 Journey to SQL Authority with Pinal Dave

  9. GROUP BY returns single occurence of a row. However I need to limit it to a specified number of occurences.

    Suppose the tabe has the following:

    number letter

    100 A
    100 A
    100 A
    200 B
    200 B
    200 B
    200 B

    ….and I need this to limit to say 2 occurences…the output should be…

    100 A
    100 A
    200 B
    200 B

    ….so on for each of the multiple rows in the table..

    Can you please direct me to the right query?

    • Dear AS,

      This can be achived by various methods, however I’ve shown you only two.

      For Example:
      —————

      You’ve a table with values like below….

      Create Table #Abc (SNo Int, Letter varchar(5))
      Insert Into #Abc
      Select 100, ‘A’
      Union All
      Select 100 ,’A’
      Union All
      Select 100 ,’A’
      Union All
      Select 200, ‘B’
      Union All
      Select 200, ‘B’
      Union All
      Select 200, ‘B’
      Union All
      Select 200, ‘B’

      and it’s output looks like this…

      SNo Letter
      ———– ——
      100 A
      100 A
      100 A
      200 B
      200 B
      200 B
      200 B

      (7 row(s) affected)

      –One Way With Common Table Expression
      ;With CTE
      As
      (
      Select Row_Number() Over (Partition By Letter Order By Letter) As RowId, SNo, Letter
      From #Abc )

      Select SNo, Letter
      From CTE
      Where RowId <= 2

      – Or
      – Another with Cross Apply

      Select a.*
      From (Select Distinct SNo,Letter From #Abc) B
      Cross Apply
      ( Select top 2 *
      From #Abc x
      Where B.SNo = X.SNo
      And B.Letter = X.Letter ) a

      –Drop Table #Abc

      I hope you'll find this helpful.
      Thanks,

      Atif Siddyqi

  10. I understand that we can use a HAVING clause without a GROUP BY clause. I tried to write a few queries using AdventureWorks database, but do not understand the purpose of using a Haveing clause without the GROUP BY clause. Will appreciate if you can explain that.

  11. I’m wondering if there is any reason for both the WHERE and HAVING clause. Clearly WHERE filters records on fetch and/or before aggregation, and HAVING operates after aggregation. However, couldn’t the SQL compiler determine the most effecient place always. A clause based on an aggregated function (couny, sum, max, etc.), would be forced into the aggregate filter, and something without aggregated functions would be on the initial fetch. Mixing in a non-logical operator (>,100000

    In this case, the clause *has* to be a having clause. That is,
    select CUST,sum(REVENUE)
    from order where TYPE = ‘WHOLESALE’
    group by CUST
    having sum(REVENUE)>100000

    Incidently, thinking of this example makes me understand why non-aggregated clauses are allowed in HAVING clauses. So now, I’m thinking, using WHERE is just telling the compiler how to do something that it should be smart enough to figure out anyway.

    I vote for deprecating the where clause to a hint. Any takers?

  12. Hi Pinal,

    Very thorough and plain self explanatory description on the HAVING vs WHERE clauses you have provided. You have been very helpful with my SQL Programming & Developing course. Thanks a million bunches!

    Dennis.

  13. hi all,
    need help in having clause. I am having a table where there are duplicate record and some are unique record. The record filter are based on three columns and I am able to fetch the filtered records using groupby and having clause.
    But problem is I want to see all the columns while my having clause can only show me those columns which I am using in filter.
    Can someone suggest how can I all the columns which are filtered using having clause?.
    My query is :-
    select count(*) as cnt, column1,column2,column3 from tablename group by (column1,column2,column3 ) having count(*) > 1 and I have 12 other columns which I am not able to see by this.

  14. i was always under impression that you should use WHERE whenever possible, leaving HAVING to those times when where can not get the job done.

    From the point of query performance, is WHERE not faster then HAVING (and vise versus)…

    can anyone clear this up for me?

  15. “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.”

    I found the above statement written by you WRONG. i tried the
    following statement and got an error:

    select * from customers having customerid=100

    Conclusion: We can not use only HAVING in SELECT statement.

  16. i thought that where execute on retrieved records while having filter before fetching so i think this is a big difference in execution of a query. please guide and correct me

  17. where is i m lacking ?

    –Fetching unmatched record

    select ident
    from
    (
    select ident, full_name from position_code_abstract UNION All select position_code_abstract_ident, comments
    from position_code_abstract_comment)
    Group by ident
    having count(*)=1
    order by ident

  18. select Count(*) FROM Nop_Product INNER JOIN Nop_ProductVariant
    ON Nop_Product.ProductID = Nop_ProductVariant.ProductID
    where(Nop_Product.Color like ‘%’ + @Keywords + ‘%’ or
    Nop_Product.Style like ‘%’ + @Keywords + ‘%’ or
    Nop_Product.Brand like ‘%’ + @Keywords + ‘%’ or
    Nop_Product.Name like ‘%’ + @Keywords + ‘%’)
    group by Nop_Product.Name
    having MAX(Nop_ProductVariant.StockQuantity) > 3.

    I need to return the count from this query, but it is returning multiple rows and not returning actual count. Can anyone please help me as I need it urgently….

  19. @Rahul,

    You are getting Multiple Counts because you are grouping using group by Nop_Product.Name.

    That means, for every Nop_Product.Name you will see a Count. If you dont want that… you can consider your this whole query as inner query.

    Here is an example,

    select COUNT(*)
    From (
    select Count(*) Counts
    FROM Nop_Product
    INNER JOIN Nop_ProductVariant ON Nop_Product.ProductID = Nop_ProductVariant.ProductID
    where ( Nop_Product.Color like ‘%’ + @Keywords + ‘%’
    or Nop_Product.Style like ‘%’ + @Keywords + ‘%’
    or Nop_Product.Brand like ‘%’ + @Keywords + ‘%’
    or Nop_Product.Name like ‘%’ + @Keywords + ‘%’
    )
    group by Nop_Product.Name
    having MAX( Nop_ProductVariant.StockQuantity) > 3.
    )A

    ~ IM.

  20. I knew the difference when i had around 1 year of experience. Probably, you are not interviewing right prospects. :)
    Anyways, you are doing great job by maintaining this blog.

  21. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 7 of 31 Journey to SQLAuthority

  22. Nice article…i have one doubt can we use HAVING with out GROUP BY clauss.. if possible can you explain in detail, when we need to use where and having.

  23. Let’s see if I understand this correctly: nothing in the Group By clause can change, before or after aggregation. Therefore, any condition on these fields could be done either in a Where clause or in a Having clause.

    Therefore, the only reason to use a Having clause is if I want to put in a condition based on aggregated values. But if I must have a Having clause, I may as well put all of my conditions into it, to save the bother of including both a Where and a Having clause.

    Is this correct, or have I missed something?

  24. Nice article. Please put more light on performance issue in order to use Having and where clause. Also post some business scenarios in order to use Having and where.

  25. Is HAVING redundant?
    In other words,
    Any query with HAVING clause has its HAVING-less semantic equivalent.
    True or false?

    Same question with HAVING replaced by WHERE.

  26. That’s an interesting question, Igor. There is plenty of redundancy in SQL syntax.

    As regards HAVING, I suppose TOP could be used to replace some HAVING expressions, but only some.

    As for WHERE, it depends on the WHERE clause. Some WHERE expressions could be more elegantly (and in some RDBMSs more efficiently) achieved using a JOIN. Most, though, cannot.

  27. Hi,
    I have doubt in sqlserver.
    I have one table in my db.
    sname percentage
    rama 86%
    krishna 67%
    varma 78
    raghava 90%
    venkat 87
    radha 97
    maanasa 89%

    How can i get get data who is having percentage in my table?
    Very important and I need immediate reply friends
    Thanks

  28. I have table like this

    sname gender
    ramakrishna F
    Sunitha M
    Ramudu F
    Mahalakshmi F
    Revathi M
    Renuka M
    Raghu F
    Rayudu F
    Krishnan F
    The client entered data male has female and female has male and some rows client entered correctly . How can i Update Male has gender male and female has gender female.

    I want result like this
    sname gender
    ramakrishna M
    Sunitha F
    Ramudu M
    Mahalakshmi F
    Revathi F
    Renuka F
    Raghu M
    Rayudu M
    Krishnan M

  29. Thank you so much! This is my first visit to this blog. I’m in the middle of teaching myself SQL through using access. I’m sure this blog will be a valuable resource

  30. 1. Having works as filter on select result
    2. We can not directly use function in where clause but can be use in Having

  31. Good example.
    However, what about the performance impact ?
    Which way of elimination works faster ?
    Are there any pros/cons of one over another ?

  32. Nice one Dear,
    Its not tough question but no buddy think on it and just use “having” clause.
    Thanks for Explanation…

  33. If I used having as where (i.e. without group by clause), I want to know which one is faster ‘Where’ OR ‘Having’? Which one we have to prefer?
    Scenario I want to use this in finding locations in certain distance using lat and longitude.

  34. Pingback: SQL SERVER – Weekly Series – Memory Lane – #036 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s