Difference between having and where clause in SQL

Where and having perform similar functions, but for different purposes.

But have some differences.

Here I am using AdventureWorksDW2008R2 Database.

1.       Where clause applies to each single row. Not mandatory to use group by clause.

Select ProductKey,EnglishProductName   from [AdventureWorksDW2008R2].[dbo].[DimProduct]   where ProductKey > 320

Having is applied to summarized rows (Summarized with group by clause)

Select ProductKey,EnglishProductName   from [AdventureWorksDW2008R2].[dbo].[DimProduct]   group by  ProductKey,EnglishProductName   having ProductKey > 320

   

2.        Where clause can be used with Select, Update and Delete Statement Clause but having clause can be used only with Select statement.

Where clause

--Select   Select ProductKey,EnglishProductName   from [AdventureWorksDW2008R2].[dbo].[DimProduct]   where ProductKey = 320   --Update   update [AdventureWorksDW2008R2].[dbo].[DimProduct]   Set EnglishProductName='xxxxxxx'   where ProductKey = 320     --delecte   Delete from [AdventureWorksDW2008R2].[dbo].[DimProduct]   where ProductKey = 320

It will be working fine

Having clause

--Update   update [AdventureWorksDW2008R2].[dbo].[DimProduct]   Set EnglishProductName='xxxxxxx'   Having  ProductKey = 320     --delecte   Delete from [AdventureWorksDW2008R2].[dbo].[DimProduct]   Having  ProductKey = 320

Both are not working throwing Syntax error. And it will not work.

3.        We can't use aggregate functions in the where clause unless it is in a subquery contained in a HAVING clause whereas we can use aggregate function in having clause. We can use column name in having clause but the column must be contained in the group by clause.

SELECT P.productKey,P.EnglishProductName,SUM(FIS.SalesAmount) AS TotalSalesAmount   FROM [AdventureWorksDW2008R2].[dbo].[FactInternetSales] FIS   INNER JOIN [AdventureWorksDW2008R2].[dbo].[DimProduct] P   ON P.ProductKey=FIS.Productkey   where SUM(FIS.SalesAmount)>74354   Group by P.productKey,P.EnglishProductName

The above sql script will not work throwing error

  

SELECT P.productKey,P.EnglishProductName,SUM(FIS.SalesAmount) AS TotalSalesAmount   FROM [AdventureWorksDW2008R2].[dbo].[FactInternetSales] FIS   INNER JOIN [AdventureWorksDW2008R2].[dbo].[DimProduct] P   ON P.ProductKey=FIS.Productkey   where FIS.ProductKey<320   Group by P.productKey,P.EnglishProductName   having SUM(FIS.SalesAmount)>74354

It is working Fine