SQL Server ISNULL in Snowflake, Databricks and BigQuery
SQL Server ISNULL(x, y) becomes IFNULL in Snowflake and BigQuery, and IFNULL or NVL in Databricks. Do not keep the name ISNULL: Snowflake has no ISNULL function, and Databricks isnull(x) is a one-argument test that returns a boolean.
SQL Server function: ISNULL(check_expression, replacement_value) · SQL Server docs
Equivalents
| Target | Support | Syntax | Notes | Source |
|---|---|---|---|---|
| Snowflake | Different name | IFNULL(expr1, expr2) | NVL(expr1, expr2) is the same function. COALESCE also works and accepts more than two arguments. | Snowflake docs |
| Databricks | Different name | ifnull(expr1, expr2) | nvl(expr1, expr2) is a synonym. Both are documented as coalesce with two arguments. The built-in isnull(expr) only returns true or false. | Databricks docs |
| BigQuery | Different name | IFNULL(expr, null_result) | COALESCE(expr, null_result) is equivalent. | BigQuery docs |
Behavior differences to check
- Return type: SQL Server returns the type of the first argument and may truncate the replacement, for example ISNULL(CAST(NULL AS VARCHAR(2)), 'abc') returns 'ab'. The target functions return a common type of both arguments, so results that relied on truncation change.
Databricks
- A query that keeps ISNULL(col, 0) fails on Databricks because isnull takes one argument. A query that uses ISNULL(col) as a condition still runs but means something else.
Examples
Default a missing discount to zero
SELECT order_id, ISNULL(discount, 0) AS discount FROM orders;SELECT order_id, IFNULL(discount, 0) AS discount FROM orders;SELECT order_id, ifnull(discount, 0) AS discount FROM orders;SELECT order_id, IFNULL(discount, 0) AS discount FROM orders;