Null Values Origin

Null Values can result in the following cases:

• When reading rows
• If the table queried from already contains NULL values
• When using outer joins
• When inserting or updating rows (INSERT and UPDATE Statements)
• If no value for a column is provided, NULL values are allowed, and no default values are defined
• If a NULL value is explicitly specified for a column and NULL values are allowed.
• When adding a column to a table (ALTER TABLE statement)
• If the table has at least one row, NULL values are allowed for the added column and no default value is defined for the added column

The introduction of the logical value “unknown” and the way expressions are evaluated by first evaluating their atomic sub-expressions lead to an unexpected result: Logical expressions that always evaluate to true in usual programming languages and according to common sense no longer evaluate to true in all cases. The following list shows some example:

• A = A
• (A<5) OR (A>=5)
• 0*A = 0
• 2*A = A+A
• A OR (NOT A)
• MAX (…) >= ALL (<Set of all values>)
• MIN (…) <= ALL (<Set of all values>)