Null-Subquery

• To check whether a value of an attribute is NULL (atomic predicate)

WHERE attribute IS NULL
Copy

• Any arithmetic expression which has NULL values is evaluated as NULL.
  For example, this query is same as the above.
  SELECT * FROM staff WHERE (picture+1) IS NULL

Different aggregation functions treat NULL values differently.

• If NULL values and non-NULL values are mixed,

  • all aggregation functions ignore NULL values.
• If the group only consists of NULL values,

  • count returns 0;
  • other aggregation functions return NULL.

• 如果NULL与非NULL的属性混合 那么所有aggregation function会忽略 NULL

• 如果只有NULL，那么count() return 0,其他aggragation function return NULL

• The logic with NULL values has three constants:
  • TRUE, FALSE, and UNKNOWN 三值判断体系
• A comparison with NULL values is evaluated as UNKNOWN.
  To check whether a logical constant is UNKNOWN, use IS UNKNOWN.
  For example, try the query and check the outcome.

SELECT staff_id FROM staff WHERE (staff_rating>5) IS UNKNOWN

• 如果某个 attribute = NULL 那么他的逻辑判断结果 为 UNKNOWN

• OR:

  • (unknown OR true) = true
  • (unknown OR false) = unknown
  • (unknown OR unknown) = unknown
• AND:

  • (true AND unknown) = unknown
  • (false AND unknown) = false
  • (unknown AND unknown) = unknown
• NOT:

  • (NOT unknown) = unknown