Oracle IS NULL Operator

This SQL tutorial focuses on the Oracle IS NULL operator, and provides explanations, examples and exercises. For this lesson’s exercises, use this link.

This tutorial is a part of several posts explaining how to use the WHERE clause in Oracle. To read additional posts regarding this subject, please use the following links:

• Introduction – Oracle WHERE clause
• Simple comparison operators
• IN operator
• BETWEEN operator
• LIKE operator
• IS NULL operator
• AND & OR operators

Oracle IS NULL / IS NOT NULL

In Oracle,  NULL value indicates an unavailable or unassigned value. The value NULL does not equal zero (0), nor does it equal a space (‘ ‘). Because the NULL value cannot be equal or unequal to any value, you cannot perform any comparison on NULL values by using operators such as ‘=’ or ‘<>’.

The following Oracle queries will produce no results:

The products without any discount:

SELECT product_id, product_name, unit_price, discount
FROM products
WHERE  discount = NULL  
-- (no rows will be retrieved)

Products with a discount:

SELECT product_id, product_name, unit_price, discount
FROM products
WHERE  discount <> NULL 
-- (no rows will be retrieved)

In Oracle, to handle comparison with NULL values, you need to use the following Oracle operators:

• IS NULL – equals the logical operation ‘= NULL’ (records with NULL values)
• IS NOT NULL – equals the logical operation ‘<> NULL’ (records without NULL values)

The products without a discount:

SELECT product_id, product_name, unit_price, discount
FROM products
WHERE  discount IS NULL  
-- (error)

The products with a discount:

SELECT product_id, product_name, unit_price, discount
FROM products
WHERE  discount IS NOT NULL  
-- (error)