Difference between NULL and SPACE

by Raghavender Vedakattu

Consider a database table, “ZEMPLOYEE”, with the following fields:

 Lets insert some records into this table.

Now let’s add a new field “DEPT (CHAR10)” to this table (without checking the “key” and “initial values” check boxes), and then activate the table. Since the newly added field is not a part of primary key, there would be no problems in activating the table.  

When the following two SELECT queries are executed, no records are retrieved even though many records satisfying the WHERE condition (General assumption what we make).

This is the SELECT query….
SELECT *
FROM ZEMPLOYEE
INTO IT_EMPLOYEE
WHERE DEPT EQ SPACE.

SELECT *
FROM ZEMPLOYEE
INTO IT_EMPLOYEE
WHERE DEPT NE SPACE.

Now the question arises, why are these queries failing for both the conditions (EQ/NE)? 

Here is the solution: 

When we insert a new field to an existing table, NULL values are automatically inserted into the new field by the system for all existing records. However NULL and SPACE are not the same. 

The logical expression SQL condition is either "true, false, or unknown". The expression is UNKNOWN if one of the columns involved in the database contains a NULL value and such records could be retrieved by using “IS NULL” instead of SPACE. 

Please note that the NULL value is inserted only for the existing records, by the time the new field is being inserted. For all new records, SPACE or the initial (default) value is inserted. 

If the new field is inserted by checking the checkbox “initial values”, then the initial values (SPACE in case of characters) are automatically inserted and not the NULL values.