Here We will learn about different ways to replace NULL values in SQL Server.
Let's see How to replace null.Let's consider the Employees table below.
In previous post We have learnt writing a LEFT OUTER SELF JOIN query, which produced the following output.
In the above image output, MANAGER column, for Sandy rows is NULL. I want to replace the NULL value, with 'No Manager' Let''s see how to replace null value using query
1- Using ISNULL() function:
We are passing 2 parameters to IsNULL() function. If M.EmpName returns NULL, then 'No Manager' string is used as the replacement value.
SELECT E.EmpName as Employees,
ISNULL(M.EmpName,'No Manager') as Manager
FROM Employees E
LEFT JOIN Employees M
ON E.ManagerID = M.EmpID
The output will be:
2-Using CASE Statement:
SELECT E.EmpName as Employees,
CASE WHEN M.EmpName IS NULL
THEN 'No Manager' ELSE M.EmpName END as Manager
FROM Employees E
LEFT JOIN Employees M
ON E.ManagerID = M.EmpID
The output will be like below:
3-Using COALESCE() function:
COALESCE function returns the first non-null expression in the list. If all expressions evaluate to null, then the COALESCE function will return null.
SELECT E.EmpName as Employees,
COALESCE(M.EmpName, 'No Manager') as Manager
FROM Employees E
LEFT JOIN Employees M
ON E.ManagerID = M.EmpID
The output will be like above image :
Let's see How to replace null.Let's consider the Employees table below.
In previous post We have learnt writing a LEFT OUTER SELF JOIN query, which produced the following output.
In the above image output, MANAGER column, for Sandy rows is NULL. I want to replace the NULL value, with 'No Manager' Let''s see how to replace null value using query
1- Using ISNULL() function:
We are passing 2 parameters to IsNULL() function. If M.EmpName returns NULL, then 'No Manager' string is used as the replacement value.
SELECT E.EmpName as Employees,
ISNULL(M.EmpName,'No Manager') as Manager
FROM Employees E
LEFT JOIN Employees M
ON E.ManagerID = M.EmpID
The output will be:
2-Using CASE Statement:
SELECT E.EmpName as Employees,
CASE WHEN M.EmpName IS NULL
THEN 'No Manager' ELSE M.EmpName END as Manager
FROM Employees E
LEFT JOIN Employees M
ON E.ManagerID = M.EmpID
The output will be like below:
3-Using COALESCE() function:
COALESCE function returns the first non-null expression in the list. If all expressions evaluate to null, then the COALESCE function will return null.
SELECT E.EmpName as Employees,
COALESCE(M.EmpName, 'No Manager') as Manager
FROM Employees E
LEFT JOIN Employees M
ON E.ManagerID = M.EmpID
The output will be like above image :
active collagen reviews
ReplyDeleteactive collagen supplements
Buy Lorna Vanderhaeghe
Buy Lorna Vanderhaeghe Estrosmart
Buy Lorna Vanderhaeghe Supplements
Lorna Vanderhaeghe Active Collagen
lorna vanderhaeghe estrosmart
lorna vanderhaeghe health products
lorna vanderhaeghe products
lorna vanderhaeghe thyrosmart
lorna vanderhaeghe website
estrosmart