Difference between where and having in sql server
sql server where vs having
sql server group by where having
group by having sql server
In this vide we will discuss the difference between where and having caluses in sql server.
Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.
https://www.youtube.com/channel/UC7sEwIXM_YfAMyonQCrGfWA/?sub_confirmation=1
Let us understand the difference with an example.
To calculate total sales by product, we would write a GROUP BY query as shown below
SELECT Product, SUM(SaleAmount) AS TotalSales
FROM Sales
GROUP BY Product
Now if we want to find only those products where the total sales amount is greater than $1000, we will use HAVING clause to filter products
SELECT Product, SUM(SaleAmount) AS TotalSales
FROM Sales
GROUP BY Product
HAVING SUM(SaleAmount) ] 1000
If we use WHERE clause instead of HAVING clause, we will get a syntax error. This is because the WHERE clause doesn’t work with aggregate functions like sum, min, max, avg, etc.
SELECT Product, SUM(SaleAmount) AS TotalSales
FROM Sales
GROUP BY Product
WHERE SUM(SaleAmount) ] 1000
So in short, the difference is WHERE clause cannot be used with aggregates where as HAVING can.
However, there are other differences as well that we need to keep in mind when using WHERE and HAVING clauses. WHERE clause filters rows before aggregate calculations are performed where as HAVING clause filters rows after aggregate calculations are performed. Let us understand this with an example.
Total sales of iPhone and Speakers can be calculated by using either WHERE or HAVING clause
Calculate Total sales of iPhone and Speakers using WHERE clause : In this example the WHERE clause retrieves only iPhone and Speaker products and then performs the sum.
SELECT Product, SUM(SaleAmount) AS TotalSales
FROM Sales
WHERE Product in ('iPhone', 'Speakers')
GROUP BY Product
Calculate Total sales of iPhone and Speakers using HAVING clause : This example retrieves all rows from Sales table, performs the sum and then removes all products except iPhone and Speakers.
SELECT Product, SUM(SaleAmount) AS TotalSales
FROM Sales
GROUP BY Product
HAVING Product in ('iPhone', 'Speakers')
So from a performance standpoint, HAVING is slower than WHERE and should be avoided when possible.
Another difference is WHERE comes before GROUP BY and HAVING comes after GROUP BY.
Difference between WHERE and Having
1. WHERE clause cannot be used with aggregates where as HAVING can. This means WHERE clause is used for filtering individual rows where as HAVING clause is used to filter groups.
2. WHERE comes before GROUP BY. This means WHERE clause filters rows before aggregate calculations are performed. HAVING comes after GROUP BY. This means HAVING clause filters rows after aggregate calculations are performed. So from a performance standpoint, HAVING is slower than WHERE and should be avoided when possible.
3. WHERE and HAVING can be used together in a SELECT query. In this case WHERE clause is applied first to filter individual rows. The rows are then grouped and aggregate calculations are performed, and then the HAVING clause filters the groups.
Text version of the video
http://csharp-video-tutorials.blogspot.com/2015/09/difference-between-where-and-having-in.html
Slides
http://csharp-video-tutorials.blogspot.com/2015/09/difference-between-where-and-having-in_15.html
All SQL Server Text Articles
http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html
All SQL Server Slides
http://csharp-video-tutorials.blogspot.com/p/sql-server.html
Full SQL Server Course
https://www.youtube.com/playlist?list=PL4cyC4G0M1RQ_Rm52cQ4CcOJ_T_HXeMB4
All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd
All Dot Net and SQL Server Tutorials in Arabic
https://www.youtube.com/c/KudvenkatArabic/playlists
Видео Difference between where and having in sql server канала kudvenkat
sql server group by where having
group by having sql server
In this vide we will discuss the difference between where and having caluses in sql server.
Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.
https://www.youtube.com/channel/UC7sEwIXM_YfAMyonQCrGfWA/?sub_confirmation=1
Let us understand the difference with an example.
To calculate total sales by product, we would write a GROUP BY query as shown below
SELECT Product, SUM(SaleAmount) AS TotalSales
FROM Sales
GROUP BY Product
Now if we want to find only those products where the total sales amount is greater than $1000, we will use HAVING clause to filter products
SELECT Product, SUM(SaleAmount) AS TotalSales
FROM Sales
GROUP BY Product
HAVING SUM(SaleAmount) ] 1000
If we use WHERE clause instead of HAVING clause, we will get a syntax error. This is because the WHERE clause doesn’t work with aggregate functions like sum, min, max, avg, etc.
SELECT Product, SUM(SaleAmount) AS TotalSales
FROM Sales
GROUP BY Product
WHERE SUM(SaleAmount) ] 1000
So in short, the difference is WHERE clause cannot be used with aggregates where as HAVING can.
However, there are other differences as well that we need to keep in mind when using WHERE and HAVING clauses. WHERE clause filters rows before aggregate calculations are performed where as HAVING clause filters rows after aggregate calculations are performed. Let us understand this with an example.
Total sales of iPhone and Speakers can be calculated by using either WHERE or HAVING clause
Calculate Total sales of iPhone and Speakers using WHERE clause : In this example the WHERE clause retrieves only iPhone and Speaker products and then performs the sum.
SELECT Product, SUM(SaleAmount) AS TotalSales
FROM Sales
WHERE Product in ('iPhone', 'Speakers')
GROUP BY Product
Calculate Total sales of iPhone and Speakers using HAVING clause : This example retrieves all rows from Sales table, performs the sum and then removes all products except iPhone and Speakers.
SELECT Product, SUM(SaleAmount) AS TotalSales
FROM Sales
GROUP BY Product
HAVING Product in ('iPhone', 'Speakers')
So from a performance standpoint, HAVING is slower than WHERE and should be avoided when possible.
Another difference is WHERE comes before GROUP BY and HAVING comes after GROUP BY.
Difference between WHERE and Having
1. WHERE clause cannot be used with aggregates where as HAVING can. This means WHERE clause is used for filtering individual rows where as HAVING clause is used to filter groups.
2. WHERE comes before GROUP BY. This means WHERE clause filters rows before aggregate calculations are performed. HAVING comes after GROUP BY. This means HAVING clause filters rows after aggregate calculations are performed. So from a performance standpoint, HAVING is slower than WHERE and should be avoided when possible.
3. WHERE and HAVING can be used together in a SELECT query. In this case WHERE clause is applied first to filter individual rows. The rows are then grouped and aggregate calculations are performed, and then the HAVING clause filters the groups.
Text version of the video
http://csharp-video-tutorials.blogspot.com/2015/09/difference-between-where-and-having-in.html
Slides
http://csharp-video-tutorials.blogspot.com/2015/09/difference-between-where-and-having-in_15.html
All SQL Server Text Articles
http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html
All SQL Server Slides
http://csharp-video-tutorials.blogspot.com/p/sql-server.html
Full SQL Server Course
https://www.youtube.com/playlist?list=PL4cyC4G0M1RQ_Rm52cQ4CcOJ_T_HXeMB4
All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd
All Dot Net and SQL Server Tutorials in Arabic
https://www.youtube.com/c/KudvenkatArabic/playlists
Видео Difference between where and having in sql server канала kudvenkat
Показать
Комментарии отсутствуют
Информация о видео
Другие видео канала
![Group by in sql server - Part 11](https://i.ytimg.com/vi/FKSSOpQe5Jc/default.jpg)
![Part 1 How to find nth highest salary in sql](https://i.ytimg.com/vi/fvPddKyHxpQ/default.jpg)
![Lec-53 Difference between Delete, Drop & Truncate in SQL | DBMS](https://i.ytimg.com/vi/_m1aJdD-oD8/default.jpg)
![](https://i.ytimg.com/vi/Iae0pfVrZ1o/default.jpg)
![Clustered and nonclustered indexes in sql server Part 36](https://i.ytimg.com/vi/NGslt99VOCw/default.jpg)
![Difference Between WHERE and HAVING in SQL With Examples | Most Important Interview Question](https://i.ytimg.com/vi/ggBXefD4UT8/default.jpg)
![Table valued parameters in SQL Server](https://i.ytimg.com/vi/Ewf5bivTKdI/default.jpg)
![Difference between rank dense rank and row number in SQL](https://i.ytimg.com/vi/MZTSHDFuCUk/default.jpg)
![SQL Having Clause - SQL Training Online - Quick Tips Ep16](https://i.ytimg.com/vi/9VnaNTBkdXQ/default.jpg)
![Rollup in SQL Server](https://i.ytimg.com/vi/HLTdfCtfIJs/default.jpg)
![Aggregate Functions, Group by Clause and Having Keyword in SQL ? | SQL Server Interview Questions](https://i.ytimg.com/vi/K2mFsfhLckw/default.jpg)
![Window functions in SQL Server](https://i.ytimg.com/vi/TzsrO4zTQj8/default.jpg)
![SQL "difference between" interview questions (part 1)](https://i.ytimg.com/vi/RZc4QSRRk98/default.jpg)
![Joins in sql server - Part 12](https://i.ytimg.com/vi/wW4xcQ3FFp4/default.jpg)
![Union and union all in sql server Part 17](https://i.ytimg.com/vi/9w5uRCFOiTo/default.jpg)
![Part 4 Delete duplicate rows in sql](https://i.ytimg.com/vi/ynWgSZBoUkU/default.jpg)
![Advanced Aggregate Functions in SQL (GROUP BY, HAVING vs. WHERE)](https://i.ytimg.com/vi/nNrgRVIzeHg/default.jpg)
![Stored procedures in sql server Part 18](https://i.ytimg.com/vi/Qu3E-oncF3g/default.jpg)
![SQL Server Interview Questions and Answers | SQL Interview Questions](https://i.ytimg.com/vi/SEdAF8mSKS4/default.jpg)
![WHERE clause and HAVING clause in SQL explained with an example](https://i.ytimg.com/vi/4CdOYVZLJWE/default.jpg)