SQL Server interview question :- What is use of Coalesce and ISNULL ( SQL Server training)?

ArticlesBlog

Written by:


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

25 Replies to “SQL Server interview question :- What is use of Coalesce and ISNULL ( SQL Server training)?”

  1. Puurusottam Saha says:

    nice!

  2. Abhinav Kumar says:

    very good

  3. deepu duvedi says:

    impresive

  4. vibhakar singh says:

    Thats what i wanted…
    thnx,

  5. esayas ayele says:

    Thanks my brother
    Very informative I got it

  6. Aamir Shahzad says:

    Good Job!
    http://sqlage.blogspot.com/search/label/SQL%20SERVER%20DBA%20INTERVIEW%20QUESTIONS

  7. wonkydonki says:

    awesome thank you. Nicely explained.

  8. Rishi Saw says:

    very impressive 

  9. Rishi Saw says:

    pls share us more video

  10. Sagar Shah says:

    Thanks

  11. Dimitrios Papageorgiou says:

    Excellent work sir 🙂 Thank you for your help!!

  12. Kiran survase says:

    Thansks Friend..
    Nice Explanation….

  13. singata tyalimpi says:

    nice work sir… made it so easy

  14. Shruti Challani says:

    Very informative and made easy.

  15. Ram Chougale says:

    very nice sir

  16. Jay Khan says:

    fantastic sir G 🙂

  17. Steve Flanigan says:

    Informative and helpful, thanks! As far as your English goes, please don't apologize as it is perfectly fine!! I can't tell you how many Americans, I am being one, that haven't mastered their native language of English let alone even being able to speak a second language. Again, great video.

  18. Ankur Lodha says:

    it's co-lace. Your tutorials are good quick refresher. Thanks!

  19. Yeshitila Yigzaw says:

    Do not worry about English you are best

  20. YANIV SODAI says:

    THANKS very helpful movie very eazy to understans

    but another question i didnt understand what is the "IFNULL" is ? there is a diffarnce between the " IFNULL, ISNULL" ?

  21. Frank Martin says:

    Very educational but annoying with all the smacking

  22. Anjuri Gupta says:

    thanks a lot, very nice teaching

  23. Manjunath Badiger says:

    Nice
    Thanks

  24. A K Shiwakoti says:

    Best explanation and clearest illustration of the use of COALESCE and ISNULL. Thank you!

Leave a Reply

Your email address will not be published. Required fields are marked *