SQL ISNULL() Function: Complete Guide

The ISNULL() function returns the particular value if the expression is NULL. If the expression is NOT NULL, then ISNULL() function returns the expression.

SQL ISNULL()

SQL ISNULL() is a built-in function used to replace the NULL values with the specified value. The ISNULL( ) function is available in MySQL and SQL Server database management systems.

SQL Server ISNULL( ) function replaces the NULL value with another value. This is because NULL has been replaced by 100 via the ISNULL() function, so the total becomes 300 + 100 = 400.

MySQL ISNULL( ) function tests whether the expression is NULL. If the expression is NULL, the ISNULL() function returns 1. Otherwise, this function returns 0.

Syntax

ISNULL(expression, value)

Parameters

1. Expression: It can be of any type that is used for checking NULL.
2. Replacement: It returns the value type if the expression is denoted as NULL.

Note

1. If the value of the expression evaluates to NULL, then the value of the replacement gets implicitly converted to the type of expression.
2. If the expression is NOT NULL, the function returns the value of the expression.

Query 1

Select ISNULL (NULL, 40);

Output

40

Explanation

The function returned the second argument as the first argument specified as NULL.

Query 2

Select ISNULL (12, 40);

Output

12

Explanation

The function returned 1st argument, as it was NOT NULL.

Query 3

Select ISNULL (NULL, ‘2020-02-25’);

Output

‘2020-02-25’

Explanation

The function returned 2nd argument, as 1st argument was NULL.

Query 4

Select ISNULL (‘2020-02-26’, ‘2020-02-25’);

Output

‘2020-02-26’

Explanation

The function returned 1st argument, as it was NOT NULL.

See also

SQL SESSION_USER

SQL SYSTEM_USER

SQL USER_NAME

SQL CURRENT_USER

SQL CONVERT