MySQL IF Function

MySQL IF() Function

The IF() function in MySQL is a conditional function that returns different values based on a condition. It is similar to the IF-ELSE logic in programming.

1. Syntax of IF()

IF(condition, value_if_true, value_if_false)

condition → The condition to evaluate (TRUE or FALSE).
value_if_true → The value returned if the condition is TRUE.
value_if_false → The value returned if the condition is FALSE.

2. Basic Example

📌 Example: Check if a number is greater than 100

SELECT IF(150 > 100, 'High', 'Low') AS Result;

✔ Output:

+--------+
| Result |
+--------+
| High   |
+--------+

📌 Explanation: Since 150 > 100 is TRUE, MySQL returns 'High'.

3. Using IF() in a SELECT Query

📌 Example: Categorizing salary levels

SELECT employee_name, salary, 
       IF(salary > 5000, 'High Salary', 'Low Salary') AS salary_category
FROM employees;

✔ Output:

+--------------+--------+---------------+
| employee_name | salary | salary_category |
+--------------+--------+---------------+
| John Doe     | 6000   | High Salary   |
| Jane Smith   | 4500   | Low Salary    |
+--------------+--------+---------------+

📌 Explanation: If the salary is greater than 5000, it is categorized as 'High Salary', otherwise 'Low Salary'.

4. Using IF() in an UPDATE Statement

📌 Example: Updating employee bonuses based on performance

UPDATE employees
SET bonus = IF(performance_rating >= 8, 1000, 500);

✔ Explanation: If performance_rating is 8 or higher, the bonus is set to 1000, otherwise, it is 500.

5. Using IF() in WHERE Clause

📌 Example: Fetching employees who earn more than 5000

SELECT * FROM employees WHERE IF(salary > 5000, TRUE, FALSE);

✔ Explanation: This works the same as WHERE salary > 5000.

6. IF() vs. CASE Statement

While IF() is useful for simple conditions, for complex conditions, use the CASE statement.

📌 Example: Using CASE instead of IF

SELECT employee_name, salary,
       CASE 
           WHEN salary > 7000 THEN 'Very High'
           WHEN salary BETWEEN 5000 AND 7000 THEN 'High'
           ELSE 'Low'
       END AS salary_category
FROM employees;

✔ This is better than using multiple IF() functions.

7. Summary

✔ IF(condition, true_value, false_value) is used for conditional checks.
✔ Works in SELECT, WHERE, and UPDATE queries.
✔ Use CASE for more complex conditions.

Would you like an example based on your database? 🚀

Souy Soeng

MySQL IF Function