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