A Null represents an unknown or a missing value. It is important to understand that a null value does not mean a zero of a text string of one or more blank spaces.
When used for its stated purpose, null can be quite useful as shown in the Clients table in figure 1. The null values seen in the ClientMiddleInitial field and the ClientCounty field represent either missing or unknown values. In order to use a null correctly, you will need to understand why they occur:
Human error is commonly associated with the resultant missing values. An example of this may be seen if we consider the record for Henry Thomas in the above table. If you are entering the data for this record and you fail to ask the client for the County name they live in the data will then be considered missing and as such the record will be shown including a null. Once this error is realised however, you can then correct it by calling the client.
Unknown values can appear for a variety of reasons. An example of this may be that the value for a field has not yet been defined. On a student course database, there may be a Categories table that defines that type of courses available; you may add a new course to your list, but as yet there may not be a correct category defined that this course will fit in to. Another example of an unknown value is that it is truly unknown. If we refer back to the example concerning Henry Thomas contained in the Clients table; when entering the data for this record, you have asked him for the County that he lives in, he may not actually know the county name. If you also do not know the name of the county in question, then at this moment in time, the value is truly unknown. You can return to correct this problem when either of you determines the correct county name.
You may also have a case for a null value if you are working in a database and none of its values applies to a record. An example of this may be the Employees database and a table that contains an HourlyRate field and a Salary field. You may see null records here as the fact is the employee won't be paid a fixed salary and an hourly rate, therefore one will always be null.
You must also consider that there is a slim difference between "is not applicable" and "does not apply". In the previous example, "does not apply" is the correct definition as the value definitely can not apply to one or the other of the fields. As another example, suppose you are working in a database that includes a Patient table, and that the table includes a field relating to Hair Colour; if you are updating this and a patient recently lost all of their hair then the field will need to include "not applicable". You could use a Null value in this field, although it is recommendable to use a true value such as "N/A" or "Not Applicable", making information much clearer to the user.
Allowing Nulls in a table will depend on exactly how you are wishing to use the data.
Below deals with the negative elements of using Nulls within your database:
Including null values within your data can have an adverse effect when using this data within any mathematical operations. Any operation that includes a null value will result in a null; this being logical as if a value is unknown then the result of the operation will also be unknown. Below shows how using a null in a calculation will alter the outcome:
(100 x 3) + 10 = 310
(Null x 4) + 10 = Null
(20 x Null) + 100 = Null
(20 x 100) + Null = Null
Using the products table below, you can see the effects that including Nulls will have on any mathematical expressions you have used:
|001||Word 2002 Bible||Books||10|
|002||Access 2002 Bible||Books||29.99||5||149.95|
|003||28" TFT JVC Television||995.00|
|004||Nokia 310 Mobile Phone||65.00||10||650.00|
|005||14" Sanyo Portable TV||Audio/Visual||149.99|
|006||128mb Pen Drive||49.00||15||735.00|
Notice how including nulls in a table will effect mathematical operations
You can see an example of the problem that null values cause when looking at certain records in this table. In this table, the StockValue field derives its results by using the Price and the QuantityInStock values, i.e. [Price]*[QtyInStock]. You can see that for the first record, ProductCode 001, that where a user has not entered a value for the Price field (i.e. a Null) then the StockValue also shows as Null.
This can lead to more severe problems as this may go undetected. Suppose you need to calculate the value of all items that you currently have In Stock; you will not receive an accurate result as values will not be taken into account.
To ensure that the Stock Value can always be calculated, you must first ensure that the Price and the QtyInStock fields can never contain a NULL value.
A further example of the effects of null values can be seen below:
Null values will also effect aggregate functions that incorporate the values 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.
|CategoryDescription||Total No. Of Occurrences|
Null values will effect the results from an aggregate function
The Total No. Of Occurrences is the result of using the expression Count([CategoryDescription])
The table shows that there are "0" occurrences of the unspecified category, implying that each Product has been assigned to a category. This is clearly not the case, as viewing the Products table will show that there are two products that do not belong in any category at present.
Using all of this information, you should now be able to see that allowing missing or unknown values will have adverse effects on other things that you may wish to do with your data and should be considered when in the process of database design.