Different ways to replace NULL in sql server - Part 15 — Transcript

Full transcript

1. 0:00hello welcome to preim Technologies I am
2. 0:02wenet this is part 15 of SQL server in
3. 0:06this session we'll learn about the
4. 0:07different ways that are available to
5. 0:09replace null values in SQL Server
6. 0:12specifically we'll be looking at ISL
7. 0:14function case statement and coas
8. 0:18function please watch part 14 of this
9. 0:20video series before continuing with this
10. 0:23session now I have an employee table
11. 0:26which has got three columns within it
12. 0:28employee ID name and manager ID now if I
13. 0:33ask you who is the manager of Rob you
14. 0:35look at manager ID column okay manager
15. 0:38ID for Rob is one and you look up one
16. 0:42within the employee ID column okay one
17. 0:44is Mike so Rob's manager is Mike so
18. 0:47along the same lines if you look Todd
19. 0:49Todd manager ID value is null which
20. 0:51means Todd doesn't have a manager and if
21. 0:54you remember in part 14 of this video
22. 0:56series we have written a left outer self
23. 1:00join to retrieve employee names along
24. 1:02with their manager names okay and this
25. 1:05is the output that we have since Todd
26. 1:08doesn't have a manager ID his manager
27. 1:10name is null which makes sense but then
28. 1:13in the output I don't want nulls to be
29. 1:16displayed instead of nulls you know if
30. 1:19an employee doesn't have a manager
31. 1:21instead of displaying null value I want
32. 1:23to replace that with no manager okay so
33. 1:27instead of an output like this I want an
34. 1:30output like this any employee if he
35. 1:32doesn't have a manager don't display
36. 1:34nulls replace those null values with
37. 1:37this no manager word is that possible
38. 1:41absolutely we can we we actually have
39. 1:44different ways to do that we can make
40. 1:45use of e now case statement and coess
41. 1:48functions let us look at practical
42. 1:50examples of using each one of
43. 1:54them all right so since we are we are
44. 1:58using this manager idid column and then
45. 2:00looking up in the employee ID column
46. 2:03we're kind of doing a self jooin here
47. 2:05and if you're not sure about what a self
48. 2:06jooin is please watch part 14 of this
49. 2:09video series okay so let's write the
50. 2:12left outer self jooin query so select
51. 2:15okay we'll come to the columns list in a
52. 2:17bit first let's specify the from Clause
53. 2:21from let's say TBL employee table we'll
54. 2:25call this as e and I want to do a left
55. 2:28join
56. 2:31and join with the same
57. 2:33table TBL employe but I'm going to Alas
58. 2:37that with letter M for manager so this
59. 2:40is employee table this is the manager
60. 2:43table so from the employee table what do
61. 2:46we want if you look at the output I want
62. 2:47the employee name so in the employ from
63. 2:50the employee table I want the employee
64. 2:52name and let's give it a meaningful name
65. 2:55so
66. 2:57employee and from the manager table I
67. 3:00want again the name but this is going to
68. 3:03be
69. 3:05manager okay so and we have to specify
70. 3:09so we specified the column list we
71. 3:11specified the employee table we
72. 3:13specified the manager table we have to
73. 3:15specifi the join condition so on E do in
74. 3:20the employee table take the manager ID
75. 3:23and look up in the manager table the
76. 3:26employee ID column and we have seen I
77. 3:29mean we have discussed about this query
78. 3:31in a great detail in part 14 of this
79. 3:33video series so now when you execute
80. 3:35this query we get the output you know
81. 3:38the employee names with the manager
82. 3:40names but then as we you
83. 3:42know like how we have it here Todd
84. 3:45doesn't have a manager so he has null
85. 3:48there so similarly in the output here
86. 3:50null is displayed for Todd but what's
87. 3:53our you know ultimate aim our ultimate
88. 3:55aim is basically to replace that null
89. 3:57value with no manager word so how do we
90. 4:00do that the first and simp I mean the
91. 4:02easiest way to do it is basically to use
92. 4:05eal function okay let's see how to use e
93. 4:08Nal function now eull is such a simple
94. 4:10function now as the name itself suggest
95. 4:13is null if it is null okay is
96. 4:18null so if you look at this function you
97. 4:20know the intelligence of SQL Server 2008
98. 4:23it shows up the first parameter it's an
99. 4:26expression that you pass in here if the
100. 4:28expression that you are passing in here
101. 4:30if it returns null then there is a
102. 4:33second parameter called replacement
103. 4:35value that parameter will be used so for
104. 4:38example let's pass a null here for the
105. 4:40expression and replacement value I'm
106. 4:43saying let's say no
107. 4:48manager okay so when I execute this
108. 4:51what's going to happen look at this
109. 4:54since this function is we are passing in
110. 4:56null as the first parameter since this
111. 4:58is null is making use of the replacement
112. 5:01value and look at this the column
113. 5:03doesn't have a name so let's give it a
114. 5:04meaningful name let's call this manager
115. 5:07for example so when I execute that look
116. 5:09at that you know since we are passing
117. 5:11null to e null function it is making use
118. 5:14of the replacement value on the other
119. 5:16hand let's say instead of saying null
120. 5:18I'm saying praim here okay so I'm not
121. 5:21passing null now so when you're not
122. 5:23passing null and if it is not null then
123. 5:26that value will be used so now instead
124. 5:28of saying no man
125. 5:30it will use that word praim so whatever
126. 5:33expression that you pass in here if it
127. 5:35returns null then this replacement value
128. 5:38will be used if it doesn't return null
129. 5:41then that value itself will be used so
130. 5:44what we have to do is we have to use
131. 5:45this e null function and pass in this m.
132. 5:49name into this e null function when this
133. 5:53expression returns a manager name it
134. 5:55will be used but on the other hand for
135. 5:57Todd it returns null so when it returns
136. 6:00null it uses this no
137. 6:02manager and you know we will have no
138. 6:05manager displayed so let's see how to do
139. 6:07that you can simply copy the
140. 6:12implementation and paste it here okay
141. 6:15now we don't want this hardcoded value
142. 6:18instead we want to pass M do name so if
143. 6:22this name is null then no man use no
144. 6:24manager word otherwise use the value
145. 6:27returned by m. name so when we execute
146. 6:32this okay so Todd has no manager pretty
147. 6:35simple very easy to understand along the
148. 6:38same lines you can actually use coess
149. 6:41function as well so another way to
150. 6:42replace null value is to use coess
151. 6:45function and if you look up the
152. 6:47definition of coess function in msdn
153. 6:50books online it says it is used to
154. 6:52replace I mean it it Returns the first
155. 6:56non-null value what does what do we mean
156. 6:59by that don't worry about that for the
157. 7:00time being we're going to talk about Co
158. 7:02is function in a very very great detail
159. 7:04in the next session but for now
160. 7:06understand that you know the usage of
161. 7:08coess function is pretty much similar to
162. 7:11eal function but there is much more to
163. 7:13it okay it is basically used to return
164. 7:17the first non-null value very powerful
165. 7:20function we'll be talking about that
166. 7:22piece in the next session okay but what
167. 7:24you can do is you know coess function
168. 7:27implementation I mean using coess
169. 7:29function is is a little similar to e
170. 7:32now so Co
171. 7:36a and the way I follow to remember the
172. 7:40you know the spelling of this function
173. 7:41that's a little tricky cool a uh you
174. 7:44know it's not easy to remember the
175. 7:46spelling but the trick I follow to
176. 7:48remember that is uh I spell it like
177. 7:51coal Escape e okay that's how I remember
178. 7:55that just in case if you want to use
179. 7:57that trick to remember the of the
180. 7:59function Co C Escape e okay all right Co
181. 8:04is and look at this when I execute this
182. 8:07I get now
183. 8:09praim okay it's exactly similar to the
184. 8:11way we use eal function now on the other
185. 8:14hand if I pass null for
186. 8:19that and I press F5 look at that I get
187. 8:22no manager but then colis is much more
188. 8:24powerful than is n you can pass in
189. 8:27multiple Expressions to that and it
190. 8:29Returns the first non-null expression
191. 8:32value which we'll be talking about in
192. 8:34the next session in a great detail but
193. 8:36for now just understand cois usage is
194. 8:38much similar to eal function okay so if
195. 8:42you want to use colas all you have to do
196. 8:45is instead of eal just specify col a CO
197. 8:48Escape e and when I execute this
198. 8:52obviously Tod instead of displaying null
199. 8:55we have no manager and the final way to
200. 8:58do it is is to use case statement okay
201. 9:01now you have this case statement is also
202. 9:04very simple uh basically case I mean the
203. 9:09way you use it is case you specify case
204. 9:12keyword and then you
205. 9:14specify an expression here if that
206. 9:17expression case when you specify your
207. 9:21expression if that expression returns
208. 9:25true then what value you want else
209. 9:31what value you
210. 9:33want and then you end the case statement
211. 9:36so this is the Syntax for the case
212. 9:38statement case when you specify the
213. 9:41expression this expression returns a
214. 9:43true or a false okay if that expression
215. 9:47returns true then use the value here on
216. 9:51the other hand if it returns false then
217. 9:53use the value in the else part and this
218. 9:55is the end for case statement so within
219. 9:58case you can have as many Expressions as
220. 10:01you want okay but here we don't we we
221. 10:04only have one expression okay so instead
222. 10:08of using Coles here what you basically
223. 10:11can do is you can also use the case
224. 10:13statement so
225. 10:16case
226. 10:19when m. name okay so when m. name is
227. 10:25null if that is
228. 10:27null then I want to use no
229. 10:33manager
230. 10:36else I want to use m. name itself
231. 10:40whatever m. name if it is not null then
232. 10:43I want to use the name that is returned
233. 10:45by m. name and then you can end the case
234. 10:48expression that's
235. 10:51it and then you're saying whatever value
236. 10:54this case statement returns you know
237. 10:56that should be the value for the manager
238. 10:59column so this is the alas for that
239. 11:01column so case when m. name is null then
240. 11:05no manager else you know m. name
241. 11:12and all right let's execute this and see
242. 11:15the
243. 11:17output okay look at that the output is
244. 11:20exactly similar so you can either use
245. 11:22case statement is null or coess function
246. 11:24we'll be talking about coess function in
247. 11:26a great detail in the next session and
248. 11:29you can see the implementation right
249. 11:31here for three of
250. 11:33them on this slide you can find
251. 11:35resources for asp.net and cop interview
252. 11:37questions that's it for today thank you
253. 11:39for listening have a great day