| # Licensed to the Apache Software Foundation (ASF) under one | |
| # or more contributor license agreements. See the NOTICE file | |
| # distributed with this work for additional information | |
| # regarding copyright ownership. The ASF licenses this file | |
| # to you under the Apache License, Version 2.0 (the | |
| # "License"); you may not use this file except in compliance | |
| # with the License. You may obtain a copy of the License at | |
| # | |
| # http://www.apache.org/licenses/LICENSE-2.0 | |
| # | |
| # Unless required by applicable law or agreed to in writing, | |
| # software distributed under the License is distributed on an | |
| # "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY | |
| # KIND, either express or implied. See the License for the | |
| # specific language governing permissions and limitations | |
| # under the License | |
| name: IFNULL | |
| summary: Returns a replacement if a value is NULL, and the value otherwise. | |
| syntax: | |
| sql: IFNULL(input, null_replacement) | |
| java: $("input").ifNull(nullReplacement) | |
| python: col("input").if_null(null_replacement) | |
| arguments: | |
| - name: input | |
| type: Any type | |
| required: true | |
| description: The value to return if it isn't NULL. | |
| - name: null_replacement | |
| type: A type that has a common type with `input` | |
| required: true | |
| description: The value to return if `input` is NULL. | |
| returns: | |
| description: "`input` if it isn't NULL, else `null_replacement`." | |
| nulls: Returns NULL if both arguments are NULL. | |
| type: The common type of both arguments. NOT NULL if either argument is NOT NULL. | |
| notes: | |
| - "`IFNULL(input, null_replacement)` returns the same value as `COALESCE(input, null_replacement)`." | |
| - "`null_replacement` is evaluated only if `input` is NULL." | |
| see_also: | |
| - {name: COALESCE, ref: conditional/coalesce} | |
| - {name: NULLIF, ref: conditional/nullif} | |
| examples: | |
| - title: Replace NULL | |
| description: Use `'n/a'` where the name is missing. | |
| sql: | | |
| SELECT id, IFNULL(name, 'n/a') AS name | |
| FROM (VALUES (1, 'Alice'), (2, CAST(NULL AS STRING))) AS t(id, name); | |
| result: | | |
| | id | name | | |
| |----|-------| | |
| | 1 | Alice | | |
| | 2 | n/a | | |
| - title: The result type | |
| description: A NOT NULL replacement gives a NOT NULL result, in the common type of both arguments. | |
| sql: | | |
| SELECT TYPEOF(IFNULL(n, CAST(0 AS BIGINT))) AS result_type | |
| FROM (VALUES (CAST(NULL AS INT))) AS t(n); | |
| result: | | |
| | result_type | | |
| |-----------------| | |
| | BIGINT NOT NULL | |