Different ways to replace NULL Video Tutorial - SQL ~ NIIT POST

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 NULLvalue, 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

by :- kudvenkat