Different ways to replace NULL in sql server - Part 15 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

Oh welcome to presume technologies I am
venket this is part 15 signal server in
this session we'll learn about the
different ways that are available to
replace null values in sequel server
specifically we'll be looking at is null
function case statement and colace
function please watch part 14 of this
video series before continuing for this
session now I have an employee table
which has got three columns with a net
employee ID name and manager ID now if I
ask you who is the manager of Rob you
look at manager ID column okay manager
ID for Rob is one and you look up one
within the employee ID column okay one
is Mike so Rob's manager is Mike so
along the same lines if you look todd
todd manager ID value is null which
means Todd doesn't have a manager and if
you remember in part 14 of this video
series we have written a left outer self
join to retrieve employee names along
with their manager names okay and this
is the output that we have since Todd
doesn't have a manager ID his manager
name is null which makes sense but then
in the output I don't want nulls to be
displayed instead of nulls you know if
an employee doesn't have a manager
instead of displaying null value I want
to replace that with no manager okay so
instead of an output like this I want an
output like this any employee if he
doesn't have a manager don't display
nulls replace those null values with
this no manager word is that possible
absolutely we can we actually have
different ways to do that we can make
use of ease now case statement and
coalesce functions let us look at
practical examples of using each one of
them all right so since we are using
this manager ID column and then looking
up in the employee ID column we are kind
of doing a self join here and if you're
not sure about what a self join is
please watch part 14 of this video
series okay so let's write the left
outer self join
in query so select okay we'll come to
the columns list in a bit
first let's specify the from clause from
let's say TBL employee table we'll call
this as e and I want to do a left to
join and join with the same table TBL
employee but I'm going to ally s that
with letter M for manager so this is
employee table this is the manager table
so from the employee table what do we
want if you look at the output I want
the employee name so in the employ from
the employee table
I want the employee name and let's give
it a meaningful name so employee and
from the manager table I want again the
name but this is going to be manager
okay so and we have to specify so we
specify the column list we specified the
employee table we specified the manager
table we have to specify the joint
condition so on a dot in the employee
table take the manager ID and look up in
the manager table the employee ID column
and we have seen I mean we have
discussed about this query in a great
detail in part 14 of this video series
so now when you execute this query we
get the output
you know the employee names with their
manager names but then as we you know
like how we have it here Todd doesn't
have a manager so he has null there so
similarly in the output here null is
displayed for Todd but what's our you
know ultimate aim our ultimate aim is
basically to replace that null value
with no manager word so how do we do
that the fast and simple I mean the
easiest way to do it is basically to use
is null function okay let's see how to
use is null function now is null is such
a simple function now as the name itself
suggests is null if it is now okay is
now so if you look at this function you
know the intelligence of sequel server
2008 it shows up the first parameter
it's an expression that you pass in here
if the expression that you are passing
in here if it returns null then there is
a second parameter called replacement
value that parameter will be used so for
example let's pass a null here for the
expression and replacement value I am
saying let's say no manager okay so when
I execute this what's going to happen
look at this since this function is we
are passing in null as the first
parameter since this is now it is making
use of the replacement value and look at
this the column doesn't have a name so
let's give it a meaningful name let's
call this manager for example so when I
execute that look at that you know since
we are passing null to use null function
it is making use of the replacement
value on the other hand let's say
instead of saying now I'm saying presume
here okay so I'm not passing null now so
when you're not passing null and if it
is not null then that value will be used
so now instead of saying no manager it
will use that word Pragya so whatever
expression that you pass in here if it
returns null then this replacement value
will be used if it doesn't return null
then that value itself will be used so
what we have to do is we have to use
this ease null function and pass in this
m dot name into this is null function
when this expression returns a manager
name it will be used but on the other
hand foot toward it returns now so when
it returns null it uses this no manager
and you know we will have no manager
displayed so let's see how to do that
you can simply copy the implementation
and paste it here okay now we don't want
this hard-coded value instead we want to
pass m dot name so if this name is now
then no mandate use no manager word
otherwise use the value returned by m
dot name so when we execute this okay so
todd has no manager pretty simple very
easy to understand along the same lines
you can actually use
koulos function as well so another way
to replace null value is to use colas
function and if we look up the
definition of colas function and MSDN
books online it says it is used to
replace I mean it it returns the first
non null value what is what do we mean
by that don't worry about that so the
time being we're going to talk about
colas function in a way very great
detail in the next session but for now
understand that you know the usage of
coulis function is pretty much similar
to ease null function but there is much
more to it okay it is basically used to
return the first non null value very
powerful function we'll be talking about
that piece in the next session okay but
what you can do is you know Coley's
function implementation I mean using
coalesce functions it's a little similar
to East now so who lays and the way I
followed to remember then you know the
spelling of this function that's a
little tricky cool is you know it's not
easy to remember the spelling but the
trick I followed to remember that is I
spell it like coal escape e ok that's
how I remember that just in case if you
want to use that trick to remember the
spelling of the function cool 0al escape
e ok all right
cool ace and look at this when I execute
this I get now pledging ok it's exactly
similar to the way we use seasonal
function now on the other hand if I pass
null for that when I press f5 look at
that I get no manager but then coulis is
much more powerful than X now you can
pass in multiple expressions to that and
it returns the first non null expression
value which we'll be talking about in
the next session in a great detail but
for now just understand cool is usage is
much similar to ease null function ok so
if you want to use colace all you have
to do is instead of ismail just specify
colace cool escape e and when I execute
this obviously Todd and
of displaying now we have no manager and
the final way to do it is is to use case
statement okay now you have this case
statement is also very simple basically
case I mean the way you use it as case
you specify case keyword and then you
specify an expression here if that
expression case when you specify your
expression if that expression returns
true then what value you want else what
value you want and then you end the case
statement so this is the syntax for the
case statement case when you specify the
expression this expression returns a
true or false okay if that expression
returns true then use the value here on
the other hand if it returns false then
use the value in the else part and this
is the end for case statement so with in
case you can have as many expressions as
you want okay but here we don't we only
have one expression okay so instead of
using colace here what you basically can
do is you can also use the case
statement so case when m dot name okay
so when m dot name is now if that is
null then I want to use no manager else
I want to use m dot name itself whatever
m dot name if it is not now then I want
to use the name that is returned by m
dot name and then you can end the case
expression that's that and then you are
saying whatever value this case
statement returns you know that should
be the value for the manager column so
this is the alias for that column so
case when m dot name is null then no
manager else
you know MDOT name and all right let's
execute this and see the output
okay look at that the output is exactly
similar so you can either use case
statement is null or colace function
we'll be talking about colas function in
a great detail in the next session and
you can see the implementation right
here for three of them on this slide you
can find resources for asp.net and C
sharp interview questions that's it for
today thank you for listening have a
great day