A null is basically used to represent the unknown and missing values. But it does not mean that the null value is equivalent to zero or a blank text string. See the example below for the same.
|Client ID||First Name||Middle initial||Last Name||City||Country|
|4||Halk||Null||Jamur||Sillyvelly|| Null |
|5||Yen||Null||hui||Newcity|| North Brazil|
How null values are created or inserted into the database?
It is good to have null values in the table whenever they are just for showing purpose like Middle name or stating some information. As you can see in the above client table null is useful to show the middle initial field and in the country field. If we want to use this null in a proper way then we should know the reason for their occurrence.
The very first reason for null values could be a manmade error. As while entering the data in the table, we forget to ask a particular client say John that which country does he live in. thus this missing data will be shown including a null value. You can see the country column for john in the above table.
There could be lots of reasons to fill a field with the value null. The value has not been defined for that field could be one of the reason. Let’s understand this scenario with an example related to the above table. Assume that we asked for john about his country information he is living in but unfortunately he does not know his country name and you also don’t have any idea about his country then we have to set a null value in his country column.
There could be another case where we need to insert the null value for a field. For example, in a company, an employee is working in both day and night shift, there are the fields total hour worked for the day and night shift both. Assume that he is at night shift for one month then his total hour worked in day shift will be always null for one month. So, in this case, null value come as per the condition.
Problems with null:
In general null value does not impact on anything until it is used just for showing the status of any field. But once that field is used in mathematical calculation with some other logical field then it can impact on the outcome result.
(50 x 3) + 10 = 160
(Null x 4) + 25 = Null
(20 x Null) + 500 = Null
With the help of below product table, you can see the impact of null value to a mathematical calculation.
|Product Code||Product Name||Product Category ||Price||stock||StockValue|
|1||Fight with Truth||Books||Null||10||Null|
|2|| Fight with Truth ||Books||30.95||5||154.75|
|4||HP Printer Ink Tank||Null||70||10||700.00|
|5||Videocon LCD with home theater||Audio/Visual||90||Null||Null|
|6||Hard Disc 1Tb||Device||55||15||825.00|
You can easily understand the problems due to null value by inspecting the value of above product table. Here stock value is derived by multiplication of price and stock field like[Price*stock]. So, for product 1 where the price is null, the stock value will always show null.
This problem can be more critical when you need to calculate the total value of all the item which are kept current in stock then you can not get an accurate result for the same.
to address this problem you need to make sure that price and stock field can’t be assigned as null.
See another example where null value is affecting an aggregate function.
Null values will also affect aggregate function that incorporates the value of a given field. If we were using an aggregate function, for example, a count function, the result will always contain a null if the field contains a null. The table below shows the results of performing a summary calculation that will count the number of occurrences of a category in the products table.
Let’s take an aggregate function which is counting the Product category column.
|CategoryDescription||Total no. of Occurrences|
After applying the aggregate function we can see the effect of null value clearly.
Here in the result of category calculus, we can observe that the occurrence of the unspecified category is 0, whereas if we see the product table then we can easily find that there are two product which does not show any category.
So, in this blog, you have learned how the null value inserted in the database table. It can be entered by human mistake and some time data is unknown at present and we leave that field and by default, it is filled with null. And some time information does not match a particular column and thus filled with null. And you have also read the effect of these null value in mathematical operation and aggregate function.
Hope! you would have enjoyed this blog post. Please share your reviews and comments with us. You can also write to us at email@example.com if you have any query regarding this blog.
Have a good time!