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.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiBBmAtBVAV9PBRGVv8TayWrTYwhuTP72kZ-mR1jN_3zg0fkBJdtW7AhxYWiQ-DVqv6gETw-rfJMdtDMZ8kZp6e3wQGq36OsBUGhk74qzKWFkND95t9it03nkp5htO27vscX5NU9lF9gX6I/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/AVvXsEhbjGLZoiu2gxZRKjw_FcveEJYlpliiwdwC3qYzfGeO2BBkViJSUemdmxGs4IDROqTynGn8rBqza60Xe24bXrXqnpXMuzmzP5LGK4_lyU2V7X5JK-DILZwpJTDrcUTvjBWdYYjlIqFnOPiO/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/AVvXsEiBBmAtBVAV9PBRGVv8TayWrTYwhuTP72kZ-mR1jN_3zg0fkBJdtW7AhxYWiQ-DVqv6gETw-rfJMdtDMZ8kZp6e3wQGq36OsBUGhk74qzKWFkND95t9it03nkp5htO27vscX5NU9lF9gX6I/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/AVvXsEhbjGLZoiu2gxZRKjw_FcveEJYlpliiwdwC3qYzfGeO2BBkViJSUemdmxGs4IDROqTynGn8rBqza60Xe24bXrXqnpXMuzmzP5LGK4_lyU2V7X5JK-DILZwpJTDrcUTvjBWdYYjlIqFnOPiO/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
Thaks you're the man!!
ReplyDeleteThis is an excellent tutorial. I did not understand COALESCE() function much, however, I wish that I will get to know that in future videos as you mentioned. Thank you much, Sir!!!
ReplyDeleteVenkat you are doing a great job its really amazing
ReplyDeleteThanks for your effort to explain simply but understandably
ReplyDeleteThanks For All The Examples These are very helpfull for me
ReplyDeleteThank you so much Vekant , videos are very helpful to understand basic..
ReplyDeleteKeep it up great !!!
Thank you So much Venkat,For ur effort .It gives a life to so many people
ReplyDeletelike Me.
Hi Venkat,
ReplyDeleteIm unable to solve this. Can you please help-
Write a query for the table below to get the output result ?
Input-
Id, val1, val2, val3
1, null, null, hello
1, world, null, null
1, null, spark, null
2, good, morning, spark
Output-
Id, val1, val2, val3
1, world, spark, hello
2, good, morning, spark
with x as (select * from table_Name)
Deleteselect distinct x.id,
STUFF((SELECT ', ' + val1
FROM x x2
WHERE x2.id = x.id
FOR XML PATH ('')) , 1, 2, '') as val1,
STUFF((SELECT ', ' + val2
FROM x x2
WHERE x2.id = x.id
FOR XML PATH ('')) , 1, 2, '') as val2,
STUFF((SELECT ', ' + val3
FROM x x2
WHERE x2.id = x.id
FOR XML PATH ('')) , 1, 2, '') as val3
from (SELECT DISTINCT id, val1,val2,val3 FROM x) x;
Create Table EMP
ReplyDelete( EmployeeID INT Primary key,
Name nvarchar(50),
ManagerId nvarchar(50)
)
Insert Into EMP Values ( 1, 'Mike',3)
Insert into EMP Values (2, 'Rob',1)
Insert Into EMP Values ( 3, 'Todd',NULL)
Insert Into EMP Values (4,'Ben',1)
Insert Into EMP Values (5,'Sam',1)
Create Table Manager
(
Employee nvarchar(30),
Manager nvarchar(30)
)
Insert into Manager Values ( 'Mike','Todd')
Insert into Manager Values ( 'Rob','Mike')
Insert into Manager Values ( 'Todd',NULL)
Insert into Manager Values ( 'Ben','Mike')
Insert into Manager Values ( 'Sam','Mike')
Select * From EMP
Select * From Manager