blob: 3efc8b7572cbf45101ca3ca0be7342a93b541435 [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: NULLIF
summary: Returns NULL if two values are equal, and the first value otherwise.
syntax:
sql: NULLIF(value1, value2)
arguments:
- name: value1
type: Any type that `=` can compare with `value2`
required: true
description: The value to return.
- name: value2
type: The type of `value1`, or one comparable with it
required: true
description: The value that turns `value1` into NULL.
returns:
description: NULL if `value1` equals `value2`, else `value1`.
nulls: Returns NULL if `value1` equals `value2` or if `value1` is NULL. A NULL `value2` never equals `value1`, so the function returns `value1`.
type: The type of `value1`, always nullable.
notes:
- "`NULLIF(value1, value2)` is short for `CASE WHEN value1 = value2 THEN NULL ELSE value1 END`."
- There is no Table API method. Use `callSql("NULLIF(a, b)")` in Java or `call_sql("NULLIF(a, b)")` in Python.
see_also:
- {name: CASE, ref: conditional/case}
- {name: COALESCE, ref: conditional/coalesce}
examples:
- title: Turn a placeholder into NULL
description: "`-1` marks a missing score."
sql: |
SELECT score, NULLIF(score, -1) AS known_score
FROM (VALUES (80), (-1)) AS t(score);
result: |
| score | known_score |
|-------|-------------|
| 80 | 80 |
| -1 | NULL |
- title: NULL arguments
description: A NULL `value2` returns `value1`. A NULL `value1` returns NULL.
sql: |
SELECT
NULLIF(5, CAST(NULL AS INT)) AS null_value2,
NULLIF(CAST(NULL AS INT), 5) AS null_value1;
result: |
| null_value2 | null_value1 |
|-------------|-------------|
| 5 | NULL |