Coalesce function in sql server Part 16 — Transcript

Full transcript

1. 0:00hello welcome to pre Technologies I'm
2. 0:02wut this is part 16 of SQL server in
3. 0:05this session we'll understand how to use
4. 0:08coess function with an example now
5. 0:11according to the msdn books online Coes
6. 0:13function Returns the first nonnull value
7. 0:17let's understand what we mean by this
8. 0:19now if you look at this employee table
9. 0:20here we've got ID first name middle name
10. 0:23and last name columns and if you look at
11. 0:25these employees some of them have just
12. 0:28their first name some of them have their
13. 0:30middle and last name and some of the
14. 0:32employees have just their last name now
15. 0:34we want to write a query which pulls out
16. 0:38the ID and the name of the person as you
17. 0:41can see on the slide now when you pull
18. 0:43out an when you pull out the name okay
19. 0:47if the employee has got the first name
20. 0:49you pull that out okay but whereas if an
21. 0:53employee does not have a first name and
22. 0:55if it has middle and last name then we
23. 0:58want to pull their middle name okay and
24. 1:01if an employee doesn't have first name
25. 1:03and middle name and if he has only the
26. 1:06last name then we want to pull their
27. 1:08last name okay but whereas if an
28. 1:10employee has both first and middle name
29. 1:14then we just want their first
30. 1:16name similarly if he has all of their
31. 1:19names you know first middle and last
32. 1:21names then still we we just want their
33. 1:23first name so the priority should first
34. 1:25go to their first name if that's present
35. 1:28pull it out if not then and middle name
36. 1:31if first and middle name both of them
37. 1:33are not available only then we pull the
38. 1:35last name okay so that's how our output
39. 1:38has to be all right so in order to do
40. 1:41that we we can use the coess function
41. 1:43now if you look at the query here to the
42. 1:46coess function you're passing in look at
43. 1:49this in the output we want the ID and
44. 1:50the name of the person so obviously in
45. 1:52the select list we'll have ID and then
46. 1:54to pull out the name we are using the
47. 1:56coas function we are not saying first
48. 1:58name middle name last name dire we are
49. 2:00passing those column names into this
50. 2:02coas function look at the definition of
51. 2:04the coas function it Returns the first
52. 2:07non-null value so what's going to happen
53. 2:09when you execute this query the coess
54. 2:12function will check okay in this row is
55. 2:15the first name available yes the first
56. 2:17name available so first name is not null
57. 2:21so it will immediately return Sam okay
58. 2:24but whereas when it comes to the second
59. 2:25row it checks okay is the first name
60. 2:28available no it's null so what happens
61. 2:31it checks the middle name okay is middle
62. 2:33name present yes then what is that you
63. 2:36know it's not null it's present so it's
64. 2:38going to return that middle name which
65. 2:40is Todd similarly it comes to the third
66. 2:43row it checks okay is first name null
67. 2:45yes it is null so it goes to the middle
68. 2:47name okay is middle name null yes it is
69. 2:49still null so it goes to last name okay
70. 2:53and then return Sara so we get that but
71. 2:55come here to the fourth row when it
72. 2:57comes here the first name itself is
73. 2:59present it doesn't even bother to check
74. 3:01the middle name okay because Coles
75. 3:03function Returns the first non-null
76. 3:06value okay so col is first name middle
77. 3:09name last name and we are giving it an
78. 3:11alias name from TBL employee okay let's
79. 3:14quickly write this query and see the
80. 3:16result in
81. 3:17action so if you look at this we exactly
82. 3:20have the same table here and we want to
83. 3:22select their ID so select ID and what
84. 3:26else we want we want the name of the
85. 3:28employee
86. 3:31okay but we want to use the coas
87. 3:34function so coas what do we want we want
88. 3:37to pass into this function first
89. 3:42name the middle name and last
90. 3:46name so when we pass these three columns
91. 3:49whichever column Returns the first
92. 3:51nonnull value that value will be used in
93. 3:54the output and we want to give this
94. 3:56column an alas let's call it name and
95. 3:59from which table are we trying to
96. 4:01retrieve this from the employee table so
97. 4:05TBL employee so let's execute
98. 4:09that okay so if you look at this we get
99. 4:12the output that we
100. 4:18expected on this slide you can find
101. 4:20resources for asp.net and cop interview
102. 4:23questions that's it for today thank you
103. 4:25for listening have a great day