133 ways to replace null values in SQL Server (Different ways to replace NULL in SQL Server)

There are three ways to replace null values in query results in SQL Server.

If you have one of the following tables:

We can use self-connect (self-join) as a table of employee name and its corresponding manager name:

SELECT E. [name], M.[name]from  [  Tblemployee] as EleftJOIN[  Tblemployee] as Mon= M.employeeid

We can see that Todd's corresponding Manger is null, which means that Todd should be at the top of the company and he has no manager. But the display is unfriendly, and we hope that if Todd does not have a manager, then his manager name will appear as "no Mnager", such as:

As mentioned at the beginning of the article, there are three ways to replace null values in query results, as described below.

A) Replace with the IsNull () function.

SELECTE.[Name],ISNULL(M.[Name],'No Manager')   from [Tblemployee]  asE Left JOIN [Tblemployee]  asM onE.managerid=M.employeeid

If the first argument of the ISNULL () function is a null value, then the result is the second argument.

II) using the COALESCE () function

SELECTE.[Name],COALESCE(M.[Name],'No Manager')   from [Tblemployee]  asE Left JOIN [Tblemployee]  asM onE.managerid=M.employeeid

As you can see, in our example, the IsNull function and the COALESCE function are used the same way. In fact, the two functions are different, the specific differences in the following article will be introduced.

III) using the case and then ELSE End statement

SELECTE.[Name], Case  whenM.[Name]  is NULL  Then 'No Manager' ELSEM.[Name] END   from [Tblemployee]  asE Left JOIN [Tblemployee]  asM onE.managerid=M.employeeid

This statement is a bit like a high-level language such as C # if else if else if else ... Statement. If the conditions after the when are true, then the then statement goes to the Else statement, and vice versa. There can be a lot of when and else in SQL in series to be used as a detection condition.

133 ways to replace null values in SQL Server (Different ways to replace NULL in SQL Server)