we will learn about different ways to replace NULL values in SQL Server.
Consider the Employees table below.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjmAQro4XIyaQ0HphJ5bofZ-ladahiRn_e0Fbfd7LX-45QrEO8ribsRk584ahgUTQg2Ih5uqCF8id7J6YCwGCoUDAV7O9H1rXxVWk3g6iUjcNiJxVoFINfu6MKdTMMC7OJlB4sZc0yrQMd5/s1600/tblEmployee.png)
In Part 14, we have learnt writing a LEFT OUTER SELF JOIN query, which produced the following output.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEinUPl7WnI5FjC98dk58-P3U2wRHNBQSAWoy3C4UeyRpeQ-u-NtvYpkOIfChQ0TYnc5bZZ3qUylkN2a7NYx_QQxrcLtS1rwgoMDYXlE99pu5Nd7lsJecDHt1nVIeiYI7M3IGz4-zSifcTvG/s1600/Self+Join.png)
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
Consider the Employees table below.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjmAQro4XIyaQ0HphJ5bofZ-ladahiRn_e0Fbfd7LX-45QrEO8ribsRk584ahgUTQg2Ih5uqCF8id7J6YCwGCoUDAV7O9H1rXxVWk3g6iUjcNiJxVoFINfu6MKdTMMC7OJlB4sZc0yrQMd5/s1600/tblEmployee.png)
In Part 14, we have learnt writing a LEFT OUTER SELF JOIN query, which produced the following output.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEinUPl7WnI5FjC98dk58-P3U2wRHNBQSAWoy3C4UeyRpeQ-u-NtvYpkOIfChQ0TYnc5bZZ3qUylkN2a7NYx_QQxrcLtS1rwgoMDYXlE99pu5Nd7lsJecDHt1nVIeiYI7M3IGz4-zSifcTvG/s1600/Self+Join.png)
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
0 comments:
Post a Comment
Note: only a member of this blog may post a comment.