Teradata NULLIFZERO in Snowflake, Databricks and BigQuery
Snowflake keeps NULLIFZERO and Databricks Runtime 16.0 added it. BigQuery uses NULLIF(x, 0), which also works everywhere else.
Teradata function: NULLIFZERO(arg) · Teradata docs
Equivalents
| Target | Support | Syntax | Notes | Source |
|---|---|---|---|---|
| Snowflake | Same name | NULLIFZERO(expr) | Returns NULL if the argument is 0. | Snowflake docs |
| Databricks | Same name | nullifzero(expr) | Databricks Runtime 16.0 and above; the docs do not list it for Databricks SQL warehouses. | Databricks docs |
| BigQuery | Rewrite | NULLIF(expr, 0) | Standard SQL. | BigQuery docs |
Behavior differences to check
- NULLIFZERO is mostly used to avoid division by zero. BigQuery also offers SAFE_DIVIDE(x, y), which returns NULL when y is 0.
Examples
Conversion rate without dividing by zero
SELECT orders / NULLIFZERO(visits) AS conversion FROM daily_stats;SELECT orders / NULLIFZERO(visits) AS conversion FROM daily_stats;SELECT orders / nullif(visits, 0) AS conversion FROM daily_stats;SELECT SAFE_DIVIDE(orders, visits) AS conversion FROM daily_stats;