What is Difference between where and having clauses in SQL server

In this post, We will discuss the difference between where and having clauses in SQL server with the examples.
we will be using the sales table. I’ve already created this table.

Here is the SQL script to create and populate with test data.

TblProducts Table has the product information and TblProductSale table contains sales-related data. So when any customer purchases any product, an entry will be made into the sale table.

If you look at the data that we have here, notice we have sold Books twice, Mobile Phone twice, Watches & Computer Accessories. Once.

Table script

/****** Object: Table [dbo].[TblProducts] Script Date: 12/07/2020 8:00:33 PM ******/
CREATE TABLE [dbo].[TblProducts](
[Id] [int] NOT NULL,
[ProductName] [nvarchar](100) NOT NULL,
[Quantity] [int] NOT NULL,
[Price] [float] NULL,
CONSTRAINT [PK_TblProducts] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[TblProductSale] Script Date: 12/07/2020 8:00:34 PM ******/
CREATE TABLE [dbo].[TblProductSale](
[Id] [int] NOT NULL,
[ProductId] [int] NULL,
[Datetime] [datetime] NULL,
CONSTRAINT [PK_TblProductSale] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[TblProducts] ([Id], [ProductName], [Quantity], [Price]) VALUES (1, N'Books', 10, 100)
GO
INSERT [dbo].[TblProducts] ([Id], [ProductName], [Quantity], [Price]) VALUES (2, N'Mobile Phone', 100, 15000)
GO
INSERT [dbo].[TblProducts] ([Id], [ProductName], [Quantity], [Price]) VALUES (3, N'Watches', 50, 1000)
GO
INSERT [dbo].[TblProducts] ([Id], [ProductName], [Quantity], [Price]) VALUES (4, N'Cameras ', 30, 10000)
GO
INSERT [dbo].[TblProducts] ([Id], [ProductName], [Quantity], [Price]) VALUES (5, N'Computer Accessories.', 40, 2000)
GO
INSERT [dbo].[TblProductSale] ([Id], [ProductId], [Datetime]) VALUES (1, 1, CAST(N'2020-10-16T17:16:57.953' AS DateTime))
GO
INSERT [dbo].[TblProductSale] ([Id], [ProductId], [Datetime]) VALUES (2, 2, CAST(N'2020-10-16T17:16:57.953' AS DateTime))
GO
INSERT [dbo].[TblProductSale] ([Id], [ProductId], [Datetime]) VALUES (3, 1, CAST(N'2020-10-16T17:32:44.040' AS DateTime))
GO
INSERT [dbo].[TblProductSale] ([Id], [ProductId], [Datetime]) VALUES (4, 3, CAST(N'2020-10-16T17:32:44.040' AS DateTime))
GO
INSERT [dbo].[TblProductSale] ([Id], [ProductId], [Datetime]) VALUES (5, 5, CAST(N'2020-10-16T17:32:44.040' AS DateTime))
GO
INSERT [dbo].[TblProductSale] ([Id], [ProductId], [Datetime]) VALUES (6, 2, CAST(N'2020-10-16T17:32:44.040' AS DateTime))
GO

Now we want to write a query that’s going to calculate the total sales by product. So the result of that query should be like the below image.

SELECT ProductName,SUM(Price) AS TotalSales
FROM TblProductSale
Inner JOIN TblProducts
ON TblProductSale.ProductId = TblProducts.Id
GROUP BY ProductName

We want to calculate total sales by product. So we are using GROUP BY on the product column since we won’t the total sale amount by using the SUM() aggregate function on the sale amount column.

Now we want to find only those products where the total sales is greater than 200.

We can very easily achieve this using having Clause. Let’s look at that in action. So I am going to use the having clause and we want to filter based on this total sales column.

SELECT ProductName,SUM(Price) AS TotalSales
FROM TblProductSale
Inner JOIN TblProducts
ON TblProductSale.ProductId = TblProducts.Id
GROUP BY ProductName
HAVING SUM(Price) > 200

So when we execute this, we should get only those products where the total sales is greater than in 2000.

So here we are using the having clause with Sum() aggregate function to filter the groups.

Now, what do you think?what is going to happen if we use the where clause instead of having? When we execute it, we get a syntax error straightaway.

1. So this is one of the primary differences between where and having clauses, where clauses cannot be used with the aggregates, whereas having can be used with the aggregates.
2. There are other differences as well -where clause filters rows before aggregate calculations are performed, whereas having class filters rows after the aggregate calculations are performed.

Let’s understand what we mean by this. Now we want to calculate the total sales of Books and Mobile Phone so we know the products already.

We want to compute the total sales for those two products, there are two ways we can achieve this. We can achieve this either by using where or having clause.

Let’s look at that in action. So I’m going to use the where clause here and we want to compute the total sales for Books and Mobile Phone

SELECT ProductName,SUM(Price) AS TotalSales
FROM TblProductSale
Inner JOIN TblProducts
ON TblProductSale.ProductId = TblProducts.Id
WHERE ProductName in ('Books', 'Mobile Phone')
GROUP BY ProductName

So when we execute the above query, we get the total sales for Books and Mobile Phone.

Now we can achieve the same thing using having as well.

SELECT ProductName,SUM(Price) AS TotalSales
FROM TblProductSale
Inner JOIN TblProducts
ON TblProductSale.ProductId = TblProducts.Id
GROUP BY ProductName
HAVING ProductName in ('Books', 'Mobile Phone')

We get the same result. Now, what is the difference between these two queries?

The first query, is going to filter all the rows , It is going to retrieve only Books and Mobile Phone, and then it’s going to perform the aggregate calculation i.e Sum.

whereas with having, it’s going to retrieve all the products, it’s going to perform the aggregate calculation on all the products, and then it’s going to filter the groups.

So clearly we can see that we have a performance impact when we use having clause. So according to a performance standpoint, having is going to be slower than where and should be avoided when possible.

Because having is just going to filter the groups, whereas where filters, all the rows, it only gets Books and Mobile Phone and then performs the aggregation on those two products, whereas in having case it performs the aggregation for all the products and then filters Books and Mobile Phone groups.

So having is definitely slower than where in this case and should be avoided.

The post What is Difference between where and having clauses in SQL server appeared first on Software Development | Programming Tutorials.