Interpreting NULL Values

NULL values can result in the following cases:

• When reading rows (the SELECT statement).

  • If the table queried from already contains NULL values.

  • When using outer joins.

• When inserting or updating rows (INSERT and UPDATE statements).

  • If NULLs are permitted in a column, no value for that column is provided, and no default value is 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.

By allowing NULL values, a third truth value is necessary. In addition to TRUE and FALSE there is also the truth value UNKNOWN in SQL. Any comparison with a NULL value other than the IS NULL or IS NOT NULL predicates results in the truth value UNKNOWN.

This applies to all value-based comparison operators: =, >, >=, <, <=, <>

Two-value logic is a subset of three-value logic. When NULL values are eliminated, the AND test simplifies.

When NULL values are eliminated, the OR test also simplifies.

The introduction of the logical value UNKNOWN and the way expressions are evaluated leads to an unexpected result. That is that 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 examples:

• 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>)

Do the following two SQL queries have the same result?

1. Code Snippet

   Copy codeSwitch to dark mode

   123
   SELECT Name, Overtime
       FROM Official
   WHERE Overtime <= 10 OR Overtime > 10;

2. Code Snippet

   Copy codeSwitch to dark mode

   12
   SELECT Name, Overtime
       FROM Official;

The two SQL queries have different results!

1. Only officials where OvertimeIS NOT NULL applies:

   Code Snippet

   Copy codeSwitch to dark mode

   123
   SELECT Name, Overtime
       FROM Official
   WHERE Overtime <= 10 OR Overtime > 10;

   NAME	OVERTIME
   Mr A	10
   Mr B	10
   Ms C	20
   Mr E	10
   Mr F	18
   Ms G	22

2. All officials (including those with: Overtime IS NULL )

   Code Snippet

   Copy codeSwitch to dark mode

   12
   SELECT Name, Overtime
       FROM Official;

   NAME	OVERTIME
   Mr A	10
   Mr B	10
   Ms C	20
   Ms D	?
   Mr E	10
   Mr F	18
   Ms G	22
   Ms H	?
   Mr I	?