The IF() function in MySQL returns one value if a condition is true and another value if the condition is false, acting like an IF-THEN-ELSE statement.
Syntax
IF(condition, value_if_true, value_if_false)
Parameter Values
- condition: The condition to test (can be any expression that evaluates to true or false).
- value_if_true: The value to return if the condition is true.
- value_if_false: The value to return if the condition is false.
Definition and Usage
- The
IF()function is used to evaluate a condition and return one of two values based on whether the condition is true or false. - It simplifies decision-making in SQL queries by applying conditional logic.
Return Value
- If the condition is true, it returns the
value_if_true. - If the condition is false, it returns the
value_if_false.
Technical Details
- Works in: From MySQL 4.0
Examples
Example 1: Basic Comparison
Return "YES" if the condition is true, or "NO" if the condition is false:
SELECT IF(500 < 1000, "YES", "NO");
Example Output:
'YES'
Example 2: Test Condition with Numbers
Return 5 if the condition is true, or 10 if the condition is false:
SELECT IF(500 < 1000, 5, 10);
Example Output:
5
Example 3: Compare Strings
Test whether two strings are the same and return "YES" if they are, or "NO" if not:
SELECT IF(STRCMP("hello", "bye") = 0, "YES", "NO");
Example Output:
'NO'
Example 4: Apply Conditional Logic in Queries
Return "MORE" if the quantity is greater than 10, or "LESS" if not:
SELECT OrderID, Quantity, IF(Quantity > 10, "MORE", "LESS")
FROM OrderDetails;
Usage Notes
- The
IF()function is frequently used for conditional expressions directly inSELECTstatements,ORDER BYclauses, and other parts of SQL queries. It helps avoid writing complexCASEstatements when only a simple condition check is needed.