Different
ways to replace NULL in SQL SERVER
Consider the Employees table below.
Write 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
