Null Subquery

Null-Subquery

NULL

  • The domain of each data type contains a special value – NULL.
    • NULL means the value is unknown or does not exist.
    • NULL values can participate calculations in the arithmetic expressions
    • aggregation functions, comparisons, and predicates (logical test) in the WHERE clause.

Arithmetic and Atomic Predicate

  • To check whether a value of an attribute is NULL (atomic predicate)
WHERE attribute IS NULL
  • 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

Aggregation

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

Comparison and Compound Predicates

  • 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

Subquery

  • A subquery is a query (SELECT…FROM…WHERE) inside another query.

Subqueries can be in all of SELECT, FROM, and WHERE clauses.

In the FROM clause, users need to indicate some tables. A “SELECT…FROM…WHERE…” query returns a temporary table. (The type is matched.)

BackLink