Most major DBMSs provide us with a function for replacing null values with another value.

But the name of the function tends to differ across DBMSs. For example, SQL Server has an ISNULL() function, while others (such as MySQL, MariaDB, and SQLite) have an IFNULL() function for doing the same thing.

However, to confuse matters, MySQL and MariaDB each have an ISNULL() function that works differently to SQL Server’s function of the same name (MySQL and MariaDB’s implementation only accept a single argument, and return 1 if its null and 0 if it’s not).

Anyway, in the case of Oracle Database, we can use the NVL() function to replace null values with another value.

Actually, Oracle Database also has an NVL2() function that allows us to provide another value to use in the event that the first argument is not null.

If we simply want to test whether a value is null or not, we can use the IS NULL condition (or the IS NOT NULL for the opposite test).

The NVL() Function

Here’s an example that demonstrates how the NVL() function works:

SELECT NVL(null, 'Run')
FROM DUAL;

Result:

Run

In that case, the first argument was null and so the second argument was returned.

Here’s what happens when the first argument is not null:

SELECT NVL('Walk', 'Run')
FROM DUAL;

Result:

Walk

The first argument is returned.

The NVL2() Function

As mentioned, Oracle Database also provides us with the NVL2() function. This function allows us to provide another value to use in the event that the first argument is not null.

Here’s an example of how it works:

SELECT NVL2(null, 2, 3)
FROM DUAL;

Result:

3

The first argument was null and so the third argument was returned.

Here’s what happens when the first argument is not null:

SELECT NVL2(1, 2, 3)
FROM DUAL;

Result:

2

The second argument is returned.

The IS NULL and IS NOT NULL Conditions

If we just want to find out whether or not a value is null, we can use the IS NULL comparison condition. We can alternatively use IS NOT NULL to check whether it’s not null.

Suppose we have the following table:

SELECT * FROM Autoparts
WHERE Price IS NULL;

Result:

ID	OE#	PRICE	MODEL
2	62150B3278	–	CAMRY
1	62150A3278	168	HILUX

In this case, the PRICE column contains a null value for the first row, but not for the second row.

Here’s an example of using IS NULL against that table:

SELECT * FROM Autoparts
WHERE Price IS NULL;

Result:

ID	OE#	PRICE	MODEL
2	62150B3278	–	CAMRY

Only the row with the null price is returned.

Here’s what happens when we use IS NOT NULL:

SELECT * FROM Autoparts
WHERE Price IS NOT NULL;

Result:

ID	OE#	PRICE	MODEL
1	62150A3278	168	HILUX

The other row is returned.