Difference Between WHERE and HAVING in SQL With Examples | Most Important Interview Question

hello everyone welcome back to another video so today's video is going to be an in-depth explanation uh about where and having so i'm gonna discuss the the difference between wear and having so this is a question which is you know which is making a lot of you confused so i'm gonna make this video i'll try to make this video short and we'll try to explain the basic difference so let's start with the video so when having both are used for for filtering of records now what does filtering mean now for example when i write select stuff from employees i am selecting all the details from the employees but when i write select star from employees where age is greater than 20. so i'm giving a filter like i don't want all the details but i want only the details where age is greater than so and so so that is filtering so where and having both are used for filtering but the basic difference between two is that where is used for filtering at row level and having is used for filtering at group level group level now what this what this means is row level means individual rows and group level means whenever aggregates and whenever aggregate functions are involved whenever we are grouping the data that is and then we want to filter then we use filtering so having loss cannot be used without group by clause in most cases so you know just remember it and we'll see one example where where and having is used together in a query so this will clear your doubts but before that let's see these two examples okay now select count sal comma department id from employees group by department id and i have i've written a dash over here to you know to discuss whether we have to write where over here or having okay dash count of sal is greater than 5000 now what is count count is an aggregate function i hope you all know what what are aggregate functions counts are min max all those are aggregate functions so whenever aggregate function is involved as i already said over here whenever grouping is involved whenever aggregate functions involved and we are filtering based on aggregate function then we use having so here what will come having okay now see example number two select star from employees dash sal is greater than 5000. here we are filtering based on a single column your aggregate function is not involved here as you can see we have used count count is greater than sal here we have used aggregate function we are using here we are filtering based on groups but here we are not we are not filtering based on groups but these are individual role level filtering so here we will have where so i hope this example these two examples have made it clear for you all that whenever we are filtering so see this is the two difference here we have filtered based on an aggregate function grouping is involved and we are filtering based on this grouping and hence we are using having but here in this case in case we are here there is no aggregate function here we are filtering based on one single column so your individual individual row level filtering is happening so hence we are using where over here okay now let's see this example where and having is used together in a query so let's see this example can we use where and having together in a query yes we can definitely so look at look over here select count of id comma country from customer where customer is not aware country is not equal to usa just see this this much query first select count of id comma country from customer where country is not equal to usa why have we used where over here because here we are using country one single column name we are using over here okay which is why we are using where where country is not equal to usa but as you can go as you can see ahead group by country then we are grouping it by country first of all what we are doing is first of all we are we are selecting all these but we are removing those countries and sorry we are removing those recalls where countries us so we are removing those and then we are grouping based on countries so again we are grouping so india and different different countries will group them and then i am again filtering when count of id is greater than equal to nine so again although all those filtering will happen on based on different countries like india and other countries and after that wherever count is greater than 9 only those will be printed so whether count is less than 9 those will not be printed so as you can so i hope that was clear why we used having over here because here we are doing filtering based on an aggregate function got it so i hope this this this definitely helped you guys so again i'll just repeat it one last time that you're firstly we are filtering based on row level where country is not equal to usa okay and after that i want to group them after that i am grouping them by country by different different countries i will group them and then i'm again filtering based on that count function so after that and hence we are using having over here so generally whenever you are using wear and having together it is not a rule but generally first where is used then group by and then having so this is the basic order so i hope this video helped you guys in some way so this is the basic difference between wear and having and yes if you like the video don't forget to like share and subscribe to my channel for more such videos and yes thank you so much

Channel: Crack Concepts

Views: 20,273

Rating: 4.9275551 out of 5

Keywords: where and having in sql, WHERE clause in SQL, HAVING clause in SQL, difference between where and having in sql server, where clause and having clause in sql explained with an example, difference between Having and Where Clause in SQL with example, where vs HAVING clause

Id: ggBXefD4UT8

Length: 6min 21sec (381 seconds)

Published: Mon Apr 05 2021