Difference Between Where and Having Clause in SQL

WHERE clause is used to filter out a set of records returned from a table. But HAVING clause is an external filters that works on top of a GROUP BY clause.

HAVING is used to check conditions after the aggregation takes place. It is used to filter values from a group.

Example

SELECT   SalesOrderID,
         SUM(UnitPrice * OrderQty) AS TotalPrice
FROM     Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING   SalesOrderID > 50000

WHERE is used before the aggregation takes place. It is used is filter records from a result.

Example

SELECT   COUNT(SalesOrderID)
FROM     Sales.SalesOrderDetail
WHERE    UnitPrice > 200

The where clause works on row’s data, not on aggregated data. Let us consider below table 'Marks'.

Marks Table

Student       Course       Score

a              c1            40

a              c2            50

b              c3            60

d              c1            70

e              c2            80

Consider the query

Syntax

SELECT Student, Score FROM Marks WHERE Score >=40

This would select a data row by row basis. The having clause works on aggregated data. For example, the output of below query

Syntax

SELECT Student, SUM(score) AS total FROM Marks GROUP BY Student

Marks Table

Student     Total

a             90

b             60

d             70

e             80

When we apply having in the above query, we get

Syntax

SELECT Student, SUM(score) AS total FROM Marks GROUP BY Student
HAVING total > 70

Marks Table

Student     Total

a              90

e               80

Difference Between Where and Having Clause in SQL

Where Clause	Having Clause
WHERE clause can be used with Select, Insert and Update statements	HAVING clause can only be used with the Select statement
Aggregate functions cannot be used in the WHERE clause, unless it is in a sub query contained in a HAVING clause	Aggregate functions can be used in Having clause
WHERE filters rows before aggregation (GROUPING)	HAVING filters groups, after the aggregations are performed
Where Clause select rows before grouping	Having Clause select rows after grouing
SELECT Column, AVG(Column_name)FROM Table_name WHERE Column > value GROUP BY Column_name	SELECT Column, AVG(Column_name)FROM Table_name WHERE Column > value GROUP BY Column_name HAVING column_name>or