Different ways to replace NULL in sql server

In this session, we will learn about different ways to replace NULL values in SQL Server.

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