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.
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
We will discuss about COALESCE() function in detail, in the next session
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
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