# Nullability

Null values are used to express a value that isn’t yet known.

Null values can be found in the data present in existing sources or the product of joining data using non-inner joins.

The schema of nullable types is represented as a union of the field type and a null value:

```json
{
  "type": "record",
  "name": "record",
  "namespace": "example",
  "doc": "A schema representing a nullable type.",
  "fields": [
    {
      "name": "property",
      "type": [
        "null",
        "double"
      ],
      "doc": "A property that can be null or a double."
    }
  ]
}
```

### Using nullable types  <a href="#using-nullable-types" id="using-nullable-types"></a>

Working with null values can create situations where it’s not clear what the outcome of an operation is.

One example of this would be the following:

```properties
null * 1 = ?
null + 1 = ?
```

Looking at the first two expressions, one may be tempted to solve the problem above by saying, “Null is 1 when multiplying and 0 when summing,” meaning the following would be the evaluation result:

```properties
null * 1 = 1
null + 1 = 1
```

Rewriting the third expression applying the distributive property of multiplication, however, shows that the rule creates inconsistencies:

```properties
(null + 1) * null = (null + 1) * null <=>
null * null + 1 * null = (null + 1) * null <=>
1 * 1 + 1*1 = (0 + 1) * 1 <=>
1 + 1 = 1 * 2 <=>
1 = 2  //not valid
```

To avoid scenarios like the above, where computation may have different results based on the evaluation approach taken, most operations in lenses do not allow operations to use nullable types.

### Address nullability  <a href="#address-nullability" id="address-nullability"></a>

Lenses provide the following tools to address nullability flexibly:

#### COALESCE  <a href="#coalesce" id="coalesce"></a>

* **Coalesce**: A function that allows specifying a list of fields to be tried until the first non-null value is found.
  * Note: the coalesce function won’t verify if a non-nullable field is provided, so an error may still be thrown if all the provided fields are null
  * e.g.:`COALESCE(nullable_fieldA, nullable_fieldB, 0)`

#### AS\_NON\_NULLABLE  <a href="#as_non_nullable" id="as_non_nullable"></a>

* **AS\_NON\_NULLABLE**: a function that changes the property type from nullable to non-nullable.
  * Note: This function is **unsafe** and will throw an error if a null value is passed. It should only be used if there’s a guarantee that the value won’t ever be null (for instance, if used in a CASE branch where the null case has been previously handled or if the data has been filtered and the null values removed).
  * e.g.: `AS_NON_NULLABLE(nullable_field)`

#### AS\_NON\_NULLABLE and CASE  <a href="#as_non_nullable-and-case" id="as_non_nullable-and-case"></a>

* **AS\_NON\_NULLABLE** with **CASE**: A type-checked construct equivalent to using coalesce:
  * e.g.:

```sql
CASE
    WHEN a_nullable_field IS NULL THEN 0
    ELSE AS_NON_NULLABLE(a_nullable_field)
END
```

#### AS\_NULLABLE  <a href="#as_nullable" id="as_nullable"></a>

The `AS_NULLABLE` the function is the inverse transformation of the `AS_NON_NULLABLE` version. This function transforms a non-nullable field type into a nullable type. It can be used to insert data into existing topics where the schema of the target field is nullable.