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
- How to use integer value with SQL Server query utilizing HashBytes
- Script all data from SQL Server database
- how can I update a list of records in a table with the new value depending on the old value
- Refactor overheaded T-SQL query
- A very strange case of DbUpdateConcurrencyException
- displaying unique row and ranking in SQL?
- Create a Trigger to fire on all Tables
- SQL to identify missing week
- How to Convert Sub Query to Joins for Fast Result?
- C# - SQL Server - ? parameters and Oledb
- How to set collation for a connection in SQL Server?
- Obtain records with multiple instances of field values
- Connect Python with SQL Server Database
- Hibernate with sql server retrieving data Encountered unexpected unknown token
- Get Week from a date range SQL Server
- How to create same Temporary table in ELSE IF Statement in SQL Server?
- Select where in list or else select all most elegantly
- How to reduce execution time of a select query
- Why hard-coded dates in WHERE clause gives faster performance then same dates as a parameters?
- SET QUOTED_IDENTIFIER Error in job and sp_MSForEachTable
- Can I avoid using cursors here?
- How to get rank from full-text search query with Linq to SQL?
- Loop through XML using SQL
- Why SQL Server generates two statements for only one foreign key?
- How do I create system versioned tables using SSDT in Visual Studio 2019?
- Cant find my EF code first database
- SQL select row where (this column has many different values in the other)
- The parameterized query '(@lastName nvarchar(4000),@firstName nvarchar(4000),@middleName ' expects the parameter '@lastName', which was not supplied
- Table Per Concrete Type (TPC) Inheritance in Entity Framework 6 (EF6)
- Finding related XML elements filtering on attribute from current element in XPath
- Turn off IDENTITY_INSERT for Dataset insert
- Pass back original SQL operation row count, after trigger?
- SQL Server 2008: Collation for UTF-8 code page 65001
- Dynamic Search multiple terms in linqtosql
- Using UNPIVOT in constructed String Query Dynamic
- SQL Server Invalid Column name after adding new column
- Parsing SOAP XML in SQL Server
- Sql server - find ranges of a column based on another
- Convert HH:MM:SS
- How to get sum by MonthYear in sql?
- What is more efficient to send between web pages objects by session or to send ID and make new queries to sql server?
- SQL Linked Server returns error "no login-mapping exists" when non-admin account is used
- Can I rename a check constraint on a table type in SQL Server 2014?
- SQL Server compare varchar field with binary(16)
- How to get previous record if current record does not exist in table
- How to construct a SQL sub query in SQL Server 2008?
- SELECT SUM () by DISTINCT intervals (Date)
- SQL Server: how to select sub-query column in main query
- How to display row values as column names?
- LAG and LEAD alternative in SQL syntax
- Unable to append to SQL Server table using sqlSave in R
- Delete using where not exists
- Why does below Dynamic Query returns NULL?
- Issue with IsNumeric in t-sql?
- characters appearing incorrectly even with Unicode source and destination (SSIS)
- Updating Duplicate Data From Last Row to First Row
- SQL need advice with substring and charindex
- Constructing a safe dynamic query using SQL Server
- Detect that AppDomain is doomed in sql clr assembly?
- Json functions in SQL Server 2016
- How to remove special charectors in a table dynamically
- Building an android app that will connect to a server that contain a SQL database. How should I do it?
- SQL Server Agent Job History - Delete for only one day
- SQL Server: how can I know my stored procedure is optimum in performance
- Save XML with attribute to Table in SQL Server
- CTE arithmetic shift operator causes arithmetic overflow error
- How to escape a null character in T-SQL?
- Into what C# data type should I retrieve a SQL Server UNIQUEIDENTIFIER?
- Missing index explanation
- How do I insert left joins in a nested query?
- How I can insert a spinner selected item into SQL server for mobile app?
- Store Images in Db or on Server?
- TSQL Extract Number Range from String with Known Ending
- SQL Server / SSRS 2005 CASE evaluation of Parameter and NULL
- The report server cannot process the report or shared dataset
- Code to create & connect to a SQL Server database: Whats wrong with it?
- grouping results in <option> in to one option if a record column is the same
- enumerating rows in sql with multiple same values
- Unique data based on name,email from sql server
- How to get rid of dynamic SQL (T-SQL)
- Multiple COUNT(*) with join
- Debugging SQL in SSMS v18.0+
- How bad is overupdating?
- SQL Server Tedious Callback
- Get data between 20:00 and 05:00 in SQL Server
- How to create number ranges from a list of numbers?
- how to sql recursion solve first node first then move to another in CTE
- Generate Row Number for every 3 rows
- If I have the following SQL database, how can I get this information?
- How to create customized quarter based on a given date column sql sql server
- VARCHAR' is not a recognized built-in function name
- Visual Studio 2013 incompatibility with MS SQL Server 2014
- odbc_prepare for procedure with datetime input parameters
- How to change the data to * but only 50% of the word
- Is it efficient to use SQL functions on the left-hand side of SELECT commands?
- Match two Columns in SQL Join
- SQL: Get the month name from weekcode and year
- Can I optimize this SQL remote update?
- Java: Can we use multi HashMap to group the resultset
- Schema compare - Unexpected exception caught during population of source model: Object reference not set to instance object
- Insert after trigger calling stored proc with insert statement
- "Unable to load the specified metadata resource"
- Use column defined from subquery in query
- SQL: How To Use DateDiff + ORDER BY with INNER JOIN
- JAVA/MSSQL: com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed
- What's the equivalent of Postgres' RAISE NOTICE in SQLServer 2005?
- Insert List<> and check for duplicates
- Sub select SQL query using where and groupby
- How to select all rows where Field = '*'
- How would a single script truncate a particular table in every database?
- Padding number with 0s before doing a insert into a SQL Server database
- First and Last name tables to increase performance?
- BCP multiple files in/out using Azure Active Directory Interactive
- What should i know about switching from sqlite to SQL Server
- T-SQL grouped sum, based on help table
- java JDBC ArrayIndexOutOfBoundsException when delete, update
- Cannot free up unallocated space in SQL Server 2005
- SQL Server, Remove null values in a select case query
- CrudRepository existsBy returns a wrong result
- Update using case statement
- Restrict Admin Pages to Admin Users only
- How to convert row data into column name?
- user with role db_datawriter can't select form database
- Calculate the age and Insert it to an already column via SQL Server
- Update Multiple Row Values at Once using cell a reference
- Strange SQL Server Error That Only Occurs On One Table
- T-SQL exactly 1 condition is true out of a set of conditions
- How to execute a stored procedure with parameters?
- SQL Query: get the unique id/date combos based on latest dates - need speed improvement
- sql strip http and https
- Connecting to SQL Server 2012 (using Windows auth) via powershell Enter-PSSession
- Optimize TSQL when joining a table created using a Table-Valued Function
- How to know where AS keyword should be used?
- How do you do a update if you don't know the id, and just you know a information with another table relationed
- SQL Server - database size calculation difference question
- Remove the zeros using SQL Server
- How to get particular value from SqlCommand
- SQL - Matching two rows from multiple columns in one table to 1 row in another table.
- how to get the unique max data
- Deny Select Permission on a set of tables to a specific user
- sql server trigger syntax
- Should we start with multiple small-grained databases for an app that may scale massively
- Arithmetic overflow error but only on one server
- How to Sort the record for an odd sequence in sql server 2016
- Automatically / programmatically copy tables (structure and data) between SQL Server DBs
- How does DB2 Express compare to Sql Server Express?
- Using a FROM Clause to Join two Queries into a Sub-Query
- How to return a MAX() value without adding all columns to the Group By clause?
- How to get data for the last week data?
- Get Unique data from table