---

SQL: Three Value Logic

---

• 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 ?

  • Answer:

    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