SQL Server ISNULL Function - Replace NULL Values

SQL Server ISNULL() function replaces the NULL value with a given replacement expression.

ISNULL(input_expression, replacement_value)

Parameters

input_expression: This is the expression to be checked for a NULL value. It can be of any type.

replacement_value: value that is returned if the input_expression evaluates to NULL.

Return Value

Returns the same type as the input expression.

If the input_expression is not null then its value is returned else the replacement value is returned.

Note: If a literal NULL is provided as the input_expression, then ISNULL() returns the datatype of the replacement_value. If a literal NULL is provided as the replacement_value and no replacement value is provided, then ISNULL() returns an integer value.

Example 1:

The following example demonstrates the ISNULL() function.

Example: ISNULL()

SELECT ISNULL(NULL, 'Hello') AS Result

The ISNULL() function returns the input expression value if it is not a NULL. In the following example, the given expression 'SQL Server' is not a null value and hence ISNULL returns the expression as it is without replacing it.

Example: ISNULL()

SELECT ISNULL('SQL Server', 'abcd') AS Result;

Now, consider the following Employee table where two rows has NULL value in the DepartmentID column.

In the following example, the ISNULL() replaces all the NULL value in the DepartmenID column with 20.

Example: ISNULL()

SELECT EmployeeID, FirstName, LastName, 
    ISNULL(DepartmentID, 20) As Department FROM Employee;