IF

Syntax

IF condition_1
    THEN result_1
[ ELSEIF condition_2
    THEN result_2
  ... ]
ELSE
    default_result
END

IF(
    condition_1, result_1,
  [ condition_2, result_2,
    ... ]
    default_result
)

Description

Checks conditional expressions condition_1, result_1, ... and returns the matching result for the first condition found to be TRUE. IF all conditional expressions are FALSE, it returns default_result. The expressions to be checked are set using logical operators.

Argument types:

  • condition_1 — Any
  • result_1 — Any
  • condition_2 — Any
  • result_2 — Any
  • default_result — Any

Return type: Depends on argument types

Note

Arguments result_1, result_2, default_result must be of the same type.

Examples

IF
    [Profit] > 100
        THEN "High"
    ELSEIF [Profit] > 25
        THEN "Medium"
    ELSE "Low"
END
Example with data table

Formulas:

  • sales: [sales] ;
  • if_function: IF(ZN([sales]) < 100, "Less than 100", [sales] < 1000, "100 - 1000", "1000 and greater") ;
  • if_statement: IF ZN([sales]) < 100 THEN "Less than 100" ELSEIF [sales] < 1000 THEN "100 - 1000" ELSE "1000 and greater" END .
sales if_function if_statement
432.40 '100 - 1000' '100 - 1000'
77.00 'Less than 100' 'Less than 100'
12000.00 '1000 and greater' '1000 and greater'
NULL 'Less than 100' 'Less than 100'
34.25 'Less than 100' 'Less than 100'
128.00 '100 - 1000' '100 - 1000'
0.00 'Less than 100' 'Less than 100'
NULL 'Less than 100' 'Less than 100'

Data source support

ClickHouse 21.8, Microsoft SQL Server 2017 (14.0), MySQL 5.7, Oracle Database 12c (12.1), PostgreSQL 9.3, YDB.

Previous
CASE
Next
IFNULL