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.
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)
Select ID ,Name,Department,Salary from@tabEmployee
Null for numeric data type:
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:
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.
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.
The operator “IS NULL” can be used for string data types as well
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”.