Replacing NULL and Empty string in Select statement

Welcome to Quickpickdeal , in this session, we will learn about the various methods available to replace null values in SQL Server. Specifically, we will explore the ISNULL() function, the CASE statement, and the COALESCE() function.

Now, let's consider a table named TblEmployeeManager, which consists of three columns: EmpId, name, and Manager_Id.

Now, if I ask you who is the manager of Jackson, you'll look at the Manager_Id column. Jackson's Manager_Id is 6, so you look up 6 within the EmpId column, which corresponds to Gabriel. Therefore, Jackson's manager is Gabriel.

Similarly, if we consider Anthony, his Manager_Id value is NULL, indicating that Anthony doesn't have a manager.

In our previous SQL joins series, we wrote a left outer self-join to retrieve employee names along with their manager names. Since Anthony doesn't have a manager ID, his manager's name is Null.

If any employee doesn't have a manager, we don't want to display Null. Instead, we want to replace those null values with the phrase 'no manager'. Is this possible? Absolutely. We have different ways to accomplish this.

I don’t want 'null' to be displayed instead of null, I want to replace it with 'Super Boss'.

We can utilize the ISNULL(), CASE statement, and COALESCE() functions. Let's examine practical examples of using each of them.

So, since we are utilizing the Manager_Id column and then referencing the EmpId column, we're essentially performing a self-join here.

If you're unsure about what a self-join is, please refer to the post below.

• What is SELF JOIN ? with Realtime Example

So let’s right the left outer self join Query.

SELECT Emp.EmployeeName as Employee, Mng.EmployeeName as Manager
FROM TblEmployeeManager Emp
LEFT JOIN TblEmployeeManager Mng
ON Emp.Manager_Id = Mng.EmpId

When we execute the above query, we obtain the following result. But what is our ultimate goal? Our ultimate goal is essentially to replace the null value with the word 'Super Boss'.

So how do we accomplish that? The easiest way to do it is to use the ISNULL() function.

SELECT Emp.EmployeeName as Employee, ISNULL(Mng.EmployeeName,' Super Boss') as Manager
FROM TblEmployeeManager Emp
LEFT JOIN TblEmployeeManager Mng
ON Emp.Manager_Id = Mng.EmpId

Now ISNULL() is a simple function, now as the name itself suggests ISNULL if it is NULL.

The first parameter is an expression that you pass in. If the expression returns null, then there is a second parameter called the replacement value.

Similarly, you can also use the COALESCE() function. Another way to replace null values is by using the COALESCE() function. If you look up the definition of the COALESCE() function in the MSDN books online, it states that it returns the first non-null value. What does this mean? The usage of the COALESCE() function is quite similar to the ISNULL() function, but it offers more versatility.

It is essentially used to return the first non-null value, making it a very powerful function. You can read more about COALESCE() in the post below.

SELECT Emp.EmployeeName as Employee, COALESCE(Mng.EmployeeName,' Super Boss') as Manager
FROM TblEmployeeManager Emp
LEFT JOIN TblEmployeeManager Mng
ON Emp.Manager_Id = Mng.EmpId

COALESCE() is much more powerful than ISNULL(). You can pass and multiple expressions to that and it returns the first non-null expression value.

The final way to use the case statement. now you have a case statement is also very simple,

SELECT Emp.EmployeeName as Employee, CASE WHEN Mng.EmployeeName IS NULL then 'Supper Boss' ELSE Mng.EmployeeName END as Manager
FROM TblEmployeeManager Emp
LEFT JOIN TblEmployeeManager Mng
ON Emp.Manager_Id = Mng.EmpId

Table Sql Script

GO
/****** Object:  Table [dbo].[TblEmployeeManager]    Script Date: 10/12/2020 9:41:49 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblEmployeeManager](
    [EmpId] [int] IDENTITY(1,1) NOT NULL,
    [EmployeeName] [nvarchar](max) NULL,
    [Manager_Id] [int] NULL,
 CONSTRAINT [PK_TblEmployeeManager] PRIMARY KEY CLUSTERED 
(
    [EmpId] ASC
)
) 
GO
SET IDENTITY_INSERT [dbo].[TblEmployeeManager] ON 
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (1, 'Michael', 5)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (2, 'Jackson', 6)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (3, 'Jacob', 6)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (4, 'Luke', 5)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (5, 'Grayson', 8)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (6, 'Gabriel', 9)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (7, 'Anthony', NULL)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (8, 'Lincoln', 7)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (9, 'Ryan', 7)
GO
SET IDENTITY_INSERT [dbo].[TblEmployeeManager] OFF
GO