Open In App

What is NULL ? Give an example to illustrate testing for NULL in SQL. What is dangling tuple problem ?

Improve
Improve
Like Article
Like
Save
Share
Report

What is NULL ?
In Structured Query Language Null Or NULL is a special type of marker which is used to tell us about that a data value does not present in the database. In Structured Query Language (SQL) Null is a predefined word which is used to identity this marker. It is very important to understand that a NULL value is totally different than a zero value.

In other words we can say that a NULL attribute value is equivalent of nothing that means in database there is an attribute that has a value which indicates nothing or Null, An attributes does not exist or we can say that it is missing . In database a Null value in tables is that value in the fields that appears to be blank. It is a field that has no value.

An Example to illustrate testing for NULL in SQL :
Suppose there is a table named as CUSTOMERS that having records as given below.

ID

NAME

AGE

ADDRESS

SALARY

1 RAJESH 45 INDORE 48000.00
2 ANURAG 40 UJJAIN 57000.00
3 MAYANK 38 BHOPAL 45000.00
4 GAURAV 23 PUNE 35000.00
5 DEEPAK 29 MUMBAI 28000.00
6 NAMAN 25 NOIDA  
7 AYUSH 33 GWALIOR  

Now we can use IS NOT NULL operator and write a query which is as following.

SQL> SELECT * 
FROM CUSTOMERS 
WHERE SALARY IS NOT NULL;

After execution this query would produce the following result-

ID

NAME

AGE

ADDRESS

SALARY

1 RAJESH 45 INDORE 48000.00
2 ANURAG 40 UJJAIN 57000.00
3 MAYANK 38 BHOPAL 45000.00
4 GAURAV 23 PUNE 35000.00
5 DEEPAK 29 MUMBAI 28000.00

Here we can see that in CUSTOMERS table , ID no. 6 and 7 which is named as NAMAN and AYUSH and their salary column is empty and in other words it is Null . That’s why after query execution it would produce a table where these two names NAMAN and AYUSH not present because we use IS NOT NULL operator.

Now we can use IS NULL operator and write a query.

SQL> SELECT * 
FROM CUSTOMERS 
WHERE SALARY IS NULL;

After execution this query would produce the following results-

ID

NAME

AGE

ADDRESS

SALARY

6 NAMAN 25 NOIDA  
7 AYUSH 33 GWALIOR  

Here we can that in CUSTOMERS table , ID no. 6 and 7 which is named as NAMAN and AYUSH and their salary column is empty and in other words it is Null. That’s why after query execution it would produce a table where these two names NAMAN and AYUSH not present because we use IS NULL operator.

What is Dangling tuple problem?
In DBMS if there is a tuple that does not participate in a natural join we called it as dangling tuple . It may gives indication consistency problem in the database.

Another definition  of dangling problem tuple is that a tuple with a foreign key value that not appear in the referenced relation is known as dangling tuple. In DBMS Referential integrity constraints specify us exactly when dangling tuples indicate problem. 


Last Updated : 03 Nov, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads