Coalesce function in sql server Part 16 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

hello welcome to pregame technologies I
am venket this is part 16 of signal
server in this session we'll understand
how to use coulis function with an
example now according to the MSDN books
online Coley's function returns the
first non null value let's understand
what we mean by this now if you look at
this employee table here we've got ID
first name middle name and last name
columns and look at these employees some
of them have just their first name some
of them have their middle and last name
and some of the employees have just
their last name now we want to write a
query which pulls out the ID and the
name of the person as you can see on the
slide now when you pull out in when you
pull out the name ok if the employee has
caught the first name you pull that out
ok but whereas if an employee does not
have a first name and if he has middle
and last name then we want to pull their
middle name ok and if an employee
doesn't have first name and middle name
and if he has only the last name then we
want to pull their last name ok but
whereas if an employee has both first
and middle name then we just won their
first name similarly if he has all of
their names you know first middle and
last names then still we just want their
first name so the priority should first
go to their first name if that's present
pull it out
if not then middle name if first and
middle name both of them are not
available only then we pull the last
name ok so that's how our output has to
be all right so in order to do that
pee-yew we can use the coalesce function
now if you look at the query here to the
colas function you're passing in look at
this in the output we want the ID and
the name of the person so obviously in
the Select list we'll have ID and then
to pull out the name we are using the
coolest function we are not saying first
name middle name last name directly we
are passing those column names into this
coalesce function look at the definition
of the coolest function it returns the
first normal value so what's going to
happen when you execute this query this
college function will
okay in this row is the first name
available yes the first name available
so first name is not null so it will
immediately return Sam okay but whereas
when it comes to the second row it
checks okay is the first name available
no it's null so what happens it checks
the middle name okay is middle name
present yes then what is that you know
it's not now it's present so it's going
to return that middle name which is Todd
similarly it comes to the third row it
checks okay is first name now yes it is
not so it goes to the middle name okay
it's middle name null yes it is still
now so it goes to last name okay and
then returns Sara so we get that but
come here to the fourth row when it
comes here the first name itself is
present it doesn't even bother to check
the middle name okay because college
function returns the first non null
value okay so cool a is first name
middle name last name and we are giving
it an alias name from TBL employee okay
let's quickly write this query and see
the result in action so if you look at
this we exactly have the same table here
and we want to select their ID so select
ID and what else we want we want the
name of the employee okay but we want to
use the coolest function so colace what
do we want we want to pass into this
function first name the middle name and
last name so when we pass these three
columns
whichever column returns the first
normal value that value will be used in
the output and we want to give this
column and aleeah's let's call it name
and from which table are we trying to
retrieve this from the employee table so
TBL employee so let's execute that okay
so if you look at this we get the output
that we expected
on this slide you can find resources for
asp.net and C shape interview questions
that's it for today thank you for
listening have a great day