Correlated subquery in sql Part 60 — Transcript

Full transcript

1. 0:00hello welcome to brim Technologies I am
2. 0:02Venit this is part 60 of SQL Server
3. 0:04video series in this video we'll discuss
4. 0:07about correlated subqueries in SQL
5. 0:09server in part 59 of this video series
6. 0:11we have discussed the basics of SQL
7. 0:13Server we also discussed about how
8. 0:15subqueries can be replaced using joints
9. 0:18if you haven't watched that I would
10. 0:19strongly encourage you to do so before
11. 0:21continuing with the session in this
12. 0:23session we'll discuss about correlated
13. 0:24subqueries correlated subqueries are
14. 0:27very simple and easy to understand now
15. 0:30if the subquery depends on the outer
16. 0:32query for its values then that subquery
17. 0:34is called as a correlated subquery let's
18. 0:37look at an example let's flip to SQL
19. 0:38Server management studio now before we
20. 0:40look at a correlated subquery let's look
21. 0:43at a non-correlated subquery we'll be
22. 0:46using the same tables that we have used
23. 0:48in the previous session of this video
24. 0:50series TBL products and TBL products
25. 0:52sales TBL products table contains the
26. 0:54product data and TBL product sales table
27. 0:56contains sales related data so every
28. 0:59time we sell a product an entry will be
29. 1:00made into this table now let's say I
30. 1:04want to retrieve all the products that
31. 1:06has not been sold at least once how do I
32. 1:08write that query obviously whenever we
33. 1:10make a sale an entry is made into TBL
34. 1:12product sales table now if you look at
35. 1:14the sample data that we have we have
36. 1:16sold product with ID 2 and three meaning
37. 1:18laptop and desktops we haven't sold TV
38. 1:21at least once so I want to retrieve uh
39. 1:24the products that we have not sold at
40. 1:26least one so how do I do that so select
41. 1:29I want the ID name and description we'll
42. 1:31we'll write those columns later first
43. 1:33let's write the from Clause uh so TBL
44. 1:36products select from TBL products now I
45. 1:39have written the from class first
46. 1:41because I'll get intellisense for the
47. 1:43column name so it'll be easier to write
48. 1:45type so select ID name and description
49. 1:49okay so we want these three columns from
50. 1:51this table
51. 1:53where the
52. 1:55ID column in TBL products
53. 1:58table not not
54. 2:02in in this table okay product ID not in
55. 2:06this table so what I will do basically
56. 2:08select product
57. 2:10ID from TBL product sales and since
58. 2:14there are duplicate product IDs I'm
59. 2:17going to use the distinct keyword okay
60. 2:19this is the same query that we have
61. 2:21discussed about in the previous video
62. 2:23session now look at this you know how
63. 2:26does this query gets executed now the
64. 2:29subquery gets it's executed first so if
65. 2:31you look at this query this subquery can
66. 2:33be executed independently so when I
67. 2:36execute that query I get 2 and3 now we
68. 2:38are saying select ID name description
69. 2:40from TBL products where ID not in 2
70. 2:43comma 3 which should give us the product
71. 2:46with ID is equal to one okay this is an
72. 2:49example of a noncorrelated subquery in a
73. 2:52non-correlated subquery the subquery is
74. 2:55not dependent on this outer query for
75. 2:58its values the subquery can be executed
76. 3:02independently on its own okay once the
77. 3:05subquery is executed the results of this
78. 3:07subquery is then substituted within
79. 3:10those parentheses for use by the outer
80. 3:12query okay so in a non-correlated
81. 3:15subqueries subqueries usually gets
82. 3:17executed only once okay now let's look
83. 3:20at an example of a non-correlated
84. 3:22subquery again we'll be using the same
85. 3:24examples that we have discussed about in
86. 3:25the previous session let's say for
87. 3:27example I want to find out you know uh
88. 3:30all the products that we have sold and
89. 3:32the total quantity okay so basically uh
90. 3:35we have three products and I want to
91. 3:37find out all the three product names and
92. 3:39the total Quant total you know quantity
93. 3:42that we have sold so how do we do
94. 3:44that so basically I want the name and
95. 3:48then the total quantity now to retrieve
96. 3:50the total quantity I want to use let's
97. 3:52say subquery okay so I'm going to open
98. 3:55and close parenthesis select now we want
99. 3:58you know the total quantity that we have
100. 4:00sold so I'm going to use sum aggregate
101. 4:03function and sum off I want to aggregate
102. 4:06this quantity sold column so
103. 4:09quantity sold from TBL product sales
104. 4:14table okay
105. 4:17where product
106. 4:19ID is equal
107. 4:21to now for now let's substitute question
108. 4:25mark
109. 4:26there from TBL products table so where
110. 4:30is name column coming from the name
111. 4:32column is coming from TBL products table
112. 4:34and I want to sum up this quantity sold
113. 4:37for each product so the product
114. 4:40ID is going to come from TBL products
115. 4:46table okay so every row that we select
116. 4:49from the outer query so first TV is
117. 4:51selected let's say for example then what
118. 4:55is the subquery look at the subquery
119. 4:57that we have here select sum of
120. 4:58quantities so
121. 5:00so sum of quantity sold from TBL
122. 5:04products where product ID is equal to
123. 5:07look at this we are looking up the
124. 5:08product ID in TBL products table so
125. 5:11every time we select a row from the
126. 5:12outer query now the subquery gets
127. 5:15executed low so when I select this Row
128. 5:18first it then executes this subquery
129. 5:21when that row is being selected okay
130. 5:23where product ID is equal to TBL
131. 5:24products. ID here the ID is one it looks
132. 5:28up that here we don't have an ID so sum
133. 5:31for TVs will be null and then when it
134. 5:34when the query executes the second row
135. 5:37what happens it comes to the subquery
136. 5:39looks up anvl product sales table is
137. 5:41there an entry uh is there a product
138. 5:44with id2 yes there is it tries to sum up
139. 5:46that there's only one row so we only get
140. 5:49seven and then finally when it comes to
141. 5:51three okay product ID is three so there
142. 5:53are three rows so it's going to add 5 4
143. 5:56and 9 so 18 will be the sum so if you
144. 5:59look at this now can I execute this
145. 6:01subquery independently out of you know
146. 6:04independently of the outer query no I
147. 6:06can't look at this when I try to execute
148. 6:08that I get an error because the
149. 6:09multi-art identifier TBL products. ID
150. 6:13cannot be bound now a correlated
151. 6:16subquery will get executed for every row
152. 6:19you know for the outer query in the
153. 6:21outer query for every row you know this
154. 6:23subquery gets executed okay so look at
155. 6:26this when I execute this query together
156. 6:30I get TV laptop and desktop and I get
157. 6:32their some as well okay now I don't have
158. 6:35a column name here but let's go ahead
159. 6:36and give a meaningful column name using
160. 6:39alas so let's say Quantity
161. 6:42sold now we get a column name okay so
162. 6:45that's a correlated subquery because
163. 6:48this subquery is dependent on this outer
164. 6:50query for its value for the value for
165. 6:54this column product ID okay so that's a
166. 6:56correlated subquery so if the subquery
167. 6:59depends on the outer query for its
168. 7:00values then the subquery is called as
169. 7:02correlated subquery in a we Clause look
170. 7:04at this in the we Clause of the subquery
171. 7:07this product ID column gets its value
172. 7:11from TBL products table and TBL products
173. 7:13table is not present within the subquery
174. 7:16okay it's present only within the outer
175. 7:17query so this subquery is dependent on
176. 7:20the outer query for its value in this
177. 7:23case for product
178. 7:24ID so here the subquery is dependent on
179. 7:27the outer query for its value hence the
180. 7:28subquery is a correlated subquery
181. 7:30correlated subqueries get executed once
182. 7:33for every row that is selected by the
183. 7:35outer query correlated subquery cannot
184. 7:38be executed independently of uh of the
185. 7:40outer query but whereas a non-correlated
186. 7:43subquery can be executed independently
187. 7:47um of the outer
188. 7:49query on this slide you can find
189. 7:51resources for asp.net C and SQL server
190. 7:53interview questions that's it for today
191. 7:55thank you for listening have a great day