# Do arithmetic that tolerates nulls and zeros

Shape / columns

A variant of [Apply a maths function](https://meridian.online/operations/apply-a-maths-function).

Add or subtract with nulls treated as zero, or divide with null returned when the denominator is zero.

Page: <https://meridian.online/operations/apply-a-maths-function/null-safe-arithmetic>

## Status

Runs as a SQL step.

arcform carries it as a `sql:` step today.

## The DuckDB step

add and subtract with nulls as zero, and divide with null for a zero denominator

```sql
CREATE TEMP TABLE pairs AS SELECT * FROM (VALUES
  (1, 10, 4), (2, NULL, 4), (3, 10, NULL), (4, 10, 0), (5, 0, 0), (6, NULL, NULL)
) t(id, a, b);
-- a plain 10 // 0 stops with an error on 2.0, and 10 / 0 on doubles gives infinity; nullif turns the zero into a missing value
CREATE TEMP TABLE result AS
SELECT id, coalesce(a, 0) + coalesce(b, 0) AS safe_sum, coalesce(a, 0) - coalesce(b, 0) AS safe_difference,
       a // nullif(b, 0) AS safe_whole, a / nullif(b, 0) AS safe_ratio FROM pairs;
SELECT * FROM result ORDER BY id;
SELECT CASE WHEN ((SELECT list(safe_sum ORDER BY id) FROM result) = [14, 4, 10, 10, 0, 0] AND (SELECT list(safe_difference ORDER BY id) FROM result) = [6, -4, 10, 10, 0, 0] AND (SELECT list(safe_whole ORDER BY id) FROM result) = [2, NULL, NULL, NULL, NULL, NULL] AND (SELECT list(safe_ratio ORDER BY id) FROM result) = [2.5, NULL, NULL, NULL, NULL, NULL]) THEN 'as expected' ELSE error('nulls were not treated as zero in the sum and difference, or a zero or missing denominator did not give a null ratio') END AS verdict;
```

The statement builds its own data.

It needs nothing installed beyond DuckDB itself.

## What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

- Bruin: [`bruin.safe_add(fields)`](https://getbruin.com/docs/bruin/assets/templating/macros.html) (read 2026-09-27)
- Bruin: [`bruin.safe_divide(numerator, denominator)`](https://getbruin.com/docs/bruin/assets/templating/macros.html) (read 2026-09-27)
- Bruin: [`bruin.safe_subtract(fields)`](https://getbruin.com/docs/bruin/assets/templating/macros.html) (read 2026-09-27)
- dbt-utils: [`safe_add`](https://raw.githubusercontent.com/dbt-labs/dbt-utils/main/README.md) (read 2026-09-27)
- dbt-utils: [`safe_divide`](https://raw.githubusercontent.com/dbt-labs/dbt-utils/main/README.md) (read 2026-09-27)
- dbt-utils: [`safe_subtract`](https://raw.githubusercontent.com/dbt-labs/dbt-utils/main/README.md) (read 2026-09-27)
- SQLMesh: [`@SAFE_ADD`](https://github.com/SQLMesh/sqlmesh/blob/23b237cea7218cc8f338b89c14f0957394a0003b/docs/concepts/macros/sqlmesh_macros.md) (read 2026-09-27)
- SQLMesh: [`@SAFE_DIV`](https://github.com/SQLMesh/sqlmesh/blob/23b237cea7218cc8f338b89c14f0957394a0003b/docs/concepts/macros/sqlmesh_macros.md) (read 2026-09-27)
- SQLMesh: [`@SAFE_SUB`](https://github.com/SQLMesh/sqlmesh/blob/23b237cea7218cc8f338b89c14f0957394a0003b/docs/concepts/macros/sqlmesh_macros.md) (read 2026-09-27)