[oracle] null vs empty string in Oracle

Possible Duplicate:
Why does Oracle 9i treat an empty string as NULL?

I have a table in Oracle 10g named TEMP_TABLE with only two columns - id and description just for the sake of demonstration.

The column id is a sequence generated primary key of type NUMBER(35, 0) not null and the column DESCRIPTION is a type of VARCHAR2(4000) not null.

The basic table structure in this case would look something like the following.

+--------------+-----------+---------------+
|Name          | Null?     | Type          |
+--------------+-----------+---------------+
|ID            | NOT NULL  | NUMBER(35)    |
|DESCRIPTION   | NOT NULL  | VARCHAR2(4000)|
+--------------+-----------+---------------+

After creating this table, I'm trying to insert the following INSERT commands alternatively.

INSERT INTO temp_table (id, description) VALUES (1, null); ->unsuccessful
INSERT INTO temp_table (id, description) VALUES (2, '');   ->unsuccessful

Both of them are unsuccessful as obvious because the not null constraint is enforced on the DESCRIPTION column.

In both of the cases, Oracle complains

ORA-01400: cannot insert NULL into ("WAGAFASHIONDB"."TEMP_TABLE"."DESCRIPTION")

An empty string is treated as a NULL value in Oracle.

If I dropped the not null constraint on the DESCRIPTION column then the basic table structure would look like the following

+--------------+-----------+---------------+
|Name          | Null?     | Type          |
+--------------+-----------+---------------+
|ID            | NOT NULL  | NUMBER(35)    |
|DESCRIPTION   |           | VARCHAR2(4000)|
+--------------+-----------+---------------+

and both of the INSERT commands as specified would be successful. They would create two rows one with a null value and another with an empty string '' in the DESCRIPTION column of the TEMP_TABLE.

Now, if I issue the following SELECT command,

SELECT * FROM temp_table WHERE description IS NULL;

then it fetches both the rows in which one has a null value and the other has an empty string '' in the DESCRIPTION column.

The following SELECT statement however retrieves no rows from the TEMP_TABLE

SELECT * FROM temp_table WHERE description='';

It doesn't even retrieve the row which has an empty string in the DESCRIPTION column.

Presumably, it appears that Oracle treats a null value and an empty string '' differently here which however doesn't appear to be the case with the INSERT statement in which both a null value and an empty string '' are prevented from being inserted into a column with a not null constraint. Why is it so?

In oracle an empty varchar2 and null are treated the same, and your observations show that.

when you write:

select * from table where a = '';

its the same as writing

select * from table where a = null;

and not a is null

which will never equate to true, so never return a row. same on the insert, a NOT NULL means you cant insert a null or an empty string (which is treated as a null)