Union and union all in sql server Part 17 — Transcript

Full transcript

1. 0:00hello welcome to preim Technologies I am
2. 0:02wenet this is part 17 of SQL server in
3. 0:06this session we'll talk about the
4. 0:07purpose of Union and Union all the
5. 0:09differences between them and finally
6. 0:11we'll also talk about the differences
7. 0:13between joins and
8. 0:15unions Union and Union all operators in
9. 0:18SQL Server are used to combine the
10. 0:21result set of two or more select queries
11. 0:24let's understand what we mean by this
12. 0:25with an example we have two tables here
13. 0:28TBL India customers and TBL UK customers
14. 0:32and both of these tables have identical
15. 0:34columns you know ID name email in both
16. 0:37of the tables now let's say if I issue a
17. 0:40select query against these two tables
18. 0:43look at this I am issuing two different
19. 0:46select queries and if I just execute
20. 0:48them look at the result I get two
21. 0:52separate result sets Okay in TBL India
22. 0:54customers we have got two rows and TBL
23. 0:57UK customers we have got two rows and I
24. 0:59get these as two separate result sets
25. 1:02now let's say I want to combine both of
26. 1:04the result sets into one result set how
27. 1:07do I do that using Union or Union all
28. 1:11okay let's first try to use Union all
29. 1:13and see what happens okay so select star
30. 1:16from TBL India customers table and then
31. 1:19I'm
32. 1:20saying Union
33. 1:23all select star from TBL UK customers
34. 1:27and when I execute this query look at
35. 1:29what's going to happen it's going to
36. 1:30combine the row from TBL India customers
37. 1:34with the row from TBL UK customers so
38. 1:36when I use this Union all I'm combining
39. 1:39the result sets from these two select
40. 1:41queries okay now instead of using Union
41. 1:44all if I just say Union let's see what's
42. 1:47going to
43. 1:48happen okay look at this I got only
44. 1:52three rows but let's put it back to
45. 1:54Union all we get four rows and if you
46. 1:57look at the output here there is a d
47. 1:59duplicate record here number two Sam and
48. 2:02email is ss.com so when I use Union all
49. 2:07I'm getting all the rows including the
50. 2:09duplicates but the moment I remove Union
51. 2:11all and if I just use Union look at what
52. 2:15happens to Sam record when I execute
53. 2:17this you know we have got only one Sam
54. 2:20record and look at the output it is
55. 2:22sorted as well okay but whereas when we
56. 2:26use Union
57. 2:28all you know it's basically not sorted
58. 2:31it takes all the rows from TBL India
59. 2:33customers you know combines them with
60. 2:36the rows from TBL UK customers and just
61. 2:38gives the output but whereas when you
62. 2:40use Union it's doing two things here
63. 2:43it's removing removing duplicates and
64. 2:45it's also sorting the results okay uh
65. 2:49we'll talk about the differences between
66. 2:50union and Union all in the next
67. 2:54slide so if you look at the differences
68. 2:56between union and Union all Union remove
69. 2:59moves duplicate rows whereas Union all
70. 3:02doesn't remove them and another
71. 3:04difference is for Union to actually
72. 3:08remove the duplicates it has to perform
73. 3:11a distinct
74. 3:12sort and then it has to remove the
75. 3:14duplicates so for this reason you know
76. 3:18Union is a little slower than Union all
77. 3:22and this distinct sort operation is
78. 3:24actually resource intensive in a sense
79. 3:27it's time consuming okay if you want to
80. 3:29see that you can actually turn on the
81. 3:31estimated query execution plan in SQL
82. 3:33Server management studio and to do that
83. 3:36you can either press control l or you
84. 3:38have an icon here which displays an
85. 3:41estimated execution
86. 3:43plan so display estimated execution plan
87. 3:47when we click that what's going to
88. 3:49happen is it's going to show us the
89. 3:50estimated execution plan look at this in
90. 3:53the display here the distinct sort uh is
91. 3:56actually taking 63% of the uh you know
92. 4:00time okay but whereas on the other hand
93. 4:03when I use Union all and we say display
94. 4:06estimated execution plan there is no
95. 4:09distinct
96. 4:10sort okay so it just selects the records
97. 4:13from TBL India customers and then
98. 4:15combines them with the result from TBL
99. 4:17UK customers and just displace them but
100. 4:19whereas when we use Union it has to
101. 4:23First sort the records and then remove
102. 4:25the duplicates which is a little timeing
103. 4:27time consuming so for this reason reason
104. 4:30Union is a little slower than Union all
105. 4:33so these are the two differences between
106. 4:34union and Union all Union removes
107. 4:36duplicate rows whereas Union all doesn't
108. 4:39and Union is a little slower than Union
109. 4:42all because of the fact that it has to
110. 4:44perform that distinct sort to remove
111. 4:47duplicates and this is a common
112. 4:48interview question that's asked these
113. 4:53days okay so obviously if you use Union
114. 4:58we don't get the duplicates whereas when
115. 4:59you use Union all we get the duplicates
116. 5:03okay and remember for Union and Union
117. 5:06all to work the number data types and
118. 5:09the Order of the columns in the select
119. 5:11statement should be the same okay
120. 5:13because and that should make sense as
121. 5:15well you know when we actually execute
122. 5:17this query look at this you know if if
123. 5:20we have to combine the results of this
124. 5:23select statement with the results of
125. 5:25this select statement obviously the data
126. 5:28types and the number of columns has to
127. 5:29be same okay on the other hand if you
128. 5:32just say here I want ID and name and if
129. 5:36you say here I want all the three
130. 5:39columns
131. 5:40ID name and email okay now if I try to
132. 5:45execute this look at this looking at
133. 5:48itself you can say Okay first select
134. 5:49statement is giving two columns whereas
135. 5:51this one is giving three columns how can
136. 5:53we combine them we can't do that so
137. 5:56obviously when we try to execute that we
138. 5:58get an error all queries combined using
139. 6:01a union must have an an an equal number
140. 6:04of expressions in the in their target
141. 6:07list which means equal number of columns
142. 6:09not only that okay it's not enough if
143. 6:11you just have equal number of columns
144. 6:13you know they should be in the right
145. 6:15order you know instead of if you say you
146. 6:18know I want ID as the last column it
147. 6:21doesn't really make sense okay how can
148. 6:23you combine ID with email okay you can't
149. 6:27do that so when we execute this look at
150. 6:29this the conversion fail okay because
151. 6:33what's happening you know it's trying to
152. 6:35convert either this one I mean name to
153. 6:40integer and it
154. 6:42fails okay because it detects this as an
155. 6:45integer column and it tries to convert
156. 6:48this to integer and it can't be the
157. 6:51implicit conversion fails and we get the
158. 6:53error so the number of the columns has
159. 6:55to be the same the data types have to be
160. 6:57the same and they have to be in the same
161. 6:59order okay now you can do this okay look
162. 7:03at this I'm saying okay I want ID you
163. 7:07can say ID and email and then name okay
164. 7:13you can do this when I execute this look
165. 7:15at what's going to
166. 7:17happen Okay um I get the output but if
167. 7:21you look at the output emails are
168. 7:23combined with names and similarly here
169. 7:25names are combined with emails okay so
170. 7:29as long as the data types are similar it
171. 7:31tries to combine them you know even if
172. 7:34they are
173. 7:35different it it tries to do an implicit
174. 7:38conversion if the implicit conversion H
175. 7:40works then it selects them and combines
176. 7:43them okay uh but if the implicit
177. 7:45conversion fails that's when we get an
178. 7:47error so remember the number of columns
179. 7:52and their data types have to be similar
180. 7:54and even the order has to be the same
181. 7:56you know if in your first query you have
182. 7:59the First Column as integer in the
183. 8:02second query the First Column has to be
184. 8:03integer if here it's an nare care this
185. 8:07has to be an N back basically a string
186. 8:09one a string column okay so their data
187. 8:12types and their orders have to match and
188. 8:15you need to have equal number of columns
189. 8:17otherwise the union or Union all doesn't
190. 8:20work you get an
191. 8:23error and we have already spoken about
192. 8:25the differences between union and Union
193. 8:27all and sorting the results of a union
194. 8:30or Union all okay this is interesting
195. 8:32and we know that to sort the results uh
196. 8:34you know in SQL we use the order by
197. 8:36Clause but let's say I have you know
198. 8:40let's use star for
199. 8:43now okay let's say I'm saying select
200. 8:46star from India customers Union select
201. 8:49star from UK customers when we execute
202. 8:51this we get the results now let's say I
203. 8:53want to sort the results by name okay
204. 8:57how do I do that we use the order by CL
205. 8:59class so order by maybe name when we do
206. 9:03that look at this it's not sorted now
207. 9:05but the moment I execute this query it
208. 9:07sorts the results for us okay now if I
209. 9:10use order by on the first query let's
210. 9:14see what is going to happen now when I
211. 9:16execute this look at this it gives us an
212. 9:19error stating incorrect syntax near the
213. 9:21keyword and it makes sense look at this
214. 9:23how does Union all works it takes the
215. 9:26rows from this table combines them with
216. 9:28the rows from this table table now it
217. 9:30doesn't make sense to First Take sorted
218. 9:32rows and then combine your result will
219. 9:34again become unsorted that's why after
220. 9:37getting all the results then sort the
221. 9:41results finally using your order by
222. 9:43class that's why I think autoby should
223. 9:46be only you know within the last select
224. 9:48statement for example I have another
225. 9:51table called uh TBL us
226. 9:53customers so when I say Union
227. 9:58all select star from
228. 10:02TBL us customers and we execute that
229. 10:05it's going to get all the rows from all
230. 10:07the tables and then it's going to sort
231. 10:09them okay but on the other hand if if
232. 10:11you try to use it on the second select
233. 10:14you still get an error you
234. 10:16know okay so if you want to S the
235. 10:19results of Union use the autoby clause
236. 10:22on the last select
237. 10:24statement like
238. 10:27so all right and and differences between
239. 10:30union and join we have spoken about
240. 10:32joins in the previous sessions in this
241. 10:34video series okay now unions and joints
242. 10:38are two separate things okay but however
243. 10:41you know these days people are asking
244. 10:43this question what's the difference
245. 10:45between a union and join okay join
246. 10:48basically if you look at this Union
247. 10:50combines the result set of two or more
248. 10:52select queries into a single result set
249. 10:55which includes all the rows from all the
250. 10:58queries in that Union
251. 10:59whereas what what a join does is it
252. 11:03retrieves data from you know two or more
253. 11:06tables based on The Logical relationship
254. 11:08between those two tables so usually
255. 11:10tables are related to each other using
256. 11:12primary and foreign Keys okay so we will
257. 11:15take you know the foreign key and look
258. 11:17up its value in the other table and join
259. 11:19the columns together in those related
260. 11:23tables whereas in Union you know we
261. 11:26basically combine the rows so in short
262. 11:29the difference between a union and a
263. 11:31join is that Union combine rows from two
264. 11:34or more tables whereas join combines
265. 11:36columns from two or more you know tables
266. 11:39for example you know we have seen the
267. 11:41examples of Union we have taken roles
268. 11:43from TBL USA customers and joined them
269. 11:46with TBL UK customer rows know we did a
270. 11:49horizontal kind of a join whereas if you
271. 11:51look at U you know basically not join
272. 11:54it's um uh Union okay but whereas when
273. 11:57you look at a join for example we have
274. 12:00TBL employees table and TBL departments
275. 12:02table in TBL employees table we have got
276. 12:05Department ID column which we then look
277. 12:08up in the Department's table and maybe
278. 12:11we we retrieve the department name so
279. 12:12from TBL employees table I get employee
280. 12:15name and maybe his age and then from TBL
281. 12:19Department table we get the Department
282. 12:21name so enjoins we combining the columns
283. 12:25between two or more tables whereas in an
284. 12:27Union we are combining row between two
285. 12:29or more
286. 12:31tables on this slide you can find
287. 12:33resources for asp.net and C interview
288. 12:36questions that's it for today thank you
289. 12:38for listening have a great day