How To Use NULL as Conditions in Oracle?

If you want to compare values against NULL as conditions, you should use the "IS NULL" or "IS NOT NULL" operator. Do not use "=" or "<>" against NULL. The sample script below shows you some good examples:

SELECT 'A' IS NULL FROM DUAL;
  -- Error: Boolean is not data type. 
  -- Boolean can only be used as conditions
  
SELECT CASE WHEN 'A' IS NULL THEN 'TRUE' ELSE 'FALSE' END
  FROM DUAL;
FALSE

SELECT CASE WHEN '' IS NULL THEN 'TRUE' ELSE 'FALSE' END
  FROM DUAL;
TRUE

SELECT CASE WHEN 0 IS NULL THEN 'TRUE' ELSE 'FALSE' END
  FROM DUAL;
FALSE

SELECT CASE WHEN NULL IS NULL THEN 'TRUE' ELSE 'FALSE' END
  FROM DUAL;
TRUE

SELECT CASE WHEN 'A' = NULL THEN 'TRUE' ELSE 'FALSE' END
  FROM DUAL;
  -- Do not use "="
FALSE

SELECT CASE WHEN 'A' <> NULL THEN 'TRUE' ELSE 'FALSE' END
  FROM DUAL;
  -- Do not use "<>"
FALSE

SELECT CASE WHEN NULL = NULL THEN 'TRUE' ELSE 'FALSE' END
  FROM DUAL;
  -- Do not use "="
FALSE

⇒ Concatenate Two Text Values in Oracle

⇐ What Is NULL Value in Oracle

⇑ Understanding SQL Language Basics for Oracle

⇑⇑ Oracle Database Tutorials