IF

Return true_value if the condition is TRUE, otherwise false_value.

Syntax

IF(condition, true_value, false_value)

Description

## Overview Returns true_value when the boolean condition evaluates to TRUE, or false_value when it evaluates to FALSE or NULL. This is a two-branch shorthand for CASE WHEN condition THEN true_value ELSE false_value END; because the NULL case falls through to false_value, IF never returns NULL unless one of the branch values is NULL. Use IF for simple inline branching. For three or more branches, prefer a CASE expression or WHEN for readability. ## Behavior - NULL condition is treated as FALSE: IF(NULL, a, b) returns b. - Both branch arguments must share a common supertype. - In most engines both branches are parsed and type-checked even when only one is needed at runtime; side effects in the non-taken branch may or may not fire. - Return type is the common supertype of true_value and false_value. ## Compatibility - IF is not part of ANSI SQL but is widely supported. Equivalent to CASE WHEN condition THEN true_value ELSE false_value END. - Some SQL engines use IIF as an alternative name; semantics are identical.

Parameters

Name	Type	Description
condition		Specifies the boolean expression to evaluate. If TRUE, true_value is returned; if FALSE or NULL, false_value is returned.
true_value		Specifies the value returned when the condition evaluates to TRUE.
false_value		Specifies the value returned when the condition evaluates to FALSE or NULL. Must be coercible to a common supertype with true_value.

Examples

-- Basic conditional
SELECT IF(1 > 0, 'positive', 'non-positive') AS v;  -- 'positive'

-- Condition evaluates to FALSE
SELECT IF(10 < 5, 'yes', 'no') AS v;  -- 'no'

-- NULL condition returns the false branch
SELECT IF(NULL, 'true', 'false') AS v;  -- 'false'

-- Nested IF for multi-branch logic (prefer CASE for readability)
SELECT student_id,
       IF(score >= 90, 'A', IF(score >= 80, 'B', 'C')) AS grade
FROM education.grades.fall_2026;

-- Realistic: discount rule in pricing
SELECT order_id, IF(amount > 1000, amount * 0.9, amount) AS final_amount
FROM ecommerce.sales.orders;

-- Count-when-condition pattern with aggregation
SELECT COUNT(*) AS total,
       SUM(IF(status = 'paid', 1, 0)) AS paid_count
FROM ecommerce.sales.orders;

Pitfalls

• NULL condition is treated as FALSE, so IF cannot distinguish FALSE from NULL. Use CASE WHEN condition IS NULL THEN ... when that distinction matters.
• Both branch values must share a common supertype. Mixing STRING and numeric raises a type error.
• Deep nesting of IF(IF(IF(...))) is hard to read; prefer CASE WHEN ... WHEN ... ELSE ... END for multi-branch logic.
• Short-circuit of the non-taken branch is not guaranteed; expressions with side effects or expensive subqueries may still be evaluated.
• IF is not universal SQL; a query using IF will not run on engines that only support CASE. Stick to CASE for portable code.

See Also

• when
• coalesce
• nullif
• nvl2