Null – Special Data Value

What is NULL ?

Null denotes that a data value which is not exist in the database. The term “NULL” has been coined by E.F.Codd.

Null usually represent Inapplicable or Missing information in the system. In SQL Server NULL is a keyword/reserved word.

 

Is Null as same as Zero ?

No, Missing or inapplicable information can’t be assumed as Zero value.

 

The price of a product can be a numeric  value or “Don’t know the price”. If we don’t know the value of price then it will be classified as null.

 

NULL Keyword

SQL server uses the keyword NULL to differentiate among other data values.

 

A simple example to understand about null

 

SQL  Code Snippet:

Declare @tabEmployee table (ID int, Namenvarchar(255), Department nvarchar(50), Salarydecimal(18,2))

 Insert into @tabEmployee (ID,Name,Department,Salary)

values (1000,’Sam’,’Sales’,50000),

(1001,’Mark’,’Operations’,Null),

(1002,’Tom’,Null,70000),

(1003,’Nancy’,”,90000)

Select ID ,Name,Department,Salary from@tabEmployee

 

Null for numeric data type:

Salary_Null

It has been observed that we don’t have the department information about the employee Mark. Hence a null has been presented to denote the missing information.

 

Null for string data type:

Department_Null

As we don’t know the  Department name for the employee Tom, it has been represented as NULL.

 

How to compare null value ?

As null is a special data value , it is not possible to compare using the equal sign.

Null_Compare

Hence we need a special operator to handle values with NULL. The “IS NULL” operator will be useful to compare against the NULL data values.

IS_NULL

The operator “IS NULL” can be used for string data types as well

IS_NULL_String

Is zero length string  or blank value is as same as Null ?

No,  The below mentioned query and result set confirms the fact that only nullable values can be compared using “IS NULL”.

NULL_Blank_String

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s