Support us .Net Basics C# SQL ASP.NET Aarvi MVC Slides C# Programs Subscribe Download

Different ways to replace NULL in sql server - Part 15

In this video session, we will learn about different ways to replace NULL values in SQL Server. Please watch Part 14, before continuing.






Consider the Employees table below. 


In Part 14, we have learnt writing a LEFT OUTER SELF JOIN query, which produced the following output.



In the output, MANAGER column, for Todd's rows is NULL. I want to replace the NULL value, with 'No Manager'

Replacing NULL value using ISNULL() function: We are passing 2 parameters to IsNULL() function. If M.Name returns NULL, then 'No Manager' string is used as the replacement value.
SELECT E.Name as Employee, ISNULL(M.Name,'No Manager') as Manager
FROM tblEmployee E
LEFT JOIN tblEmployee M
ON E.ManagerID = M.EmployeeID

Replacing NULL value using CASE Statement:
SELECT E.Name as Employee, CASE WHEN M.Name IS NULL THEN 'No Manager' 
ELSE M.Name END as Manager
FROM tblEmployee E
LEFT JOIN tblEmployee M
ON E.ManagerID = M.EmployeeID

Replacing NULL value using COALESCE() function: COALESCE() function, returns the first NON NULL value.
SELECT E.Name as Employee, COALESCE(M.Name, 'No Manager') as Manager
FROM tblEmployee E
LEFT JOIN tblEmployee M
ON E.ManagerID = M.EmployeeID

We will discuss about COALESCE() function in detail, in the next session

10 comments:

  1. This is an excellent tutorial. I did not understand COALESCE() function much, however, I wish that I will get to know that in future videos as you mentioned. Thank you much, Sir!!!

    ReplyDelete
  2. Venkat you are doing a great job its really amazing

    ReplyDelete
  3. Thanks for your effort to explain simply but understandably

    ReplyDelete
  4. Thanks For All The Examples These are very helpfull for me

    ReplyDelete
  5. Thank you so much Vekant , videos are very helpful to understand basic..
    Keep it up great !!!

    ReplyDelete
  6. Thank you So much Venkat,For ur effort .It gives a life to so many people
    like Me.

    ReplyDelete
  7. Hi Venkat,

    Im unable to solve this. Can you please help-

    Write a query for the table below to get the output result ?

    Input-

    Id, val1, val2, val3
    1, null, null, hello
    1, world, null, null
    1, null, spark, null
    2, good, morning, spark

    Output-

    Id, val1, val2, val3
    1, world, spark, hello
    2, good, morning, spark

    ReplyDelete
    Replies
    1. with x as (select * from table_Name)


      select distinct x.id,
      STUFF((SELECT ', ' + val1
      FROM x x2
      WHERE x2.id = x.id
      FOR XML PATH ('')) , 1, 2, '') as val1,

      STUFF((SELECT ', ' + val2
      FROM x x2
      WHERE x2.id = x.id
      FOR XML PATH ('')) , 1, 2, '') as val2,

      STUFF((SELECT ', ' + val3
      FROM x x2
      WHERE x2.id = x.id
      FOR XML PATH ('')) , 1, 2, '') as val3

      from (SELECT DISTINCT id, val1,val2,val3 FROM x) x;

      Delete
  8. Create Table EMP
    ( EmployeeID INT Primary key,
    Name nvarchar(50),
    ManagerId nvarchar(50)
    )

    Insert Into EMP Values ( 1, 'Mike',3)
    Insert into EMP Values (2, 'Rob',1)
    Insert Into EMP Values ( 3, 'Todd',NULL)
    Insert Into EMP Values (4,'Ben',1)
    Insert Into EMP Values (5,'Sam',1)

    Create Table Manager
    (
    Employee nvarchar(30),
    Manager nvarchar(30)
    )

    Insert into Manager Values ( 'Mike','Todd')
    Insert into Manager Values ( 'Rob','Mike')
    Insert into Manager Values ( 'Todd',NULL)
    Insert into Manager Values ( 'Ben','Mike')
    Insert into Manager Values ( 'Sam','Mike')

    Select * From EMP
    Select * From Manager

    ReplyDelete

It would be great if you can help share these free resources