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.
/****** 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 create a new mySQL database user from php
- TSQL: how to eliminate join on Null values when Column value is available
- Which approach is better for saving the error in SSIS
- Quoted Identifier - EF Code First
- Using Multiple IF NOT EXISTS Statements Inside A Single Query
- MIN of XML descendats of date type
- Need to Copy Data with Parent Ids
- How to convert string dd.mm.yyyy to date in SQL
- How to name a cursor dynamically in SQL Server
- Logging not Persisting When Exception Occurs in Method Executed in a Trigger
- SQL Server Column names case sensitivity
- java JDBC ArrayIndexOutOfBoundsException when delete, update
- How do I re-write this MS Access expression for SQL Server
- Trying to set up Full Text Search for SQL Server Via Script
- MS PowerApps: How to "Patch" a SQL Table with Composite Primary Key
- SQL query that returns any of the words found
- How do install pdo_sqlsrv driver in elastic beanstalk to connect SQL server db instance
- Securing SQL Server for the internet
- SQL Server Always Encrypted: Operand type clash: varchar is incompatible with varchar(max)
- RODBCExt: sqlExecute issue with dates
- Access field contents length 255 characters
- SQL - Split table based upon a column's content
- How to get detailed list of connections to database in sql server 2005?
- SQL schema and value
- How do you identify if sql server instance is an 'always on' replica?
- Calculating returns within SQL query
- insert binary(16) with executemany
- SSIS - Compare Excel With Database
- SQL DENSE_RANK and PARTITION BY
- SQL Server to MySQL
- How to fetch value from another row within the same data pipleline?
- SSAS error when processing data, after picking the database table
- Can anyone explain the following stack trace, in step by step, please?
- find the count using group by
- Using ORDER BY with UNION and ALIASES
- Ways to Automate excel for reporting on a scheduled basis where pivot tables wont cut it and data is coming from many places?
- BIRT 4.4.0 PARAMETERS getting rounded at runtime
- SQL Server to return text if there are transactions with multiple values
- C# Print multiple pages from SQL result
- Why can I not express this subquery? Is there an alternative?
- Mirroring - server network address cannot be reached or does not exist
- Convert float/int to Calendar in jpa hibernate SQL Server
- Build Expression Tree convertible to valid SQL dynamically that can compare string with doubles
- SQL Error: The executeQuery method must return a result set
- How do I access Excel data source from an SSIS package deployed on a 64-bit server?
- How do I get one distinct record per IP Address Per Day?
- SQL server date convertion
- Smarter code to query the same parameter from multiple columns?
- Best practice: Searching table against day, month or year
- Delete the records from a table ON THE following rules
- How to replace part of text in SQL server?
- How to group database column?
- Add a new table column to specific ordinal position in Microsoft SQL Server
- How to send in "NULL/DbNull" into a table-column with the datatype: nullable int
- how to select the values in the table in this format
- Creating buttons on a form that should go according to data retrieved from a database using linq to sql
- How to get data for each month from current date sql query
- Sort Everything After A Specific Character in SQL
- SQL data insertion to a column error
- SQL Server: arithmetic overflow error converting expression to data type int in where condition
- Is it possible to ignore varchar length when setting foreign key between tables?
- Symfony2 + Doctrine2 + SQL Server - Transaction Support
- SQL Server - juxstaposition of verses
- Table has sql statements in each row. How do I run each in order?
- Error While Copying database from SQL server 2005 to SQL server 2008 using Import wizard
- SQL deadlock while attempting IX lock on page
- SQL: Insert Into with multiple case whens - syntax error
- SQL Find missing date ranges
- Why does Microsoft SQL Server Allow Foreign Key Duplicates?
- Get list of dependent Tables, SQL Server 2005
- SQL Dependency causing errors in SQL Server
- SQL Delete (Suspended in activity monitor)
- When restoring SQL DB from .bak file all tables are missing
- WPF SQL Distributed Data Model with LINQ and SQL Express
- How to deal with Unicode replacement character � (0xFFFD / 65533) in SQL
- What is the easiest way to get total number for lines of code (LOC) in SQL Server?
- collation conflict between "Hebrew_CI_AS" and "SQL_Latin1_General_CP1_CI_AS"
- How can I select between record without where in SQL Server
- Is there a better way to split work for simple updates?
- Multiple triggers vs a single trigger
- Idiom for using SqlDataReader as a resource
- First steps to SQL Server
- Connecting to SQL Azure from a Java application
- SQL Insert query with missing column and Invalid column name
- SQL query how do i group by name in a table
- How to do hit-highlighting of results from a SQL Server full-text query
- Running a stored procedure as a job in SQL Server Management Studio
- How to Connect to SQL Server using LINQ to SQL?
- Difference Between "When the Job Succeeds" and "When the Job Completes"
- How do I replicate data from the Common Data Service to SQL Server on Azure?
- TSQL - Return Boolean result in select query
- Why does SQL Management Studio output null separated characters when saving as csv?
- Deleting Tree-Nodes in a SQL table
- How to upload image from C# application to SQL Server 2005
- Using SQL concatenation with ORDER BY
- REST API (JSON) that updates SQL Table using Windows Console Application and Scheduled Tasks
- Aliasing Table in SQL Server and Using Exists
- How to convert numeric to nvarchar in SQL Server?
- Optimized SQL for tree structures
- Create & Copy Access tables to SQL via SSIS? SQL 2008
- SQL Nested Select Statement
- Extract Binary Data from SQL Server using T-SQL
- Update Based on Self-Join
- SQL Query For pagination has Filter
- Sql Server Data Type of a List Type
- Database update SQL
- what is numeric(18, 0) in sql server 2008 r2
- SQL query to divide all records into a 70:30 ratio based on 3 criteria
- Merge Multiple Rows Based On Unique Identifier in SQL Server (GROUP_CONCAT for SQL Server)?
- Add average column to Pivot Table SQL
- T-SQL CASE / Subquery
- How to create loop based on value of row?
- SQL Insert if record does not already exist inserts anyway
- Report builder 3.0 with asp.net
- How to get sum of duplication count value?
- sqlsrv_fetch with sqlsrv_execute in MS SQL server
- SQL: Search the text that contains in arrays
- How to pass table value parameter to user defined data table using C# and SQL
- Pass auto generated id to other table
- Split string into table given row delimiter and column delimiter in SQL server
- EF Frustration mapping a view to an object
- How to join with results on the same line in SQL?
- How do I recover when tSQLt faketable does not reset table mapping?
- Delete Every Alternate Row in SQL
- SQL order by needs to check if DATETIME2 is not null then return them first and after order by id
- Cannot use MSSQL Timestamp as a parameter in Delphi XE8
- SQL Server MERGE + Joining other tables
- Selecting BLOB column from Oracle database to SQL Server database over linked server
- User input registration then geocoded into database
- Custom SSIS Task Failing To Load. How Do I Troubleshoot?
- What is wrong with my code, meaning that no details entered still logs the user in?
- Cannot connect Strongloop / Loopback datasource to a SQL Server Express database
- How to do a complex where clause involving dates
- How to iterate in SQL Server without while loop
- Does ordering matter for fields in a SQL update?
- Is it possible to query for a specific data type?
- Create two or more triggers in a row by executing a query with SQL Server
- SQL Server detect other browsers from navigator.userAgent
- How Should I Design TAX Table?
- Understanding SQL Recursion
- Stored procedure: reduce code duplication using temp tables
- SQL: How use case and cast in a query?
- How do I perform an accent insensitive compare in SQL Server for 1250 codepage
- SQL Server 2008 trace query and output (record rows) of query execution
- Update row with condition in SQL Server 2008
- Random sorting algorithm with spacing logic for duplicates
- Unsupported Data Type when using SQL Data Sync to Azure
- SQL compare two columns for same value
- How to generate SQL data based on existing tables?
- Comma Separated SQL Server Result Set 'JOINED' with other Columns