Different ways to replace NULL in sql server - Part 15

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

Channel: kudvenkat

Views: 407,761

Rating: 4.9243855 out of 5

Keywords: replace NULL values, ISNULL function, Case Statement, Coalesce function, left outer join replace null values, left join null values, left join null values sql server, left join and null values, sql isnull function, sql case check if null, left join null coalesce, left join case null, left join null check

Id: 4ZoHY4RT1Fo

Length: 11min 42sec (702 seconds)

Published: Sun Aug 19 2012