Oracle IS NULL

Summary: in this tutorial, you will learn how to use the Oracle IS NULL and IS NOT NULL operators to check if a value in a column or an expression is NULL or not.

Introduction to the Oracle IS NULL operator

In the database world, NULL is special. It is a marker for missing information or the information is not applicable.

NULL is special in the sense that it is not a value like a number, character string, or datetime, therefore, you cannot compare it with any other values like zero (0) or an empty string (”). Generally speaking, NULL is even not equal to NULL.

Let’s see the orders table from the sample database.

The salesman_id column stores the salesman id of the salesman who is in charge of the sales order.

The following SELECT statement attempts to return all sales orders which do not have a responsible salesman:

SELECT * FROM orders 
WHERE salesman_id = NULL
ORDER BY order_date DESC;

It returns an empty row.

The query uses the comparison operator (=) to compare the values from the salesman_id column with NULL, which is not correct.

To check if a value is NULL or not, you should use the IS NULL operator as follows:

expression |  column  IS NULL

The IS NULL operator returns true if the expression or column is NULL. Otherwise, it returns false.

The following query returns all sales orders that do not have a responsible salesman:

SELECT * FROM orders 
WHERE salesman_id IS NULL
ORDER BY order_date DESC;

Here is the partial output of the query:

Oracle IS NOT NULL operator

To negate the IS NULL operator, you use the IS NOT NULL operator as follows:

expression | column IS NOT NULL

The operator IS NOT NULL returns true if the expression or value in the column is not null. Otherwise, it returns false.

For example, the following example returns all sales orders which have a responsible salesman:

SELECT * FROM orders
WHERE salesman_id IS NOT NULL
ORDER BY order_date DESC;

This picture illustrates the partial output:

In this tutorial, you have learned how to use the Oracle IS NULL and IS NOT NULL to check if an expression or value in a column is null or not.