| # 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 | |