NULL in SQL

 In SQL, T-SQL

 

NULL in SQL is very interesting “creature”.
First of all, it is important to understand that NULL is not a value, so an expression ‘NULL value’ is incorrect. NULL is a mark for missing value, but it is not a value itself.

Let me ilustrate this on some examples created in SQL Server.

 

Variable example

The anserw is ‘Unknown’, therefore we can see SQL basic characteristic.

nullvaluesql

SQL is based on mathematical predicate logic, however the logic is modified. In math there are only two possible values for predicate: true or false.
However in SQL three-valued predicate logic is available (True, False, Unknown). Unknown is a result of a fact that it is not possible to compare if a missing value is equal to another missing value.
This script will give exactly the same result if you will change line:
Again it is because SQL cannot distinguish if a missing value is equal to a known value

 

Table example

‘=’ operator
Let’s create sample table to show an example.

Table:
nullvalues3

First of all, if we will try to query all NULLs with such query:

There will be empty result, because as shown before NULL = NULL is giving Unknown outcome.
Exactly the same will be shown if we will try

Naturally, if you would like to see NULL you should remember to use expression IS NULL:

Result:
null4
IN operator
Ok, so let’s try to use IN operator instead.

But IN operator does not return a match with NULL because it is based on ‘=’ operator. You can write the same with ‘=’:

So the result are only people from HR.
null5

 

NOT IN operator

On the other hand, let’s take a look at NOT IN:

Result is empty, because at least one value is empty. It happens because you are trying to query “department” column which doesn’t equal unknown (<>).
The same result would appear if you would wrote:

 

Normally, if have a subquery you should always think about choosing NOT EXISTS over NOT IN.
More: https://technet.microsoft.com/en-gb/library/ms184297(v=sql.105).aspx

 

Note
It is important to remember that, this example was shown with
SET ANSI_NULLS ON
If, you will switch it off the query will generate different result.
You can read more here: https://msdn.microsoft.com/en-us/library/ms188048.aspx

 

But on the other hand…
Now – let’s assume we would like to make a headcount of people in the departments. Naturally we would write:

Result:
screen2

So, as you can see in such case NULL are counted in one group. We can say the values are grouped and the group ‘NULL’ is ‘Missing Values Group’.

Mateusz
Creator & author of blog.
Recommended Posts
Showing 7 comments
  • Kamil

    Very helpful! Nice examples.

  • Hello friends, its enormous piece of writing concerning cultureand
    fully defined, keep it up all the time.

  • Dominik

    Great post and examples. Short, precise and easy to understand.

    • Mateusz

      Thanks Dominik. I am glad that you liked it.

  • RosettaKeefe

    I see your page needs some fresh articles. Writing manually is time
    consuming, but there is tool for this task. Just search for; Digitalpoilo’s tools

  • Nathan Tofanelli

    It is truly a great and helpful piece of info. I’m happy that you simply shared this useful information with us. Please stay us up to date like this. Thanks for sharing.

Contact Me

You can contact me with this form. :)

Not readable? Change text.