How to use Is null
and coalesce and case statement to replace null value to
something else in the table
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
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
Differences between UNION and UNION ALL (Common Interview
Question)
Union and union all used to combine result
Union and union all used to combine result
From the output, it is very clear that, UNION removes duplicate rows, where as UNION ALL does not. When use
UNION, to remove the duplicate rows, sql server has to to do
a distinct sort, which is time consuming. For this reason, UNION ALL is much
faster than UNION.
JOINS and UNIONS are different things. However, this
question is being asked very frequently now. UNION combines the result-set of
two or more select queries into a single result-set which includes all the rows
from all the queries in the union, where as JOINS, retrieve data from two or
more tables based on logical relationships between the tables. In short,
UNION combines rows from 2 or more tables, where JOINS combine columns from 2
or more table.
No comments:
Post a Comment