Self join in sql server - Part 14 — Transcript

Full transcript

1. 0:00Hello, welcome to Premium Technologies.
2. 0:02I'm Wenut. This is part 14 of SQL
3. 0:04Server. In this session, we'll talk
4. 0:07about self join which is nothing but
5. 0:09joining a table with itself. We'll also
6. 0:11talk about self- join classification.
7. 0:14Most of the people think self join is a
8. 0:16different type of join altogether which
9. 0:18is not true. Self join can be classified
10. 0:21as inner, outer or cross join depending
11. 0:25on how you write the self join. Before
12. 0:28continuing with the session, I strongly
13. 0:30recommend watching parts 12 and 13 of
14. 0:32this video
15. 0:34series. Now, in parts 12 and 13, we have
16. 0:37already seen how to join two different
17. 0:40tables. Specifically, we have been
18. 0:42working with TBL employees and TBL
19. 0:44departments tables. But have you ever
20. 0:47thought of a need to join a table with
21. 0:49itself? If you look at the table here
22. 0:51that we have, it has got three columns.
23. 0:54Employee ID, name, and manager ID.
24. 0:57Now let's say I want you to write a
25. 1:00query which gives me an output like
26. 1:02this. I want the employee name as well
27. 1:05as his manager's name. Okay. Now if you
28. 1:08look at the table on the left hand side,
29. 1:09if you look at Mike, Mike's manager ID
30. 1:13is three. And you take this three and
31. 1:16look up in the employee ID column. Okay,
32. 1:18three is Todd. So Mike's manager is
33. 1:21Todd. And along the same lines, if you
34. 1:24look at Rob, his manager ID is one. And
35. 1:27you take this one and look up in the
36. 1:29employee ID column, one is Mike. Okay?
37. 1:32So Rob's manager is Mike. And we get
38. 1:35that output here. So if you look at the
39. 1:38situation here, we are using this
40. 1:41manager ID column and looking up in the
41. 1:44employee ID column within the same
42. 1:46table, which means we are you know
43. 1:50referencing the same table. Okay. So
44. 1:54here if we want this kind of an output
45. 1:57like employee name and manager's name,
46. 1:59there is a need to join this employee
47. 2:02table with
48. 2:04itself. In parts 12 and 13, we have
49. 2:07joined TBL employees and tbl department
50. 2:09tables. But then in tbl employee table,
51. 2:12I had department ID column which I have
52. 2:16used to look up the ID column in tbl
53. 2:19department's table which is another
54. 2:21table. But here I'm using this manager
55. 2:24ID column to look up employee ID column
56. 2:28in the same table. So there is a need to
57. 2:31reference the same table to join with
58. 2:33the ta same table and that's nothing but
59. 2:36a self join. Now you might be wondering
60. 2:39how can we join the same table with
61. 2:42itself. Okay. So if you look at this
62. 2:45here this query right here you know we
63. 2:48have done exactly the same thing. Okay
64. 2:50select first of all whatever the we'll
65. 2:52talk about these columns just in a bit.
66. 2:55Okay. So if you look at the output we
67. 2:56want the employee name and the manager
68. 2:58name. So select employee name, manager
69. 3:00name. We'll come to those bits in a in
70. 3:03uh you know the column list in the
71. 3:05select clause in a bit. If you look at
72. 3:07the from clause, we are saying tbl
73. 3:09employee and we are giving it an a table
74. 3:12alias as e and we are saying okay join
75. 3:16this table left join. We are doing a
76. 3:18left join with tbl employee again. So if
77. 3:22you look at this you know forget about
78. 3:23the type of join here. It can be inner
79. 3:26join left join whatever. Okay. So,
80. 3:29select whatever columns you want from
81. 3:31tbl employee. Give it an alias e join
82. 3:35with the same table tbl employee and
83. 3:38give it another alias m e for employee m
84. 3:42for manager. So, you can assume now we
85. 3:45have two instances of this table you
86. 3:47know two copies of this table rather
87. 3:50employee table and then another table
88. 3:52like manager table. Okay. But both of
89. 3:56the structures will be identical. You
90. 3:58know, you're basically referencing the
91. 4:00same table. And then what you're doing
92. 4:03is in employee table, take the manager
93. 4:06ID column. Okay? In the employee table,
94. 4:10take the manager ID column and look up
95. 4:13the employee ID in the manager table,
96. 4:16which is nothing but another copy of the
97. 4:18same
98. 4:19table. Okay? And then from the employees
99. 4:22table which is nothing but E I want the
100. 4:25name which is nothing but the employee
101. 4:27name. So I'm giving it an an alias as
102. 4:30employee. And from the manager table I'm
103. 4:33getting the name which is nothing but
104. 4:34the manager name. Okay. So so that's how
105. 4:38we get this output. If you look at the
106. 4:41join here we are joining with the same
107. 4:43table. So this is nothing but a self
108. 4:45join. So let's write this in SQL server
109. 4:47management studio.
110. 4:49So we'll come to the column list in a
111. 4:52bit. Okay. Select from from which table?
112. 4:57From tbl
113. 4:59employee. Let's give it an alias as
114. 5:04E left
115. 5:08join
116. 5:10with the same table TBL employee. But
117. 5:13then I'm going to call this M for
118. 5:15manager. So this is an employee table.
119. 5:18This is the manager table. And how do we
120. 5:21want to join
121. 5:24that? Okay, in the employees table, I
122. 5:28want to take the manager ID column and
123. 5:32look that up in the manager
124. 5:36table in the employee ID column. So look
125. 5:39up employee ID column in manager table
126. 5:42using the manager ID column in employee
127. 5:44table. You are treating the same table
128. 5:46as two different tables now. which means
129. 5:48you are actually joining the same table
130. 5:50with itself. So what columns do you
131. 5:52want? Now from the employee table if I
132. 5:55pick up the name it will be an employee
133. 5:57name. So give it a meaningful name. So
134. 5:59as employee give it an alias and
135. 6:02similarly from manager table I want the
136. 6:06name but this is going to be the manager
137. 6:08name. So manager. So that's it. So when
138. 6:12we execute this
139. 6:14query, we get the output as we expect.
140. 6:18Okay. Now look at this. We are using a
141. 6:21left join here. And if you remember from
142. 6:23part 12 and 13 of this video series,
143. 6:25left join will give you all the matching
144. 6:27rows between these two tables plus
145. 6:30non-matching rows from the left
146. 6:32table. Okay? But if it's an inner join,
147. 6:36it will only give the matching rows
148. 6:38between the two tables. Which means look
149. 6:40at this. when we were using left join we
150. 6:43were getting Todd's record as well but
151. 6:45the moment I change it to inner join
152. 6:47okay see if you look at the output right
153. 6:49now we've got five rows including the
154. 6:51Todd's record now when I execute this
155. 6:54one look at what's going to happen I
156. 6:56only get four rows what happens to Todd
157. 6:59record it's not retrieved anymore why
158. 7:01because if you look at Todd's record the
159. 7:04manager ID does not match with any of
160. 7:07the employee IDs there is no matching
161. 7:08record and if you Remember in a join
162. 7:11will only give you the matching records
163. 7:12between the two tables. Now you might be
164. 7:14wondering where are two tables here but
165. 7:16remember in the join we are joining this
166. 7:19table with itself. So we are treating a
167. 7:21single table like two tables. We're
168. 7:23joining the same table with itself.
169. 7:25Okay. So when you are using this column
170. 7:27to look up in the employee ID column of
171. 7:29the same table if there are no matching
172. 7:32rows and you're doing an inner uh I mean
173. 7:34um the matching rows are not returned if
174. 7:37you do an inner self join. So you can
175. 7:40call this as an inner self join. Okay.
176. 7:43Why is it called a self join? Because
177. 7:45you're joining the same table with
178. 7:46itself and you're doing an inner join.
179. 7:48So inner self join. And along the same
180. 7:51lines you can call this query you know
181. 7:53you're joining if you look at the first
182. 7:56um picture here we are joining TBL
183. 7:58employee with manager but we are doing a
184. 8:00left join. So you can call this as left
185. 8:02outer self join. Okay. So along the same
186. 8:05lines you can do a right outer self join
187. 8:07and a full outer self join. So a self
188. 8:10join is not a different type of join
189. 8:13altogether. It can be classified under
190. 8:16any type of join inner outer any of the
191. 8:18outer joints left right or full and
192. 8:21cross join as well. We have seen how to
193. 8:23write a self you know left outer self
194. 8:25join inner self join but we haven't seen
195. 8:28how to write a cross self join. It's
196. 8:30pretty much identical. If you remember
197. 8:31from part 12 of this video series, a
198. 8:34cross join cannot have an on clause. So
199. 8:37a cross sol self join can
200. 8:40als it also shouldn't have the on
201. 8:43clause. So if you want to convert this
202. 8:46into a cross join, the first thing you
203. 8:48have to do get rid of the on clause and
204. 8:50then convert this inner join into cross
205. 8:54join. That's about it. Since we are
206. 8:56joining the same table with itself, we
207. 8:58call it you know a self join. But since
208. 9:01we are using cross join, it's called as
209. 9:03cross self join. Okay. Now remember in
210. 9:07the employee table we have got five
211. 9:09rows. And if you remember from the
212. 9:11definition of the cross join when you do
213. 9:13a cross join between two tables you get
214. 9:15the cartesian product which is nothing
215. 9:17but the number of rows in the first
216. 9:20table multiplied by the number of rows
217. 9:22in the second table. Okay. Now here it's
218. 9:26the same table. So in the the same table
219. 9:29you know we're joining employees table
220. 9:30with itself. In employee table we have
221. 9:32got five rows and in the manager table
222. 9:34obviously we have another five rows. So
223. 9:37when we execute this query we get five
224. 9:41multiplied by five which is 25 rows. So
225. 9:44when I press F5 look at that in the
226. 9:46output you should see there are 25 rows.
227. 9:49So keep in mind you know it's very
228. 9:51simple to understand joining a table
229. 9:54with itself is nothing but a self join
230. 9:56and self join is not a different type of
231. 9:59join altogether. Self join can be
232. 10:02classified under any type of joins like
233. 10:05inner outer cross join depending on how
234. 10:08you write your self join
235. 10:11query. Now if you look at the output
236. 10:14here, when we do a left
237. 10:20um when we do a left
238. 10:28join on employee dot manager ID is equal
239. 10:31to manager dot employee ID. Look at
240. 10:35this. When we execute this
241. 10:37query, we get Todd's record. You know
242. 10:39Todd doesn't have a manager. So the
243. 10:41manager name is null. Now instead of
244. 10:43showing null, you know, I want uh to
245. 10:46display since Todd doesn't have a
246. 10:48manager, maybe I want to display a text
247. 10:51here saying no manager or I want to say
248. 10:53super boss or super manager, whatever.
249. 10:55Instead of null, I want to show my own
250. 10:58string here. How do I do that? There are
251. 11:00several ways you can do it. Okay, which
252. 11:02we'll be talking about in our next
253. 11:04session.
254. 11:11On this slide, you can find resources
255. 11:12for ASP.NET and C# interview questions.
256. 11:15That's it for today. Thank you for
257. 11:16listening. Have a great day.