In this video
will try to understand
what exactly is the use of ISNULL function
and what is the use of
Coalesce function
Let me pronounce again coalesce
I am not sure that if that pronounciation is right but english is my second language
In case it is not right I am extremely sorry for that
In this video we will try to demonstrate ISNULL and Coalesce function
First start with ISNULL
In order to demonstrate the ISNULL function I have created a very simple table here
The table name is tblPerson and it has two columns first name and surname
and if you go and fire select query over this table here tblperson you can see that
it has some records here
and
Some of the records you know have null in surname
and some of the records have null in the first name
When I do a select here you can see that I am getting records for example you can see Prasad here
The first name is present but the surname is not there so it is null
then we have Raju again the surname is not there it is null
Then we have koirala here he does not have a first name
entered here
We would like to get rid of this null function in other words
this null data
I would like to fire a select query
where probably I would like to get name
If he has a First name given take the first name
If he does not have the first name
and if he has a surname then take the surname
That’s where we have this ISNULL function what I can do is I can say here
ISNULL
If this first name is null then take the surname
If the first name have some data then just written the
first name
If the first name is null he will return the surname
If the first name is not null he will just return the first name
We will give name to this column here as name
It's only one column here
Wherever there is first name he has repeat the first name
and wherever there is surname he has repeat the surname whichever column
is basically
having not null value he has repeat that column
ISNULL function actually
checks for nulls
and if that column values having nulls
he will return whatever you have given
in the other condition
In case if he does not have null he will actually return
the same value of the column
We have one more column here
There is one more column added here called as pet name
Just trying to complicate thing here now
If we do
a select here so I have here first name
then the surname
and then the pet name
Rather then two columns I have created now three columns
If you see the data here the data is bit complicated as compare to the first scenario
Some of the rows have only first name, some of the rows have only surname
and some of the rows have only pet name
Wherever you have
the non null columns
just return only that columns in other words
In the first rows I would like to just return Prasad
In the second row I would like just return Raju
In the third row and the fourth row I would like just return surname i.e koirala and shinde
and in the last row I would like to just return shivdya
If I try to use this ISNULL function here
and if I execute this
I get a error over here
This error says ISNULL function can take
only two arguments. In other words it can take either the first name and the surname or it can take either the surname and the pet name
We have the coalesce function the
The coalesce function actually retrieves
the first NonNull column value
from a row
If I type here coalesce
Wherever he find the first name he will give the first name
If he does not find the first name then he will try for the surname
If the surname has values it will return the surname
or I will say you know in case first name and surname do not have the values it will return the
pet name
If I try to do a execute here
He has return five records
and whichever columns have the value those records have been return
Just to cross check if I do a selects * from
tblperson
over here this down window over here this data which is seen in the down window pane is data
as it is from the table
and this one is the data which is return from the coalesce function
Let's start from the first records you can see Prasad here
and the surname and pet name is null so he has just taken Prasad
The second record Raju
Because Raju is in the first name and the surnames are null he has taken Raju from it
Look at the third record
Third record has first name null and the pet name null
and it has
name only
in the surname so koirala is returned
and look at the last record
the last record we have no values in the first name and surname
He has just taken the pet name and displayed over here
Coalesce helps you to return
the first
NonNull column
from multiple columns. Multiple columns means
for example we have first name, surname and pet name
from all those multiple columns it helps us to return
the first NonNull column
I hope you enjoyed this video. In this video we will trying to understand
what is the use of ISNULL function
and we also saw the advance function of ISNULL which is called as the coalesce function
Thank you so much