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 (https://blog.sqlauthority.com)
123 Comments. Leave new
I would like to say only one sentence
“Very very nice article”
Really Helpful article
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
Hi Sir
thanks a Lot for it.
its best for me.
parity sql server meaning
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
thanks for ur thoughts
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….
@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.
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.
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.
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?
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.
Superb man
s………this is easily understanding the people
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.
Nice explanation
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.
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
select * from [tableName] where percentage like ‘%[%]’
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