Working with NULLS in SQL Server – PART 1

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:
IF NULL=NULL
SELECT 'EQUAL';
ELSE
SELECT 'NOT EQUAL';

The result set can be seen as:

1_Working_with_NULLS_in_SQL_Server_PART_1

  • 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:
IF NULL IS NULL
SELECT 'RIGHT';
ELSE
SELECT 'WRONG';

The result can be seen as:

2_Working_with_NULLS_in_SQL_Server_PART_1

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:
USE TEST
SELECT Name,City,State FROM dbo.Students2
WHERE State IS NULL
ORDER BY Name;

The result will be displayed as:

3_Working_with_NULLS_in_SQL_Server_PART_1

  • Now we will see how to use the IS NOT operator quickly by going through an example:
SELECT * FROM dbo.Students1
WHERE City IS NOT NULL

The result will be displayed as:

4_Working_with_NULLS_in_SQL_Server_PART_1

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 !

 

Regards

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

Avatar

About Piyush Bajaj

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; MCTS – SQL Server 2008, Database Development; and MCTS – SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.Please feel free to drop me any question online or offline, I will try to give you the best possible answer from my side.Right now I am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.

View all posts by Piyush Bajaj →

4 Comments on “Working with NULLS in SQL Server – PART 1”

  1. “Since the operators like ‘=’ and ‘’ can’t be check for Nulls….”

    The part I have quoted above from your blog is not totally true.By setting up Set Ansi_Nulls Off you can use use comparison operators to compare 2 NULL’s .

    1 Set Ansi_nulls Off
    2 Go
    3 Select 1 Where NULL = NULL

    There is a lot of confusion in the SQL Server team itself on ways to handle Null values.Really cannot believe the solution they came up with….Depreciating the setting of Ansi_Nulls to Off in next version.

    Also the NULL values behave wierdly when you have a unique constraint. A column having a unique key will not allow multiple NULL values while in the above example it does treat two NULL’s as equal.

    Strange…Isnt it !!!!

  2. Well not sure how did Microsoft suddenly woke up in 2011 after almost 20 years of SQL Servers first release to understand setting Ansi_Nulls is not a good option…Undecided

  3. Actually, Microsoft woke up to see that clearing the ANSI_NULLS option is not a good option. ANSI_NULLS will be the only option. If two values are unknown, there is simply no way to tell that they are equal, nor different. Comparing unknown values simply doesn’t make any sense at all.

Leave a Reply

Your email address will not be published. Required fields are marked *