fbpx

Oracle IS NULL Operator

This SQL tutorial focuses on the Oracle IS NULL operatorand 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:


 

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)

UpScale Analytics is one of the largest platforms in the world for learning SQL by doing, consisting over 300 SQL exercises at different levels (including solutions), by topics, across over 100 different datasets. More…