Null in SQL :

In my previous articles i have given different examples of NVL functions which is related to Null value in SQL.In this article i would like to give you the brief introduction about Null in SQL. I would also like to give you the examples of Null value in SQL so that all users will get idea about it. With these examples i will also try to explain about the different usages of Null value in SQL.The Null value is really very important special value in SQL.So many users thinks that null value and zero value is same;but the fact is that Null value is special value in SQL which is different than zero value.

What is null value in SQL with Examples?

In this section i will give you what exactly the null value is and how user can use the null in SQL.The null value is special value in SQL which represents the value in attribute is unknown or value yet to define.

Following are some bullet-points related to null value in SQL :

1.Null value is a special value which defines Nothing or you can say it as ‘No Value’

Real Life Example :

If there Student table have marks column and the result is yet to come.In that case all values in marks column is not zero.It will be define as null.

2.The above interpretation is correct but i would like to give you three different types of interpretations with null value in SQL.

2.1.When the Value of Attribute is Unknown?

Sometimes the user does not know the value of the attribute.To define that attribute value the null value is used.

2.2.When the value not available?

Sometimes there are scenarios where value of the attribute is not available.

2.3.Attribute not applicable

Sometimes there are scenarios where attributes were used in past but now there is no need of those attributes. These kind of situations the null value is used.

3.When user tries to compare column with null operator the answer of this is always ‘Undefined’.

Mistake done by Developers?

I would like to give you the basic mistake of the developers regarding null value in SQL.So may SQL developers are using the != as well as = operator in where clause. These operators needs to use when there is not null constraint. If there are null values in SQL then user will find wrong results possibly.The counting null values are very confusing.

Real Example :

When there are 4 values in student table’s marks column and one value is null.I would like to give you the following examples for analyzing the same.

Select count(marks) from Student;

The above query will return 3 but in table there are 4 values.So this is the key mistake the developers.

Interview Question Related to Null in SQL :

In so many interview question you will find out the different scenarios with Null in SQL.The most basic scenario i will explain so that user can get information about the interview questions related to Null in SQL.

Real life scenario of Null value in SQL :

Create table Student(Roll_No Number(10));

Table created

Insert into Student(1);

commit;

Insert into Student(2);

commit;

Insert into Student(null);

commit;

So we have student table with 3 values. Following should be the interview questions

Question 1 : What will be the output of following Query?

Query :

Select * from Student where roll_no != 1;

Most of SQL developers will give the answer that this will return the value null and 2 but they are wrong. The above query will return only one value i.e. 2.

Question 2 : What will be the output of following Query?

Query :

Select * from Student where roll_no != 1 or roll_no is null;

This query will return two values which is 2 and null.

The Following table will give you the information about the values with null.Here the Unknown value is considered as Null value.

Question 3 : What will be the output of following Query?

Query :

Select * from Student where roll_no != 1 or roll_no=null;

This is another tricky question for the developers. Most of developers gives answer as null and 2.But the answer is wrong the answer is only one value i.e.2

Question 4 : What will be the output of following Query?

Query :

Select * from Student where roll_no != null;

This question is another tricky question. Most of developers will give the answer as 1 and 2 but this is absolutely wrong answer. This will return zero as answer.

Question 5 : What will be the output of following Query?

Query :

Select * from Student where roll_no is not null;

This will return the values as 1 and 2.

These are some important as well as tricky examples of Null value in SQL.If you like this article or if you have any issues or concerns with the same kindly comment in comment section.