Skip to main content

IF function

Returns one value if a condition is true, and another value if the condition is false.

Syntax

IF(condition, value_if_true, value_if_false)

Arguments

  • condition: A boolean expression.

  • value_if_true: The value to return if condition evaluates to TRUE.

  • value_if_false: The value to return if condition evaluates to FALSE or NULL.

Returns

Returns value_if_true if the condition is TRUE, otherwise returns value_if_false. The return type is the common type of value_if_true and value_if_false.

Examples

-- Example 1: Simple conditional
> SELECT IF(1 > 0, 'yes', 'no');
'yes'

-- Example 2: Conditional with column values
> SELECT 
    product_name,
    IF(quantity > 0, 'In Stock', 'Out of Stock') AS availability
  FROM products;

-- Example 3: Numeric result
> SELECT IF(score >= 60, 1, 0) AS passed
  FROM test_results;

-- Example 4: NULL condition returns false branch
> SELECT IF(NULL, 'true', 'false');
'false'

-- Example 5: Nested IF statements
> SELECT IF(score >= 90, 'A', 
         IF(score >= 80, 'B',
         IF(score >= 70, 'C',
         IF(score >= 60, 'D', 'F')))) AS grade
  FROM test_results;

-- Example 6: Using with aggregate functions
> SELECT 
    department,
    SUM(IF(status = 'active', 1, 0)) AS active_count,
    SUM(IF(status = 'inactive', 1, 0)) AS inactive_count
  FROM employees
  GROUP BY department;

-- Example 7: Conditional calculation
> SELECT 
    order_id,
    IF(is_member, total * 0.9, total) AS final_price
  FROM orders;

Notes

  • IF is equivalent to a CASE expression:

    IF(condition, value_if_true, value_if_false)
-- is equivalent to:
CASE WHEN condition THEN value_if_true ELSE value_if_false END

See Also