SQL supports a special value known as NULL which is used to represent the values of attributes that may be unknown or not apply to a tuple. For example, the Apartment_number attribute of an address applies only to address that are in apartment buildings and not to other types of residences.
- It is important to understand that a NULL value is different from zero value.
- A NULL value is used to represent a missing value, but that it usually has one of three different interpretations:
- Value unknown (value exists but is not known)
- Value not available (exists but is purposely withheld)
- Attribute not applicable (undefined for this tuple)
- It is often not possible to determine which of the meanings is intended. Hence, SQL does not distinguish between the different meanings of NULL.
In general, each NULL value is considered to be different from every other NULL in the database. When a NULL is involved in a comparison operation, the result is considered to be UNKNOWN. Hence, SQL uses a three-valued logic with values True, False and Unknown. It is, therefore, necessary to define the results of three-valued logical expressions when the logical connectives AND, OR, and NOT are used.
How to test for NULL Values ?
SQL allows queries that check whether an attribute value is NULL. Rather than using = or to compare an attribute value to NULL, SQL uses IS and IS NOT. This is because SQL considers each NULL value as being distinct from every other NULL value, so equality comparison is not appropriate.
Consider the following Employee Table,
- Find the Fname, Lname of the Employee having no Super_ssn.
SELECT Fname, Lname FROM Employee WHERE Super_ssn IS NULL;
- Find the Count of the number of Employee having Super_ssn.
SELECT COUNT(*) AS Count FROM Employee WHERE Super_ssn IS NOT NULL;
This article is contributed by Anuj Chauhan. If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to email@example.com. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.
Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.