ISNULL() Function, COALESCE() Function And CASE Statement

Introduction

There are three ways in which we can remove Null values in Table data and show them to the user.

Description

In our Table data, some columns contain null values. We can replace it using ISNULL() function , COALESCE() function, and CASE Statement.

Steps

Create one table.

CREATE TABLE [dbo].[tblEmployees](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NULL,
    [Gender] [nvarchar](10) NULL,
    [Salary] [int] NULL,
PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Then, enter some dummy records.

 
Replace the Null value using ISNULL() function.

SELECT E.Name as Employee, ISNULL(M.Gender,'No Gender') as Gender
FROM tblEmployees E
LEFT JOIN tblEmployees M
ON E.Id = M.Id

Replace the Null value using COALESCE() function() function.

SELECT E.Name as Employee, COALESCE(M.Gender,'No Gender') as Gender
FROM tblEmployees E
LEFT JOIN tblEmployees M
ON E.Id = M.Id

Replace the Null value using CASE statement.

SELECT E.Name as Employee, CASE WHEN M.Gender IS NULL THEN 'No Gender'
   ELSE M.Gender END as Gender
FROM  tblEmployees E
LEFT JOIN tblEmployees M
ON E.Id = M.Id

 

Summary

Here, we learned the following -

1. ISNULL() function , COALESCE() function and CASE Statement.
2. Why and how we use these.