Difference between where and having in sql server Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 98 of sequel server
tutorial in this video we'll discuss the
difference between where and having
clauses in cycle server for the examples
in this video we'll be using the sales
table I've already created this table
here is the sequel script to create and
populate it with test data
I'll have the script available on my
blog in case you need it
this sales table is going to have an
entry for every sale that we make if you
look at the data that we have here
notice we have sold iPhone twice laptop
twice and speakers once 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 this
selling iPhones we made $1500 laptops
1400 and speakers 400 we can very easily
achieve this using a group by query we
want to calculate total sales by product
so we are using group buy on this
product column since we want the total
sale amount we're using the sum
aggregate function on the sale amount
column so this query is going to produce
this result I've already typed this
query so when we execute this we should
get the total sales by product now we
want to find only those products where
the total sales is greater than $1000
that means we want to filter this
speaker's group 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
and if you look at what is producing
data for this total sales column it is
this expression right here sum of sale
amount so I'm going to use the same
expression and specify our condition
greater than 1000 so when we execute
this we should get only those products
where the total sales is greater than
1000 so here by using the having clause
with some aggregate function to filter
the groups now what do you think is
going to happen if we use the where
clause instead of having
look at this when I execute it we get
the syntax error straightaway so this is
one of the primary difference between
where and having clauses where Clause
cannot be used with the aggregates
whereas having can be used with
aggregates there are other differences
as well where Clause filters rules
before aggregate calculations are
performed whereas having clause filters
rose after aggregate calculations of the
form let's understand what we mean by
this now we want to calculate total
sales of iPhone and speakers so we know
the products already we want to compute
the total sales you know 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 very laws here and we want to
compute the total sales for iPhones and
speakers so where product 10 iPhone
comma speakers so when we execute this
notice that we get the total sales for
iPhone and speakers now we can achieve
the same thing using having as well so
I'm going to make a copy of this query
and then use having instead of where now
let's execute these two queries look at
that we get the same result now what is
the difference between these two queries
now with the first query this is going
to filter all the rows it is going to
retrieve only iPhone and speaker rows
and then it's going to perform the
aggregate calculation that is sum
whereas with having here 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 from a
performance standpoint having is going
to be slower than
we're and should be avoided when
possible because having here is just
going to filter the groups whereas we're
filters you know all the rules it only
gets I phones and speakers and then
performs the aggregation on those two
products whereas in this case it
performs the aggregation for all the
products and then filters iPhones and
speakers groups okay so having is
definitely slower than we're in this
case and should be avoided so let's
quickly recap the differences where
clause cannot be used with aggregates
whereas having can this means where
clause is used for filtering individual
rows whereas having clause is used to
filter groups where comes before group
by this means where Clause filters rules
before aggregate calculations of the
form having comes after group by this
means having Clause filters rules after
aggregate calculations are performed so
from a performance standpoint having is
lower than where and should be avoided
when possible there are also situations
where you can use where and having
clauses together in a select query in
this case where Clause is applied first
to filter individual rules the rules are
then grouped and aggregate calculation
to perform and then the having Clause
filter the groups so let's look at an
example of using where and having
together so here we are saying you know
this is going to retrieve the sum of i
phone and speakers now let's say we want
only that product group where the total
sales is greater than one thousand
dollars if that's the case I can use
having and use our aggregate function
sum of total in sale amount greater than
one thousand so when we execute this now
we should only get the iPhone drop
because that's the only row where total
sales is greater than one thousand so in
this query we're using both the where
and having clauses
thank you for listening and have a great
day