Oracle 9i NULL Behavior

This trips me up from time to time, so I wanted to document this for my own benefit and share my findings. Oracle treats NULL different from any other value. Any time you make a direct comparison to a NULL value, the end result is NULL.

This topic is best shown by example.

SELECT * 
FROM records 
WHERE NULL=NULL;

Returns nothing.

SELECT * 
FROM records 
WHERE ''='';

Returns nothing. Oracle treats an empty string (‘’) as NULL.

SELECT * 
FROM records 
WHERE NULL IS NULL;

Returns everything. In order to do a comparison with NULL, you must use “is”.

SELECT * 
FROM records 
WHERE '' IS NULL;

Also returns everything. This demonstrates Oracle’s behavior of the empty string (‘’) being treated as NULL.

Now it's time for the odd stuff. Whenever you are making list comparisons, you should also be careful of NULL. List comparisons are IN/NOT IN and EXISTS/NOT EXISTS.

SELECT * 
FROM records 
WHERE unique_id NOT IN (
   SELECT unique_id 
   FROM some_other_table 
   WHERE 1=0
);

Returns nothing even though you would expect it to. I’m using 1=0 because it is never true. The set will be empty(NULL). The inner query must return something in order for the query to proceed.

SELECT * 
FROM records r 
WHERE NOT EXISTS (
   SELECT 1 
   FROM some_other_table 
   WHERE unique_id=r.unique_id 
   AND 1=0
);

Returns everything. Exists will evaluate to true if something is returned and will evaluate false if nothing(NULL) is returned. Also, please note that in the inner query I’m referencing a value from the outer query(r.unique_id).

SELECT * 
FROM records 
WHERE unique_id NOT IN (
   SELECT NULL AS c 
   FROM dual
);

Returns nothing. The inner query doesn’t return an empty set, instead it returns a single value which is null. This query will take a while to run. It will scan every record and try to perform the following comparison “unique_id <> NULL”, which will fail for all records for reasons stated in the top set of examples.

SELECT * 
FROM records 
WHERE unique_id IN (
   SELECT NULL FROM dual 
   UNION ALL 
   SELECT 11697981 FROM dual --Let us assume this value actually exists in the records table.
);

Returns 1 record. The inner query returns 2 values (NULL, 11697981). Just because the list has a value in it that is null doesn’t mean the entire comparison will fail for that record.

That's all I've got on the NULL situation. Generally I get tripped up on a NOT IN clause or a <> comparison. Hopefully not anymore.

  • Debster

    I’ve read many postings on Oracle NULL vs ” but I’m still not sure how to proceed. Here’s my query

    select field_a, field_b
    from table_a
    where loc_typ = c_loc_typ
    and loc_atfi_name = c_loc_name
    and st_code = c_st_code
    and ctry_code = c_ctry_code
    and post_code = c_post_code
    and post_high_rng = c_post_high_rng ;

    For non-US/CA locations, c_st_code, c_post_code, c_port_high_rng are null. How do I mod above qry to return correct records?