blob: 800580bed34663475d58fed6a34dfa2ab2e5066a [file]
# 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 |