Nulls

The null type represents a type that has just one value: the special value null.

In relational SQL, a null typically indicates an unknown value. Unfortunately, this concept is overloaded as unknown values may arise from runtime errors, missing data, or an intentional value of null.

Because SuperSQL has first-class errors (obviating the need to serialize error conditions as nulls) and sum types (obviating the need to flatten sum types into columns and occupy the absent component types with nulls), the use of null values is discouraged.

That said, SuperSQL supports the null value for backward compatibility with their pervasive use in SQL, database systems, programming languages, and serialization formats.

As in SQL, to test if a value is null, it cannot be compared to another null value, which by definition, is always false, i.e., two unknown values cannot be known to be equal. Instead the IS NULL operator or coalesce function should be used.

Examples

The null value

# spq
values typeof(null)
# input

# expected output
<null>

Test for null with IS NULL

# spq
values
  this == null,
  this != null,
  this IS NULL,
  this IS NOT NULL
# input

# expected output
null
null
true
false

Missing values are not null values

# spq
values {out:y}
# input
{x:1}
{x:2,y:3}
null
# expected output
{out:error("missing")}
{out:3}
{out:error("missing")}

Use coalesce to easily skip over nulls and missing values

# spq
const DEFAULT = 100
values coalesce(y,x,DEFAULT)
# input
{x:1}
{x:2,y:3}
{x:4,y:null}
null
# expected output
1
3
4
100