Using ISNULL in SQL

In this article, we are going to learn about the ISNULL() function to replace a null with a given value in SQL. Real examples are included to overcome problems in SQL Server & MySQL.

ISNULL() Function

The ISNULL() function is given in the following format. 

Syntax

SELECT ISNULL(Column,  Value)

Table 1. Syntax for ISNULL Function

Explanation of parameters used in the function:

• Column is the column name that will be checked for NULL values. It is a required parameter. 
• Value is the default value to be assigned if the column value is NULL. 

The function will return the column value if it is not null. Otherwise, it will return the default value.

Example

Let’s have an example to identify the approach of the ISNULL() function. Consider the following tables for the example.

Employees Table: 

Id	SectionId	Bonus
1	1	1000
2	2	NULL
3	1	3000
4	NULL	NULL
5	3	NULL
6	NULL	6000
7	5	NULL
8	NULL	7000
9	5	0
10	3	0

Table 2. Sample Data for Employees

SectionsTable: 

Id	Name
1	Planning
2	Consulting
3	QA
4	IT
5	R & D

Table 3. Sample Data for Sections

By using the following query, we can create sample data for the given example.

SQL Data Script:

CREATE TABLE Employees   (      Id INT NOT NULL,SectionId INT NULL,Bonus DECIMAL(12, 2) NULL,      PRIMARY KEY (Id)   ); CREATE TABLE Sections   (      Id INT NOT NULL,Name VARCHAR (10) NOT NULL,      PRIMARY KEY (Id)   ); INSERT Employees (Id,SectionId,Bonus) VALUES(1,1,1000), (2,2,NULL), (3,1,3000), (4,NULL,NULL), (5,3,NULL), (6,NULL,6000), (7,5,NULL), (8,NULL,8000), (9,5,0), (10,3,0) INSERT Sections (Id,Name) VALUES(1,'Planning'), (2,'Consulting'), (3,'QA'), (4,'IT'), (5,'R & D');

Script 1. Sample Data

We can use the following SQL query to get the employee data with sections.

SQL Script:

SELECT Employees.Id,Sections.Name,Employees.Bonus FROM   Employees LEFT JOIN Sections ON Employees.SectionId = Sections.Id;

Script 2. Converting to Lowercase

The result is shown below. 

Id	Section	Bonus
1	Planning	1000.00
2	Consulting	NULL
3	Planning	3000.00
4	NULL	NULL
5	QA	NULL
6	NULL	6000.00
7	R & D	NULL
8	NULL	8000.00
9	R & D	0.00
10	QA	0.00

Table 3. Employee Data with Sections

The following query shows how to use the ISNULL() function to retrieve data without nulls.

SQL Script:

SELECT Employees.Id, ISNULL(Sections.Name, '') AS Section, ISNULL(Bonus, 0)  AS Bonus FROM   Employees LEFT JOIN Sections ON Employees.SectionId = Sections.Id;

Script 3. Converting nulls

Then we will get the below result.

Id	Section	Bonus
1	Planning	1000.00
2	Consulting	0.00
3	Planning	3000.00
4		0.00
5	QA	0.00
6		6000.00
7	R & D	0.00
8		8000.00
9	R & D	0.00
10	QA	0.00

Table 4. Corrected Employee Data

Note

Nulls in the database may affect calculations, queries & functions. You may overcome the problem in the following ways.

1. Using ISNULL() function to update nulls in the database if necessary.
2. Using ISNULL() function in queries to replace null values in the result.