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.
salesman_id column stores the salesman id of the salesman who is in charge of the sales order.
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
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:
IS NOT NULL operator
To negate the
IS NULL operator, you use the
IS NOT NULL operator as follows:
expression | column IS NOT NULL
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.