Page 60 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Unit 3: Structured Query Language

complete and any operation performed on null values may result into inconsistent data. In the Notes
following section we will discuss the problems that arises due to NULL value.

Comparison using NULL Values

When we compare valid values with NULL values boolean logic doesn’t come handy i.e., we
can’t use a boolean logic which is two valued - TRUE or FALSE. With NULL values we have to
use a then valued logic - TRUE or FALSE or UNKNOWN. For example, previous; we entered a
NULL value for salary attribute of Kelly. Now if apply condition that-list all the employees
whose salary > 20000, the evaluation of this condition for likely is UNKNOWN because salary
is a NULL value for Kelly. Similarly, for other comparison operators (>, <, =, <>), the evaluation
of the condition will always be unknown.
SQL provides is NULL comparison operator to compare to: NULL values i.e., to test whether a
value is NULL or not. If we apply this operator to salary attribute
IS NULLE.esal
then it will return a TRUE to indicate that esal attribute is a null value.

Logical Connectives AND, OR and NOT

Use of logical connectives with NULL values becomes a lit complicated if we doesn’t use three
valued logic. For example consider the following query.
Query : List all the employees whose age is less than 40 whose salary is grater than 30000.
Solution:
SELECT *

FROM employee E
WHERE E.age<40ANDE.esal>30000
All the tuples that satisfy this condition are selected but what about the tuple we inserted with
esal as NULL value? In this case, this condition will evaluate to unknown logical operators
which involves at least one column whose value is assigned as NULL will always result in an
unknown value. The following table will give you a better understanding of logical operators
when used with null values. Point to note here is that we are using a three valued logic TRUE,
FALSE or UNKNOWN i.e., the logical condition applied may evaluate to any one of them.
(Unknown is used in case of NULL values).

Operation Results and Reason
1. X AND Y * TRUE If both X and Y are true
* FALSE If either X or Y is false
* UNKNOWN If either X or Y is unknown (null values)
2. X OR Y * TRUE If either of them is true.
* FALSE If either of them is false
* UNKNOWN If one of the argument is, false and other is unknown
3. NOT X * TRUE If X is false
* FALSE If X is true
* UNKNOWN If X is unknown

LOVELY PROFESSIONAL UNIVERSITY 53