[go: up one dir, main page]
Scribd
Upload
59 views2 pages

Diff BTW Null & Space

When a new field is added to an existing database table, the system automatically inserts NULL values into the new field for existing records. However, NULL and SPACE are not the same thing. Queries using WHERE clauses to check for equality (EQ) or inequality (NE) to SPACE will not return any records because the new field contains NULL values rather than SPACE. To retrieve records with a NULL value, the query should use "IS NULL" instead of checking for SPACE.

Uploaded by

Partha Sarathi Chattopadhyaya
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
59 views2 pages

Diff BTW Null & Space

When a new field is added to an existing database table, the system automatically inserts NULL values into the new field for existing records. However, NULL and SPACE are not the same thing. Queries using WHERE clauses to check for equality (EQ) or inequality (NE) to SPACE will not return any records because the new field contains NULL values rather than SPACE. To retrieve records with a NULL value, the query should use "IS NULL" instead of checking for SPACE.

Uploaded by

Partha Sarathi Chattopadhyaya
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 2

Difference between NULL and SPACE

by Raghavender Vedakattu

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

MANDT
EMPNO
EMPNAME
EMPSAL.

 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.

You might also like