SQL NULL Values
In SQL there may be some records in a table that do not have values or data for every field. This could be possible because at a time of data entry information is not available. So 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. SQL places a NULL value in the field in the absence of a user-defined value. 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.
Importance of NULL value:
- It is important to understand that a NULL value is different from a zero value.
- A NULL value is used to represent a missing value, but that it usually has one of three different interpretations:
- The 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.
Principles of NULL values:
- Setting a NULL value is appropriate when the actual value is unknown, or when a value would not be meaningful.
- A NULL value is not equivalent to a value of ZERO if the data type is a number and is not equivalent to spaces if the data type is character.
- A NULL value can be inserted into columns of any data type.
- A NULL value will evaluate NULL in any expression.
- Suppose if any column has a NULL value, then UNIQUE, FOREIGN key, CHECK constraints will ignore by SQL.
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.
Now, consider the following Employee Table,
Suppose if we find the Fname, Lname of the Employee having no Super_ssn then the query will be:
SELECT Fname, Lname FROM Employee WHERE Super_ssn IS NULL;
Now if we find the Count of the number of Employees having Super_ssn.
SELECT COUNT(*) AS Count FROM Employee WHERE Super_ssn IS NOT NULL;