What is the difference between Where and Having Clause?

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