digitalBush

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.