Difference between where and having in sql server — Transcript

Full transcript

1. 0:00this is part 98 of SQL Server tutorial
2. 0:03in this video we'll discuss the
3. 0:04difference between where and having
4. 0:06Clauses in SQL server for the examples
5. 0:09in this video we'll be using the sales
6. 0:11table I've already created this table
7. 0:13here is the SQL script to create and
8. 0:15populate it with test data I'll have the
9. 0:17script available on my blog in case you
10. 0:19need it this sales table is going to
11. 0:22have an entry for every sale that we
12. 0:24make if you look at the data that we
13. 0:26have here notice we have sold iPhone
14. 0:28twice laptop twice and speakers once now
15. 0:32we want to write a query that's going to
16. 0:35calculate the total sales by product so
17. 0:38the result of that query should be like
18. 0:40this selling iPhones we made
19. 0:43$1,500 laptops 1,400 and speakers 400 we
20. 0:47can very easily achieve this using a
21. 0:49group buy query we want to calculate
22. 0:52total sales by product so we are using
23. 0:55group Buy on this product column since
24. 0:59we want the total sale amount we're
25. 1:01using the summ aggregate function on the
26. 1:03sale amount column so this query is
27. 1:06going to produce this result I've
28. 1:08already typed this query so when we
29. 1:10execute this we should get the total
30. 1:13sales by product now we want to find
31. 1:17only those products where the total
32. 1:20sales is greater than
33. 1:22$1,000 that means we want to filter this
34. 1:25speakers group we can very easily
35. 1:28achieve this using having Clause let's
36. 1:31look at that in action so I am going to
37. 1:33use the having clause and we want to
38. 1:36filter based on this total sales column
39. 1:39and if you look at what is producing
40. 1:40data for this total sales column it is
41. 1:43this expression right here sum of sale
42. 1:45amount so I'm going to use the same
43. 1:48expression and specify our condition
44. 1:51greater than 1,000 so when we execute
45. 1:53this we should get only those products
46. 1:56where the total sales is greater than
47. 1:581,000 so here by using the having Clause
48. 2:02with some aggregate function to filter
49. 2:05the groups now what do you think is
50. 2:07going to happen if we use the we Clause
51. 2:10instead of having look at this when I
52. 2:12execute it we get the syntax error
53. 2:14straight away so this is one of the
54. 2:17primary difference between wear and
55. 2:19having Clauses wear Clause cannot be
56. 2:22used with Aggregates whereas having can
57. 2:25be used with Aggregates there are other
58. 2:28differences as well
59. 2:30where Clause filters rows before
60. 2:33aggregate calculations are performed
61. 2:35whereas having Clause filters rows after
62. 2:37aggregate calculations are performed
63. 2:39let's understand what we mean by this
64. 2:42now we want to calculate total sales of
65. 2:45iPhone and speakers so we know the
66. 2:47products already we want to compute the
67. 2:50total sales you know for those two
68. 2:53products there are two ways we can
69. 2:55achieve this we can achieve this either
70. 2:57by using wear or having claw CL let's
71. 3:00look at that in
72. 3:02action so I'm going to
73. 3:05use the wear Clause here and we want to
74. 3:09compute the total sales
75. 3:12for iPhones and speakers so where
76. 3:16product and
77. 3:18iPhone
78. 3:20comma
79. 3:22speakers so when we execute this notice
80. 3:26that we get the total sales for iPhone
81. 3:28and speakers now we can achieve the same
82. 3:32thing using having as well so I'm going
83. 3:34to make a copy of this query and
84. 3:37then use
85. 3:40having instead of where now let's
86. 3:43execute these two queries look at that
87. 3:46we get the same result now what is the
88. 3:48difference between these two queries now
89. 3:50with the first query this is going to
90. 3:52filter all the rows it is going to
91. 3:54retrieve only Iphone and speaker rows
92. 3:57and then it's going to perform the
93. 3:58aggregate calculation that is sum
94. 4:01whereas with having here it's going to
95. 4:03retrieve all the products it's going to
96. 4:05perform the aggregate calculation on all
97. 4:08the products and then it's going to
98. 4:12filter the
99. 4:14groups so clearly we can see that we
100. 4:17have a performance impact when we use
101. 4:20having Clause so from a performance
102. 4:22standpoint having is going to be slower
103. 4:24than where and should be avoided when
104. 4:27possible because having here is just
105. 4:30going to filter the groups whereas where
106. 4:32filters you know all the rows it only
107. 4:34gets iPhones and speakers and then
108. 4:36performs the aggregation on those two
109. 4:38products whereas in this case it
110. 4:40performs the aggregation for all the
111. 4:42products and then filters iPhones and
112. 4:44speakers groups okay so having is
113. 4:48definitely slower than where in this
114. 4:50case and should be
115. 4:52avoided so let's quickly recap the
116. 4:55differences we Clause cannot be used
117. 4:57with Aggregates whereas having can this
118. 5:00means wear Clause is used for filtering
119. 5:02individual rows whereas having Clause is
120. 5:05used to filter groups where comes before
121. 5:08Group by this means where Clause filters
122. 5:11rows before aggregate calculations are
123. 5:13performed having comes after Group by
124. 5:16this means having CLA filters rows after
125. 5:19aggregate calculations are performed so
126. 5:21from a performance standpoint having is
127. 5:23slower than where and should be avoided
128. 5:25when possible there are also situations
129. 5:28where you can use wear and having
130. 5:30Clauses together in a select query in
131. 5:33this case we Clause is applied first to
132. 5:36filter individual rows the rows are then
133. 5:39grouped and aggregate calculations
134. 5:40performed and then the having Clause
135. 5:43filter the groups so let's look at an
136. 5:45example of using where and having
137. 5:49together so here we are saying you know
138. 5:51this is going to retrieve the sum of
139. 5:54iPhone and speakers now let's say we
140. 5:57want only that product group where the
141. 6:01total sales is greater than $1,000 if
142. 6:04that's the case I can use having and use
143. 6:09our aggregate function sum of total I
144. 6:11mean sale amount greater than 1,000 so
145. 6:15when we execute this now we should only
146. 6:18get the iPhone row because that's the
147. 6:20only row where total sales is greater
148. 6:22than 1,000 so in this query we're using
149. 6:25both the wear and having
150. 6:28clauses
151. 6:31thank you for listening and have a great
152. 6:34day