by Raj Kumar

Created on: August 10, 2009   Last Updated: August 11, 2009

Null values serve important purpose and essential role in storing special data in rows and columns of a database table. We can come across null values at a variety of places involving SQL data manipulation in database objects like tables, views, stored procedure, triggers and SQL queries along with different types of built-in and user defined functions. Null values can impact the SQL result set. Hence extreme care should be taken while dealing with null values in terms of financial or arithmetic calculations contained by SQL statements.

Null values in any database are specially handled. Impact of null values should not be ignored. Special treatment is needed because of some natural facts. One null value can not be compared with another null value. Basic data types like integer, char, decimal or objects can be checked if they have been assigned any value. If no value is assigned, they simply contain null value. So the null value can be checked with IsNull() function which is one of the built-in SQL functions. If the value is null, IsNull() function returns "TRUE", otherwise "FALSE". A normal unique and indexed column can have only one null value if it is not the primary key column. No null value is allowed in the column on which primary key constraint is defined. As the null value means absence of value, it implies that no value has been provided. Some beginners have misconception that null value is like zero. But zero has a value whereas null doesn't have any value. Hence null is completely different from the number zero.

In database tables, several columns are defined as "NOT NULL". This constraint means that such column doesn't allow null value. If someone tries to forcefully insert or update null value in such column, error message is thrown describing that null value can not be stored in "NOT NULL" column. To avoid this type of error, default value is generally assigned to such column while creating it along with the database table. This part can also be taken care if that column is updated using stored procedure, special updating view or triggers. In that case, desired constant or default value can be specified for "NOT NULL" column through SQL data manipulation commands during addition of new rows or modification of existing value.

SQL queries and built-in functions viz. Aggregate function like AVG (which calculates average) work differently if null values are involved. They give different output when dealing with null parameters as compared to non null values during average calculation for range of mixed values. SQL queries return results with null value rows corresponding to no match found in one of the joined table based on "LEFT OUTER" and "RIGHT OUTER" join. "INNER" or "EQUI" joins ignore null values and return only matching rows from the tables involved in those type SQL join. Null values have pros and cons depending on the situation in which they are used in database table. They can also impact the financial results if not properly operated. Their involvement may misguide the user from actual output. Thus null values should be involved knowingly as per the requirement.

Learn more about this author, Raj Kumar.
Click here to send this author comments or questions.