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.

    Argument types:

    • condition_1Any
    • result_1Any
    • condition_2Any
    • result_2Any
    • default_resultAny

    Return type: Depends on argument types

    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 19.13, Microsoft SQL Server 2017 (14.0), MySQL 5.6, PostgreSQL 9.3.