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

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.