Tuesday, August 16, 2011

What is the difference between Where and Having Clause?

Posted by Rahul Kharde at 5:22 AM

We always get confused between WHERE and Having clause and make mistakes. Here in this article, I will try to highlight all the major differences between WHERE and HAVING, and things you should be aware of, when using either WHERE or HAVING.

Here is The Difference

Though the HAVING clause specifies a condition that is Similar to the purpose of a WHERE clause, the two clauses Are not interchangeable. Listed below are some differences to help distinguish between the two:
1. The WHERE clause specifies the criteria which individual records must meet to be selected by a query. It can be used without the GROUP BY clause. The HAVING clause cannot be used without the GROUP BY clause.
2. The WHERE clause selects rows before grouping. The HAVING clause selects rows after grouping.
3. The WHERE clause cannot contain aggregate functions. The HAVING clause can contain aggregate functions.
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.)

EXAMPLE

CREATE TABLE Student(sid INT identity PRIMARY KEY , City Varchar(255) NOT NULL)
INSERT INTO Student(City)VALUES('AL')
INSERT INTO Student(City)VALUES('AK')
INSERT INTO Student(City)VALUES('GA')
INSERT INTO Student(City)VALUES('CA')
INSERT INTO Student(City)VALUES('CA')
INSERT INTO Student(City)VALUES('GA')
INSERT INTO Student(City)VALUES('AK')
INSERT INTO Student(City)VALUES('LA')
INSERT INTO Student(City)VALUES('CA')

SELECT City, COUNT(*)as cnt
FROM student WHERE City IN ('CA', 'LA','AK')
GROUP BY City ORDER BY City

SELECT City, COUNT(*)as cnt
FROM student
GROUP BY City HAVING City IN ('CA', 'LA')
ORDER BY City

OUTPUT

City Cnt
------------------
AK 2
CA 3
LA 1

City Cnt
-------------------
CA 3
LA 1


If you enjoyed this post and wish to be informed whenever a new post is published, then make sure you subscribe to my regular Email Updates. Subscribe Now!


Kindly Bookmark and Share it:

YOUR ADSENSE CODE GOES HERE

0 comments:

Have any question? Feel Free To Post Below:

 

Popular Posts

Recent Comments

© 2011. All Rights Reserved | Help to understand .Net | Template by Blogger Widgets

Home | About | Top