Some RDBMSs provide an ISNULL() function that can be used when dealing with potentially null values.

MySQL, MariaDB, and Oracle Database each have an ISNULL() function that returns 1 if its argument is null, and 0 if it’s not.

SQL Server also has an ISNULL() function, but it works differently. It works more like how the IFNULL() function works in some other RDBMSs.

Other RDBMSs, such as PostgreSQL and SQLite don’t include an ISNULL() function, but they do support the IS NULL predicate (as do the other RDBMSs).

MySQL, MariaDB, & Oracle

As mentioned, MySQL, MariaDB, and Oracle Database each have an ISNULL() function that returns 1 if its argument is null, and 0 if it’s not.

Here’s an example of how ISNULL() works in those RDBMSs:

SELECT
    ISNULL( null ),
    ISNULL( 'Tropical' );

Result:

+----------------+----------------------+
| ISNULL( null ) | ISNULL( 'Tropical' ) |
+----------------+----------------------+
|              1 |                    0 |
+----------------+----------------------+

We can see that the first call to ISNULL() returned 1, because we passed a null value.

The second call to the function returned 0, because we passed a non-null value.

Here’s an example that uses a database:

SELECT * FROM Pets
WHERE ISNULL(DOB);

Result:

+-------+-----------+---------+---------+------+
| PetId | PetTypeId | OwnerId | PetName | DOB  |
+-------+-----------+---------+---------+------+
|     7 |         3 |       2 | Bark    | NULL |
|     8 |         2 |       4 | Meow    | NULL |
+-------+-----------+---------+---------+------+

In this case, we returned all rows from the Pets table where the DOB column is null.

We can also explicitly specify that ISNULL(DOB) equals 1:

SELECT * FROM Pets
WHERE ISNULL(DOB) = 1;

Result:

+-------+-----------+---------+---------+------+
| PetId | PetTypeId | OwnerId | PetName | DOB  |
+-------+-----------+---------+---------+------+
|     7 |         3 |       2 | Bark    | NULL |
|     8 |         2 |       4 | Meow    | NULL |
+-------+-----------+---------+---------+------+

SQL Server

SQL Server’s implementation of ISNULL() works differently. In SQL Server, ISNULL() does the same thing that IFNULL() works in other RDBMSs. That is, it accepts two expressions. If the first one is null, then it returns the second expression. Otherwise it returns the first expression.

Here’s an example of how ISNULL() works in SQL Server:

SELECT ISNULL( null, 'Cheese' );

Result:

Cheese

Sure, we could use it to return 1:

SELECT ISNULL( null, 1 );

Result:

1

But this will only return 0 if the first argument is 0:

SELECT ISNULL( 0, 1 );

Result:

0

The NULL Predicate

The NULL predicate (aka IS NULL predicate) is part of the SQL standard, and most (if not all) major RDBMSs support it.

So when using RDBMSs such as PostgreSQL and SQLite (that don’t provide an ISNULL() function), and when using SQL Server (where ISNULL() works differently), the NULL predicate is still an option.

The NULL predicate can be used in the following ways:

R IS NULL
R IS NOT NULL 
NOT R IS NULL
NOT R IS NOT NULL

Where R is the expression to evaluate.

Here’s an example that uses the same database table from a previous example:

SELECT * FROM Pets
WHERE DOB IS NULL;

Result:

+-------+-----------+---------+---------+------+
| PetId | PetTypeId | OwnerId | PetName | DOB  |
+-------+-----------+---------+---------+------+
|     7 |         3 |       2 | Bark    | NULL |
|     8 |         2 |       4 | Meow    | NULL |
+-------+-----------+---------+---------+------+

We get the same result that we got in ISNULL() example.