Teradata ZEROIFNULL in Snowflake, Databricks and BigQuery
Snowflake keeps ZEROIFNULL. Databricks and BigQuery have no such function; use COALESCE(x, 0) or IFNULL(x, 0).
Teradata function: ZEROIFNULL(arg) · Teradata docs
Equivalents
| Target | Support | Syntax | Notes | Source |
|---|---|---|---|---|
| Snowflake | Same name | ZEROIFNULL(expr) | Returns 0 if the argument is NULL, otherwise the argument. | Snowflake docs |
| Databricks | Rewrite | coalesce(expr, 0) | ifnull(expr, 0) and nvl(expr, 0) are equivalent. | Databricks docs |
| BigQuery | Rewrite | IFNULL(expr, 0) | COALESCE(expr, 0) is equivalent. | BigQuery docs |
Behavior differences to check
- NULLIFZERO, the inverse, is also Teradata-specific; Snowflake keeps NULLIFZERO, while Databricks and BigQuery need NULLIF(expr, 0).
Examples
Treat missing sales as zero
SELECT store_id, ZEROIFNULL(sales) AS sales FROM daily_sales;SELECT store_id, ZEROIFNULL(sales) AS sales FROM daily_sales;SELECT store_id, coalesce(sales, 0) AS sales FROM daily_sales;SELECT store_id, IFNULL(sales, 0) AS sales FROM daily_sales;