Null Value in SQL

Null Value in SQL is the special value, allocated to an attribute in a relation that indicates, the information regarding the corresponding attribute is either unknown or 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:

• not known (i.e. value exist but we don’t have any information regarding that value)
• the value for a certain attribute 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.

1. Operations on NULL Values
2. IS NULL & IS NOT NULL
3. INSERT NULL Values
4. Replace NULLL 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.

1. 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 the expression above will result in null for that particular instructor.

2. 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) treat 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?

Is Null Operator

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:

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 Not Null Operator

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 who have non-null value for their salary.

3. 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 identical though we don’t know what does the null represent in both the tuples.

How to Insert NULL Values in SQL?

To Insert the NULL values into an attribute of an instance you just have to specify the NULL keyword into the VALUES clause of the INSERT statement Such as

INSERT INTO instructor(instructor_id, name, department, salary) VALUES (164, Shovel, Physics, Null);

This will insert an instructor instance with id 164, name Shovel, department Physics and his Salary attribute is assigned a NULL value.

How to Replace the NULL Values in SQL?

Well, you can always replace the NULL value of an attribute with a specific value using two SQL functions such as ISNULL() or COALESCE(). Let’s take the instructor table shown in the figure below has three NULL values for the attribute Salary.

Now if we want to replace the NULL value in the Salary column with the string “Not Decided” then we can do it using the ISNULL() function. Observe the command below:

SELECT instructor_id, Name, Department   ISNULL(Salary, ‘Not Decided’) As Salary From Instructor;

The command above will display the result as:

For replacing the NULL with some specified label you can use the COALESCE similar to ISNULL such as:

SELECT instructor_id, Name, Department   COALESCE(Salary, ‘Not Decided’) As Salary From Instructor;

This query will produce the same result as above ISNULL. On the other hand, the COALESCE function returns the first non-null value from its arguments. For Example:

SELECT COALESCE(NULL, NULL, Jenny, NULL, Biology);

Output: Jenny

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