Advanced or intelligent joins in sql server - Part 13 — Transcript

Full transcript

1. 0:00hello welcome to preim Technologies I'm
2. 0:02wenet this is part 13 of SQL server in
3. 0:05this session we'll discuss about
4. 0:07Advanced our intelligent joints in SQL
5. 0:09Server as part of this we will see how
6. 0:12to retrieve only the non-matching rowes
7. 0:14from the left table and along the same
8. 0:16lines how to retrieve only the
9. 0:19non-matching rows from the right table
10. 0:20and finally we will see retrieving only
11. 0:24the non-matching rows from both the left
12. 0:26and right tables before continuing with
13. 0:29the session I strongly recommend
14. 0:31watching Part 12 of this video
15. 0:34series now in part 12 of this video
16. 0:36series we have seen how inner join left
17. 0:39join right join and full join works we
18. 0:42have seen examples of that if you
19. 0:44recollect from that part inner join
20. 0:47retrieves only the matching rows between
21. 0:49both the tables whereas left join
22. 0:51retrieves the matching rows plus
23. 0:53non-matching rows from the left table
24. 0:55and right join matching rows plus
25. 0:58non-matching rows from the right table
26. 1:01full outer join matching rows from both
27. 1:03the tables plus non-matching rows from
28. 1:06the left table as well as non-matching
29. 1:07rows from the right table now if you
30. 1:09look at all these joints you know the
31. 1:12queries underneath them they are pretty
32. 1:14much identical and the only difference
33. 1:16you find is the type of join rest of the
34. 1:19query you know is almost identical so if
35. 1:22you look at here it's inner join but
36. 1:25whereas in the left join you just say
37. 1:27it's a left join for right join right
38. 1:30join and full join you say full join
39. 1:31that's the only difference otherwise the
40. 1:33query is
41. 1:35identical all right now let's assume
42. 1:39okay so if you look at these four types
43. 1:41of joints here all the types of joints
44. 1:44you know they give you the matching rows
45. 1:48plus non-matching parts okay so but
46. 1:53let's say I want an output like this
47. 1:56okay I want only the non matching rows
48. 2:01from the left table and along the same
49. 2:03lines I want only the non-matching rows
50. 2:06from the right table and maybe finally I
51. 2:09want only the non-matching rows from the
52. 2:12left table and the non-matching rows
53. 2:13from the right
54. 2:15table okay so basically in this three
55. 2:19result sets we are we are actually
56. 2:22excluding the matching Parts in the
57. 2:24previous slide we see in all the four
58. 2:27types of joints we are getting the
59. 2:29matching rows plus something
60. 2:32extra okay but here no matching rows at
61. 2:36all non-matching rows from the only
62. 2:38non-matching rows from the left table
63. 2:40only non-matching rows from the right
64. 2:41table non-matching rows from the left
65. 2:44table and non-matching rows from the
66. 2:46right table and how do we do that you
67. 2:48know it's a it's a it's very easy to
68. 2:51understand this you know you write your
69. 2:52query if you want only the non-matching
70. 2:54rows from the left table you write the
71. 2:57query as you do it for left join and and
72. 2:59then you'll have a wear Clause okay
73. 3:02let's see how to do that now if you look
74. 3:03at these two tables here the it's the
75. 3:06same employees and departments table we
76. 3:07have been working with in part 12 of
77. 3:09this video series so if you look at this
78. 3:11employees table you know James and
79. 3:14Russell records have got Department IDs
80. 3:17now okay so this is our left table and
81. 3:20this is our right table now I want only
82. 3:23James and Russell's records which means
83. 3:26the non-matching rows in the left table
84. 3:29okay so how do we do that let's write a
85. 3:31query for that so when you write your
86. 3:34query the output should be like this
87. 3:36name gender salary Department name for
88. 3:39James and Russell there is no matching
89. 3:41GR in the Department's table that's why
90. 3:43Department name will be null okay but
91. 3:45basically I want only those non-matching
92. 3:48rows from the left table so let's see
93. 3:50how to write the query for that so
94. 3:52select these columns whatever you want
95. 3:55from what's the name of the table
96. 3:58TBL employee
97. 4:02and we want to left join with that
98. 4:05table and which table is that
99. 4:09TBL Department table and obviously join
100. 4:13condition
101. 4:14on TBL
102. 4:17employee. Department ID is equal to
103. 4:20departments
104. 4:22table TBL department. ID so that's our
105. 4:27joint condition so if you look at this
106. 4:29result I mean this query now it's a left
107. 4:32join query now if we execute this query
108. 4:35we get all the 10 rows from the left
109. 4:38table okay the matching rows plus the
110. 4:40non-matching rows so that's what is the
111. 4:43inner join query I mean left join query
112. 4:46select whatever columns we want from
113. 4:48employee left join department on you
114. 4:51know the join condition so we get the
115. 4:53matching rows as well as the
116. 4:54non-matching rows but what's our uh
117. 4:57Target our Target is basically to
118. 4:59retrieve only the non-matching rows from
119. 5:02the left table which means we want only
120. 5:05James and Russell's records which means
121. 5:08we have to filter out all these these
122. 5:11eight rows okay so obviously if we have
123. 5:15to filter we will use the wear Clause
124. 5:18okay and there are two ways to achieve
125. 5:20this one is look at this you can simply
126. 5:23say Okay I want all these rows but I
127. 5:28want to filter out okay where employee
128. 5:32table you know I basically want only the
129. 5:35rules which has got
130. 5:38Department ID as null so if you look at
131. 5:41this who has got Department ID as null
132. 5:45James and Russell in the employees table
133. 5:48so what you can basically do here is you
134. 5:50can say TBL
135. 5:52employee. Department ID is null so the
136. 5:56moment you do that what's going to
137. 5:58happen
138. 6:00when we press F5 we get all these rows
139. 6:04you know all this only the non-matching
140. 6:06rows from the left table another way to
141. 6:09do it is okay if you look at the
142. 6:11presentation you know another way is to
143. 6:13do it is department. ID is null so
144. 6:17Department
145. 6:21table
146. 6:26dot ID is now so when we execute this
147. 6:31query we get the same result and and
148. 6:33it's pretty simple to understand what
149. 6:35you're saying okay when you when you say
150. 6:39okay I want to do a left join what do we
151. 6:41get we get everything the matching rows
152. 6:44plus the non-matching rows from the left
153. 6:46table okay so but we want to filter out
154. 6:49James and Russell's record so as part of
155. 6:52that what is the condition we are using
156. 6:54we are saying department. ID is null so
157. 6:57basically for all these records
158. 6:59Department ID is not null hence they
159. 7:01will be filtered out only these two
160. 7:03records has got Department ID is null so
161. 7:06you know we don't have a match you know
162. 7:08as it is we get all the records from the
163. 7:10left table so these records who has a
164. 7:13department ID will be filtered
165. 7:15out so that's how we get only the
166. 7:19non-matching rows from the left table
167. 7:21now let's say I want you know if you
168. 7:24look at this one I want only the
169. 7:27non-matching rows from the right table
170. 7:29now if you remember how do we write a
171. 7:31right join instead of left join you just
172. 7:34say right join and when we do this what
173. 7:37do we get we get you know right join
174. 7:39will basically give us all the matching
175. 7:41rows between the two tables plus the
176. 7:44non-matching rows from the right table
177. 7:46so there are eight matching rows between
178. 7:48TBL employee and department so we get
179. 7:50those eight rows plus one additional
180. 7:53record you know this other department
181. 7:56record does not have you know no no
182. 7:58employee
183. 8:00is assigned that
184. 8:02department okay so so this is the
185. 8:05non-matching row in the right table okay
186. 8:09so we get this row as well as the
187. 8:11matching rows between these two tables
188. 8:13you know these eight if we are using a
189. 8:15right joint but what is Our intention
190. 8:18Our intention is basically to retrieve
191. 8:19only the non-matching rows from the
192. 8:21right table which means I just want this
193. 8:24other department row okay so how do we
194. 8:28do that all you need to do is look at
195. 8:30this we basically want to check okay
196. 8:33wherever the employee. department ID is
197. 8:37null okay so basically if you look at
198. 8:40this right join here what is it doing
199. 8:44it's giving you all the matching rows
200. 8:47plus the non-matching rows from the um
201. 8:50right table okay now when you when you
202. 8:54say where e employee. Department ID is
203. 8:58null for all of these Department ID is
204. 9:02is not null so obviously these rows will
205. 9:05be filtered out which means these
206. 9:07matching rows will also be filtered out
207. 9:09and you only be left with this other
208. 9:11department
209. 9:14row so when I
210. 9:17say TBL
211. 9:20employee. Department
212. 9:25ID is
213. 9:28null we only get that other department
214. 9:31so we only get the non-matching rowes
215. 9:33from the right table okay now let's say
216. 9:36I want the nonmatching rows from the
217. 9:38left table plus the non-matching rows
218. 9:40from the right table by now you would
219. 9:42have already guessed what to do okay you
220. 9:44will just use an or or condition okay so
221. 9:50in the we class we'll say TBL employee
222. 9:54um TBL employee. Department ID is null
223. 9:58or
224. 10:02TBL do Department do ID is
225. 10:06null so what's going to happen we are
226. 10:09going to
227. 10:10get only the non-matching rows um we're
228. 10:16saying okay I mean look at this when I
229. 10:19run this query I'm not getting the
230. 10:22non-matching rows from the left table
231. 10:25why because you are using a right join
232. 10:28so you will have to use a full join
233. 10:31there so what does full join give you
234. 10:33full join will give us all the matching
235. 10:34rows
236. 10:35between both the tables plus the
237. 10:38non-matching rows from the left table
238. 10:39plus the non-matching rows from the
239. 10:40right table so if we execute the query
240. 10:43until here we end up getting 11 rows
241. 10:47okay eight matching rows two
242. 10:49non-matching rows from the left table
243. 10:51and one non-matching row from the right
244. 10:53table but our Our intention is to filter
245. 10:57out these matching rows these eight
246. 10:59matching rows you we only want the
247. 11:01non-matching rows from the left table
248. 11:03and the non-matching row from the right
249. 11:05table so in order to do that we are
250. 11:07using this wear Clause so when we
251. 11:10execute this now we should only get the
252. 11:13non-matching rows from the left table as
253. 11:15well as from the right
254. 11:18table so we get these three
255. 11:22rows all right and one important thing
256. 11:24to keep in mind is never ever use you
257. 11:28know
258. 11:30equal to When comparing null value you
259. 11:32cannot say you know TBL employee.
260. 11:35Department ID is equal to null and along
261. 11:38the same lines here you cannot say you
262. 11:40cannot use equal to operator to compare
263. 11:43nulls in SQL Server you always have to
264. 11:45use e
265. 11:49keyword all
266. 11:52right on this slide you can find
267. 11:54resources for asp.net and C interview
268. 11:56questions that's it for today thank you
269. 11:58for listening have a great day