In MariaDB, the ISNULL() function checks whether an expression is null or not. If the expression resolves to null, then ISNULL() returns 1, otherwise it returns 0.

Syntax

ISNULL(expr)

Example

Here’s a simple example to demonstrate:

SELECT ISNULL( null );

Result:

1

In this case, the value is null and so the output is 1.

Here it is with a non-null value:

SELECT ISNULL( 'Treehouse' );

Result:

0

This time the result is 0 (because the expression is not null).

Expressions

The function checks the result of any expression. So if we do the following:

SELECT ISNULL( 7 / 0 );

We get this:

1

So in this case, we didn’t specify null as the argument, but the result of the expression is null, and so ISNULL() returns 1.

Here it is again, alongside the actual output from the expression:

SELECT 
    7 / 0,
    ISNULL( 7 / 0 );

Result:

7 / 0	ISNULL( 7 / 0 )
-----   ---------------
NULL	1

In the first column we can see that the calculation itself resulted in a null value, and so the second column returned 1.

Database Example

Suppose we have a database table like this:

SELECT * FROM Employees; 

Result:

empId	name	dept
-----   -----   -----
1	Jess	Sales
2	Rohit	NULL
3	Zohan	Sales
4	Homer	NULL

Here’s a query that uses the ISNULL() function against that table:

SELECT
    empId,
    name,
    ISNULL( dept ) AS "Is dept NULL?"
FROM Employees;

Result:

empId	name	Is dept NULL?
-----   -----   -------------
1	Jess	0
2	Rohit	1
3	Zohan	0
4	Homer	1

In that case, we created a column that displays 1 for all employees that don’t have anything in their dept column.

We can use ISNULL() in a WHERE clause to return all employees that haven’t been assigned a department (i.e. their dept field is null). In this case, we can do the following:

SELECT * FROM Employees
WHERE ISNULL(dept) = 1;

Result:

empId	name	dept
-----   -----   ----
2	Rohit	NULL
4	Homer	NULL

That’s the same as doing the following:

SELECT * FROM Employees
WHERE dept IS NULL;

Result:

empId	name	dept
-----   -----   ----
2	Rohit	NULL
4	Homer	NULL