Hey Folks,

You might be afraid of using NULLS in your programming code because nulls have such a devastating effect on expressions; some developers avoid using of nulls.

They develop their databases as if the NULLS are never permitted, and column defaults supply surrogate nulls like Blanks, 0, or ‘N/A’ instead.

NULL indicates that the data has not been entered into the database or that the column does not apply to the particular row. Since NULL values are unknown or “value absent”, the result of any expression that includes null will also have a value that is unknown.

NULLS are valuable in a database because they provide a consistent method of identifying missing data and certain queries also produce Nulls in the results, so it’s worth writing codes that checks for Nulls and handle them appropriately.

In SQL Server, one of the advantage of using Nulls is that the functions like AVG () and COUNT () aggregate functions automatically exclude Nulls from their calculation. But if we use a surrogate nulls then every aggregate query must have to filter out the surrogate null or the results will be less than accurate.

Testing for NULL:

  • This Test shows that Null does not equals Null:

The result set can be seen as:


  • Since the operators like ‘=’ and ‘<>’ can’t be check for Nulls, SQL includes two special operators for this purpose: IS and IS NOT. For e.g., using the IS operator:

The result can be seen as:


You can see that IS proves to be a valid operator.

  • It will be more clear, if you go through this example, here we are finding out those students whose State is NULL:

The result will be displayed as:


  • Now we will see how to use the IS NOT operator quickly by going through an example:

The result will be displayed as:


Well I think this might be enough for this post, in the next blog post I would like you to tell how to Handle the NULLs and many more…

Hope you like my post :)

So Stay Tuned !



Piyush Bajaj

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook