ISNULL in SQL Server

ISNULL function replaces NULL values with the specified replacement value. you can use ISNULL as :

ISNULL ( check_exp , replacement_val )

The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned after it is implicitly converted to the type of check_expression, if the types are different.


Suppose you have an table tbl_employee:

Employee Id EmpName Age Department Salary
1 Ankur 28 Sales 2500.00
2 David 32 Sales 3000.00
3 John 30 NULL 2200.00
4 James 45 Production 4200.00
5 Mohan 35 Production 4300.00
6 Ram 40 NULL 2000.00
7 McDonald 30 NULL 2600.00
8 Rohan 23 Sales 1600.00


Simple use of ISNULL

The following query will select the EmpName, Age, and Department in above table. If the Department for a particular employee is NULL, the Department shown in the result set is ‘HR’.

SELECT EmpName,Age,ISNULL(department,'HR') 
FROM tbl_employee

Output will be:

EmpName Age Department
Ankur 28 Sales
David 32 Sales
John 30 NULL
James 45 Production
Mohan 35 Production
Ram 40 NULL
McDonald 30 NULL
Rohan 23 Sales

How to use NULL with WHERE clause

Suppose now you want to select the records that have NULL value in the Department column, You can use NULL like as :

SELECT * FROM tbl_employee
 WHERE Department IS NULL

The Output will be

3 John 30 NULL 2200.00
6 Ram 40 NULL 2000.00
7 McDonald 30 NULL 2600.00