Explanation and Handling of NULL Value in Oracle Database

milly 3:57 pm on March 20, 2010 | Answers: 1    
Tags: explanation (7), handling (2), null, oracle (136), value (10)

Rate Knowledge:

 Loading ...

• milly 3:57 pm on March 20, 2010
  

  1. Oracle’s definition and usage of NULL value is different from other RDBMS.
  2. In Oracle, Null does NOT mean “No value”. Null is an “unknown”. You can’t compare an unknown with another unknown. This is like saying “I’m thinking of a number, you’re thinking of a number. Neither of us know what both those numbers are. They could be the same, or different.”

  Therefore, you’ll see the following two behaviors:
  

  SELECT * FROM dual WHERE NULL = NULL;
  
  No rows selected.

  SELECT * FROM dual WHERE NULL <> NULL;
  
  No rows selected.

  3. When checking for null, use “IS NULL” or “IS NOT NULL” instead of “= NULL” or “!= NULL”.

  4. When you use != and want to return rows that also include NULL value in a select statement, the following will fail:
  SELECT * FROM table_name WHERE col1 != 'value';

  Instead, you need to do the following:
  SELECT * FROM table_name WHERE (col1 != 'value' OR col1 IS NULL);