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.

Example

Suppose you have an table tbl_employee:
 
tbl_employee

Employee IdEmpNameAgeDepartmentSalary
1Ankur28Sales2500.00
2David32Sales3000.00
3John30NULL2200.00
4James45Production4200.00
5Mohan35Production4300.00
6Ram40NULL2000.00
7McDonald30NULL2600.00
8Rohan23Sales1600.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:

EmpNameAgeDepartment
Ankur28Sales
David32Sales
John30NULL
James45Production
Mohan35Production
Ram40NULL
McDonald30NULL
Rohan23Sales

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

3John30NULL2200.00
6Ram40NULL2000.00
7McDonald30NULL2600.00