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.
- 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.
- 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.
Read More Articles
- Linq to SQL Group by and Sum in Select | Linq To SQL Group By and Sum
- How send an HTTP POST request to a server from Excel using VBA?
- What is Difference between where and having clauses in SQL server
- How to Use EXCEPT Operator with an example in Sql
- How to use merge statement in the SQL server
- How to write Re-runnable SQL server query
- How to create Cursor in sql server with example
- How to generate random data in sql server for performance testing
- How to write subquery in select statement in Sql
- How to Log record changes in SQL server in an audit table
- Logon Trigger Example to Restrict Access in Sql Server
- DDL Triggers In Sql Server with Example | Database and Server Scoped Triggers
- How to achieve ACID properties with Example in Sql Server
- What is transaction in sql server with example
- Instead Of Delete Trigger In Sql Server With Example
- Instead Of Update Trigger In Sql Server With Example
- Instead Of Triggers in Sql server With Example
- After Update Trigger in Sql Server With Example
- Triggers in SQL server with real-time example
- C# -Saving a base64 string as an image into a folder on server in Web Api
- what are the advantages and disadvantages of indexes-Sql Server?
- Sql Server- Difference between unique and non unique index
- Temporary Tables in sql server with real time example
- Clustered and Non clustered index in sql server with real example
- How does Recursive CTE works in Sql server?
- How to update table using CTE in sql server
- How to use group by in SQL Server query?
- Sql Server pivot rows to columns
- What is CTE in sql server with example
- SQL Server – -Simple way to transpose rows into columns
- What is Sql Joins? With Realtime Examples
- Difference between Inner Join ,Left Join and Full Join-Sql
- How to get Employee manager hierarchy in Sql ?
- How to create Inline table-valued functions in SQL server
- Scalar User-defined functions In sql server-Step by Step
- [Solved]-How To Update a Table using JOIN in SQL Server?
- Create Stored procedure with Output and Input parameters in SQL
- [Solved]-Best Sql server query with pagination and count
- [Solved]- find records from one table which don’t exist in another Sql
- [Solved]-How to concatenate text from multiple rows into a single text string in SQL server?