SQL - Handling NULL values

Here lets discuss about how to deal with NULL values in SQL.

Take a sample tblEmployeeStructure like below. In an organization, all are employees irrespective of their position say Lead,Manager,People Manager etc .


 

From the above table we need to know who is Manager for each employee.
Scott is having Manager_ID as 3, so Rosy is having Employee ID as 3.
Hence Rosy is a manager for Scott.

We wanted to get the each employee Manager like below, this is one of the important interview question.





Now lets see how to work out with this.

In the output, MANAGER column, for Rosy's rows is NULL. We 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 ManagerFROM tblEmployeeStructure ELEFT JOIN tblEmployeeStructure MON 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 ManagerFROM tblEmployeeStructure ELEFT JOIN tblEmployeeStructure MON 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 ManagerFROM tblEmployeeStructure ELEFT JOIN tblEmployeeStructure MON E.ManagerID = M.EmployeeID