CS457 Syllabus & Progress

## SQL: Three Value Logic

Handling NULL value in SQL is a royal pain in the butt, so avoid it if you can... if you can't, here are the details...

• Meaning of NULL.

• The NULL value can be used for several meanings :

 Unknown: For example, when the birthday of an employee is unknown at the time, you can represent it with NULL Unavailable: For example, if an employee has a private phone number that he/she does not want it listed, it can be represented with NULL Not applicable: For example, if the employee relation has an attribute "degree" but some employee does not have any degree, it can be represented with NULL

• It is often not possible to determine WHICH of the three meanings is intended

• Meaning matters

• Suppose you want to find all employee who has a Ph.D. degree

• Question:

 Do any employee with NULL qualify ?

 If NULL means unknown: maybe If NULL means unavailable: maybe If NULL means not applicable: NO

So there is ambiguity on what to do when an attribute value is NULL

• Processing of NULL values.

• Some rules on NULL

 SQL does not (cannot ?) distinguish between the different meanings of NULL SQL consider each NULL value as different from every other NULL value Consequence: So when two (different) tuples have a NULL value for some attribute, these two NULL values are NOT equal When NULL is involved in a comparison operation , the outcome is equal to NULL (UNKNOWN)

• Because a comparison operation can result in a THIRD value (normally, comparison operations only returns: true or false , not UNKNOWN ), we say that:

 SQL uses a three value logic system

• The logical values in SQL are:

• TRUE
• FALSE
• UNKNOWN

• The Three Value Logic.

• the three value logic AND operation:

AND TRUE FALSE UNKNOWN
TRUE TRUE FALSE UNKNOWN
FALSE FALSE FALSE FALSE
UNKNOWN UNKNOWN FALSE UNKNOWN

• the three value logic OR operation:

OR TRUE FALSE UNKNOWN
TRUE TRUE TRUE TRUE
FALSE TRUE FALSE UNKNOWN
UNKNOWN TRUE UNKNOWN UNKNOWN

• the three value logic NOT operation:

NOT TRUE FALSE UNKNOWN
---> FALSE TRUE UNKNOWN

• Other Effects of NULL values

• There are a number of undesirable consequences of having NULL values in attributes

• Examples:

 A join will only select a tuple when the condition evaluates to TRUE (UNKNOWN will not output any tuples; some of these tuples may have been selected. You don't get the full result...) More importantly, the result of many set functions will be affected , e.g., MAX , MIN and AVG will return NULL when some value is NULL

• Testing for NULL values

• Syntax:

 attr IS NULL

• Example:

 ``` SELECT * FROM Employee WHERE BDate IS NULL ```