Difference between where and having in sql server
HTML-код
- Опубликовано: 14 сен 2015
- 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 RUclips channel. Hope you can help.
/ @aarvikitchen5572
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
csharp-video-tutorials.blogspo...
Slides
csharp-video-tutorials.blogspo...
All SQL Server Text Articles
csharp-video-tutorials.blogspo...
All SQL Server Slides
csharp-video-tutorials.blogspo...
Full SQL Server Course
• SQL Server tutorial fo...
All Dot Net and SQL Server Tutorials in English
ruclips.net/user/kudvenka...
All Dot Net and SQL Server Tutorials in Arabic
/ kudvenkatarabic
Thanks venkat sir! God bless you!
Many Many Thanks For Your Nice Tutorials.Go Ahead.
Thank you Sir! Your illustration was very clear, and it helped a lot! :)
a very helpful video, thank you!
Thanks for sharing your knowledge. It was helpful to me.
Clear and concise! I also like the way you speak! Good job sir! :)
Great explanation, thank you!
Excellent, thank you
Very useful. Thank you sir
Perfect ! Ty
Great work
Good english pronunciation, and very well explained. Thank you my friend.
Hi venkat sir,can u pls make it one video for execution plan and some tips of performance tunning
nice, thank my friend
Thanks so much!
very well explained. Thank you
Thanks alot sir it helped me a lot!
Superb explaination
Thank U Venkat! Very simple video. I see you add some videos missing in the previous sessions. You are the best teacher. Please go on with this tutorial! I watched all your videos. I'm the biggest fan, my career goes further thanks to you.
0
0
0
0
0
Thank you!
thank you!!!
Perfect
excellent explanation
very nice expiation sir...
Very good 👍👍
Very well explained
thanks :)
Thank you
Hi venkat Sir Please make video on linked servers and performance tuning
very intuitive thanks
Thank you very much for taking time to give feedback. This means a lot. I am very glad you found the videos useful.
I have organised all the Dot Net & SQL Server videos in to playlists, which could be useful to you
ruclips.net/user/kudvenkatplaylists?view=1&sort=dd
If you need DVDs or to download all the videos for offline viewing please visit
www.pragimtech.com/order.aspx
Slides and Text Version of the videos can be found on my blog
csharp-video-tutorials.blogspot.com
Tips to effectively use my youtube channel.
ruclips.net/video/y780MwhY70s/видео.html
If you want to receive email alerts, when new videos are uploaded, please subscribe to my youtube channel.
ruclips.net/user/kudvenkat
If you like these videos, please click on the THUMBS UP button below the video.
May I ask you for a favor. I want these tutorials to be helpful for as many people as possible. Please share the link with your friends and family who you think would also benefit from them.
Good Luck
Venkat
very nice I learned a lot, request you to please share an query where we can break big files into small pieces, i have required it
sir please upload a video of different types of loops used in T-SQL with its real time use.
Can u create a 9th table by using while loop
thnk u
sir plz upload some videos depend on t-sql and tcl commit,rollback and save point
so many years later still useful
Since 9 years I have been from IC to the manager and every time I prepare for an interview or to brush my skills, my default search term is kudvenkat SQL
From Part 10:
where can be uses with select, insert, update, delete
having: only used with select
Hi venkat In an interview I have been asked to find out the error in this query can you help.
create table employeees(
Firstname varchar2(15) Not null,
Midname varchar2(5),
Lastname varchar2(15) Not null,
SSNnumber char(9),
birthday date,
address varchar2(50),
Sex char(1),
salary int default 800,
superSSN char(9),
depnum numeric(5),
primary key(SSNnumber),
check (Sex == 'M' OR 'F' OR 'm' OR 'f'),
foreign key(depnum) references departments(depnum)
);
Sir plz tell me best practice for this ..
if having is executed after the execution of aggregate functions then why can't we use alias name in having clause?
how much total sql server tutorial dvd ?
Good explanation...but low voice....
Please correct me if I am mistaken, both WHERE and HAVING are executed before the SELECT statement which contains the aggregate calculations. So why would it make a difference if we used WHERE over HAVING in our examples?
WHERE cannot be used with aggregate functions, so in that case you have to use HAVING