Null Value in SQL

Null Value in SQL is the special value, allocated to an attribute in a relation which indicates that the information regarding the corresponding attribute is either unknown or it doesn’t even exist. In this section, we will discuss the null values in detail.

What is the Null Value in SQL?

The null value assigned to an attribute in a relation specifies that the value for the corresponding attribute is either is not known (i.e. value exist but we don’t have any information regarding that value) or the value doesn’t exist at all.

When a new attribute is added to an already existing relation using alter table command then all the tuples of that relation initially have a null value for that newly added attribute.

Well in certain cases, we restrict null value from being entered as a value to an attribute in a relation. Such as the primary key attribute value of a relation is required to be not null. Hence, the not-null constraint is added to the primary key attribute of the relation while it is created.

Allocating null values to attributes make a relation difficult to handle. It is preferred not to make frequent use of them as they create difficulties when we access or update the database. In the further section, we will discuss the problems with null values.

How does SQL treat null values?

The null values create a problem while performing a relational operation that includes arithmetic operation, comparison operation and set operation.

Arithmetic Operations:

If any arithmetic expression including these (+, -, *, or /) operators, have any of the input as null, will evaluate the result in null. For example, if we have to increment the salary of all the instructors by 100 $ then the expression would be:

instructor.salary+100

In case, for a particular instructor, the salary value is null then expression above will result in null for that particular instructor.

Comparison Operations:

The case is worse in comparison operations. Consider, if we have a comparison (1<null). Now, how could you judge that the comparison will turn out to be true or it would be false? This is because we don’t know what does null value exactly represents.

So, it is very difficult to evaluate the result of a comparison that has a null value for comparison. Therefore, SQL evaluates the result of any comparison that includes null value, as unknown. SQL treats unknown as a logical value as it treats true and false.

It may happen that the predicate in where clause includes Boolean operations to combine the results of comparisons. Therefore, the definition of Boolean operation must be extended in order to deal with the logical value ‘unknown’.

Let us see how Boolean operations (and, or, and not) treats unknown:

and:
true and unknown = unknown
false and unknown = false
unknown and unknown = unknown

or:
true or unknown = true
false or unknown = unknown
unknown or unknown = unknown

not:
not unknown = unknown

In the predicate, the comparison between null i.e. null=null returns ‘unknown’ instead of ‘true’. In case, the predicate in where clause evaluates the result as false or unknown for any tuple in a relation. Then that tuple is not counted in the result.

What is “is null” and “is not null” in SQL?

In SQL, ‘is null’ is used to test the predicate for the null value. For example, if we have to check for all those instructors who have their salary value equal to null in the instructor table below:

Instructor table

The query for this would be:

select name from instructor where salary is null;

As you can see in the image below this query will present the list of all the instructors who have a null value for the salary.

is null

Let us see the performance of ‘is not null‘ for the same query. Is not null is also used to test the predicate for the non-null values.

select name from instructor where salary is not null;

Now, this query will list all the instructors have non-null value for their salary.

is not null

Set Operations:

In the set operations, two tuples are treated as identical, if the values for corresponding attributes of both the tuples are either non-null and equal in value or if both are null.

For example, we have two tuples:

{(‘Steive’, ‘Biology’, null), (‘Steive’, ‘Biology’, null)}

Both the tuples above are treated as identical as the first and second attribute values of both the tuples are non-null and equal in value and the third attribute of both the tuple is null. They are considered as identical though we don’t know what does the null represent in both the tuples.

So, this was all about the null values in SQL, we have seen the definition of null, how SQL treats problem raised due to null values. We have also seen the variation in result evaluated when the predicate is containing ‘is null’ and ‘is not null’.

It is always advised not to resort the null values as they make operation on database complicated.

Leave a Comment

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