blob: 527ec69e4deffa64368c17d1668b1a8412477e95 [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: IF
summary: Returns one of two values, depending on a condition.
syntax:
sql: IF(condition, true_value, false_value)
java: |
$("condition").then(trueValue, falseValue)
ifThenElse($("condition"), trueValue, falseValue)
python: |
col("condition").then(true_value, false_value)
if_then_else(col("condition"), true_value, false_value)
arguments:
- name: condition
type: BOOLEAN
required: true
description: The condition to check.
- name: true_value
type: Any type
required: true
description: The result if `condition` is `TRUE`.
- name: false_value
type: A type that has a common type with `true_value`
required: true
description: The result if `condition` is `FALSE` or NULL.
returns:
description: "`true_value` if `condition` is `TRUE`, else `false_value`."
nulls: Returns NULL if the chosen value is NULL.
type: For numeric, character string and binary values, their common type. For other values, the type of `true_value`. Nullable if any argument is nullable.
notes:
- Only the chosen value is evaluated.
- A NULL `condition` counts as `FALSE`, so the function returns `false_value`.
- For `ARRAY`, `MAP`, `ROW` or `VARIANT` values, use `CASE`. `CASE` also keeps the larger precision of two `TIME` or `TIMESTAMP` values, where `IF` uses the precision of `true_value`.
- The Table API calls accept any two types with a common type, including `ARRAY`, `MAP` and `ROW`, and keep the larger precision of two `TIMESTAMP` values.
see_also:
- {name: CASE, ref: conditional/case}
- {name: COALESCE, ref: conditional/coalesce}
examples:
- title: Choose between two values
description: Label each amount.
sql: |
SELECT amount, IF(amount > 100, 'large', 'small') AS size
FROM (VALUES (250), (40)) AS t(amount);
result: |
| amount | size |
|--------|-------|
| 250 | large |
| 40 | small |
- title: A NULL condition
description: A NULL condition returns `false_value`.
sql: |
SELECT IF(CAST(NULL AS BOOLEAN), 'yes', 'no') AS answer;
result: |
| answer |
|--------|
| no |
- title: Mixed numeric types
description: An `INT` and a `DECIMAL` value give a `DECIMAL` result.
sql: |
SELECT IF(b, 1, 2.5) AS val, TYPEOF(IF(b, 1, 2.5)) AS val_type
FROM (VALUES (TRUE)) AS t(b);
result: |
| val | val_type |
|-----|-------------------------|
| 1.0 | DECIMAL(11, 1) NOT NULL |
- title: TIMESTAMP values with different precisions
description: "`IF` uses the precision of `true_value` and drops the milliseconds. `CASE` keeps them."
sql: |
SELECT
IF(b, TIMESTAMP '2026-01-01 12:30:45', TIMESTAMP '2026-01-01 12:30:45.123') AS with_if,
CASE WHEN b THEN TIMESTAMP '2026-01-01 12:30:45' ELSE TIMESTAMP '2026-01-01 12:30:45.123' END AS with_case
FROM (VALUES (FALSE)) AS t(b);
result: |
| with_if | with_case |
|---------------------|-------------------------|
| 2026-01-01 12:30:45 | 2026-01-01 12:30:45.123 |