IS NULL

NULL is a special value in Oracle that represents absence of value in a field.

In the world of relational databases, A Null means:

1. A field in column which has not been assigned any value.
2. NULL value represents empty value, i.e, no value was given.
3. NULL is not equal to zero, an empty character string, datetime or any other data type.
4. Any arithmetic operations performed on NULL will return NULL.

NULL is not even equal to any other NULL value. This means if we want to find all the empty or null values, we can’t even use the following query.

SELECT first_name FROM employees 
WHERE first_name = NULL;

This query will return an empty row as it is not valid.

IS NULL is a special operator in Oracle which is used to find NULL values in table.

Syntax

The syntax for IS NULL operator is as follows:

SELECT column_name FROM table_name
WHERE column_name IS NULL

This query retrieves all the NULL values in the column and if none is present then it returns FALSE.

1. Oracle IS NULL operator example

We are using Employees Table from HR schema for this example.

If we want to find all the NULL values in commission_pct column, then we use this query:

SELECT first_name, last_name,salary, commission_pct 
FROM employees
WHERE commission_pct IS NULL;

Result:

The query returns all the rows which have NULL values.

2. Oracle IS NOT NULL operator Example

IS NOT NULL operator is the exact opposite of IS NULL operator and returns all the non-NULL values.

To find all the non-NULL values from commission table, we use this query:

SELECT first_name, last_name,salary, commission_pct 
FROM employeesWHERE commission_pct 
IS NOT NULL;

Result:

We get all the non-NULL values in commission_pct table by using this query.

I hope this cleared all your doubts about the NULL values in Oracle table and how to retrieve them using IS NULL operator.