What is NULL?

Most of them always confused that NULL is false, absent of value. There are few properties of the NULL are universal but the knowledge of the same is not universal.

Let us run following very simple query. Run following T-SQL script.

SELECT SUM(data)FROM (SELECT NULL AS data) t

It will return following error:

Msg 8117, Level 16, State 1, Line 1
Operand data type NULL is invalid for sum operator.

Now error itself explain, here NULL is not the type of Integer by default. We must have to convert it in Integer by casting.

SELECT SUM(data)FROM (SELECT CAST(NULL AS INT) AS data) t

Now when we run this above query we will get NULL value as below.

Most the the SQL Developer convert NULL to 0 by using ISNULL function. But It will produce the different result.

SELECT SUM(data)FROM (SELECT ISNULL(NULL,0) AS data) t

Output would be as below.




Here I just want to explain that NULL can be converted in any type like int, varchar, decimal etc. and perform your action according the datatype query.

CAST(NULL AS INT)

CAST(NULL AS VARCHAR)

CAST(NULL AS DECIMAL)

One more interesting point regarding NULL is that when you add or concatenate anything with NULL, output would be NULL.

SELECT 5 + NULL

SELECT 5 + CAST(NULL AS INT)

OR

SELECT 'Anjum' + NULL

SELECT 'Anjum' + CAST(NULL AS varchar)

Output would be NULL.

I am confident that after reading the post you will have no confusion regarding NULL in future.