Oracle IS NULL Operator

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

Introduction to the Oracle IS NULL operator #

In Oracle, NULL is a marker that indicate the data is missing, not applicable, or unknown.

Since NULL is unknown, you cannot compare it with a value. If you compare NULL with NULL using the comparison operator (=), you’ll get NULL as a result:

NULL = NULL -- return NULLCode language: PHP (php)

The reason is that an unknown value many not be equal to another unknown value.

To check if a value is NULL or not, you the IS NULL operator:

value IS NULLCode language: PHP (php)

The IS NULL returns true if the value is NULL or false otherwise.

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

value IS NOT NULLCode language: PHP (php)

The IS NOT NULL returns true if the value is not null or false otherwise.

Notice that NULL is not zero or empty string ”. If you want to compare a value with an empty string, you can use the equal to (=) or not equal operator (!=). For example:

value != ''Code language: JavaScript (javascript)

The expression returns true if the value is not blank or false otherwise.

Oracle IS NULL operator example #

We’ll use the orders table from the sample database.

In the orders table, the salesman_id column stores the id of the salesman who is in charge of the sales order.

The following SELECT statement attempts to retrieve all sales orders that do not have a dedicated salesman:

SELECT
  *
FROM
  orders
WHERE
  salesman_id = NULL
ORDER BY
  order_date DESC;Code language: SQL (Structured Query Language) (sql)

Try it

It returns an empty result set.

In this example:

• The expression salesman_id = NULL always returns false.
• The WHERE clause do not include any rows from the orders table.
• The statement returns an empty set.

The following query uses the IS NULL operator to retrieve all orders that do not have a dedicated salesman:

SELECT
  *
FROM
  orders
WHERE
  salesman_id IS NULL
ORDER BY
  order_date DESC;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

Oracle IS NOT NULL example #

The following staetment uses the IS NOT NULL operator to retrieve all sales orders which have a dedicated salesman:

SELECT
  *
FROM
  orders
WHERE
  salesman_id IS NOT NULL
ORDER BY
  order_date DESC;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

Summary #

• The IS NULL returns true if a value is null or false otherwise.
• The IS NOT NULL returns true if a value is not null or false otherwise.
• Do not use a comparion operator (=) to check if a value is NULL.

Quiz #