- NULL can standing in for many different real-world situations: missing data, secret data, meaningless/inappropriate data, ...
hassles of dealing with NULL values are really the hassles of dealling
with missing data. They are real and unavoidable.
you condition a join on attribute equality or use natural join, columns
with null in the joined columns don't match with anything.
- when you use outer-joins, nulls pad unmatched rows (See textbook for details).
- 3-valued logic
- comparisons involving NULL result in the logical value UNKNOWN
- Meaning of AND/OR/NOT well defined for UNKNOWN truth values.
- If a condition in a WHERE or HAVING clause returns UNKNOWN for a row, that row is not returned.
in class means no 2 rows can have the same vaules for all attributes in
a key. In DB implementations, this is covered by both "PRIMARY KEY" and
- You cannot have NULLs anywhere in a PRIMARY KEY (Oracle, MySQL, Postgres).
- You can have NULLs in a UNIQUE attribute or set of attributes.
- Duplicate elimination using SELECT DISTINCT
- NULL is treated as a distinct value when eliminating duplicates.
- NULL is treated as a distinct value for forming groups
- functions avg(), sum(), min(), max() do not include nulls
- count(*) counts rows with nulls
- count(attribute) and count(distinct attribute) don't count nulls