What are the different ways to replace NULL values in SQL Server

This interview question is not that common. My friend faced this interview question, when he attended an interview in London. My friend said we can use  COALESCE() in SQL Server. Then the interviewer said, that's very good answer, but do you know of any other way?


Apart from using COALESCE(), there are 2 other ways to replace NULL values in SQL Server. Let's understand this with an example.


I have a Table tblEmployee, as shown in the diagram below. Some of the Employees does not have gender. All those employees who does not have Gender, must have a replacement value of 'No Gender' in your query result. Let's explore all the 3 possible options we have.



Option 1 : Replace NULL values in SQL Server using ISNULL() function.

Select Name, ISNULL(Gender,'No Gender') as Gender
From tblEmployee



Option 2 : Replace NULL values in SQL Server using CASE.

Select Name, Case  When Gender IS NULL Then 'No Gender' Else Gender End as Gender
From tblEmployee



Option 3 : Replace NULL values in SQL Server using COALESCE() function.

Select Name, Coalesce(Gender, 'No Gender') as Gender
From tblEmployee



These are the 3 options that I can think of at the moment. If you can think of any other option, please post it here.

8 comments:

  1. Using the ISNULL function ie

    Select Name, ISNULL(Gender,'No Gender') as Gender
    From tblEmployee

    only the first letter is updated ie it has to display as "No Gender" but displaying as N

    ReplyDelete
    Replies
    1. Please check your 'Gender' column data type. make sure your column length is greater than or equal to 9.

      Delete
    2. select iif(gender is null,'no gender',gender) from sample

      Delete
  2. what is the difference between coalesce and isnull?

    ReplyDelete
    Replies
    1. Coalesce is use for more than two columns where as Isnull works only for two column. Also you can use coalesce function in joining to get first not null value from more than two different table.

      Delete
    2. the major difference is coalesce replaces the null value with the first not null value among the arguments whereas isnull replaces the null value with specified replacement value

      Delete
  3. Some how iif() and nullif also try to do something

    ReplyDelete

If you are aware of any other sql server questions asked in an interview, please post them below. If you find anything missing or wrong, please feel free to correct by submitting the form below.

 
Disclaimer - Terms of use - Contact Us