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)