Null Redux

• NULL can standing in for many different real-world situations: missing data, secret data, meaningless/inappropriate data, ...
• The hassles of dealing with NULL values are really the hassles of dealling with missing data.  They are real and unavoidable.
• Joins
• when 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.
• Keys
• "Key" 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 "UNIQUE".
• 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.  
• Grouping
• NULL is treated as a distinct value for forming groups
• Aggregates 
• functions avg(), sum(), min(), max() do not include nulls
• count(*) counts rows with nulls
• count(attribute) and count(distinct attribute) don't count nulls