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.
Last Updated :
03 Nov, 2021
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...