Oracle SQL NULL Operators

NULL is an Unknown value. If there is nothing in any column of the table, then it is known as NULL value. NULL and Zero values are not same.  You cannot use NULL instead of zero because both are not equivalent.

If any arithmetic expression contains NULL, then its result will be always NULL.

Null operations in SQL

Using NULL with SQL functions.

NVL

You can use NVL operator to return a value if the NULL value occurs.

EX:-  NVL(Emp_Commission, 0)

It will return Zero value if the Emp_Commission is NULL or it will return the Emp_Commission if it is not null.

Compare NULL Value

IS NULL

You cannot check the NULL by using the comparison operators <, =, > and <>.

If you want to test NULL value, you can use IS NULL operator in the SQL query as shown below.

SELECT EMP_NO, EMP_NAME, SALARY

FROM EMP

WHERE BONUS IS NULL;

This query will return all records from EMP table whose BONUS column contains NULL value.

IS NOT NULL

You can also use IS NOT NULL operator for comparing NULL value.

The below query returns all employees whose Bonus is not null

SELECT EMP_NO, EMP_NAME, SALARY

FROM EMP

WHERE BONUS IS NOT NULL;