Search

MySQL IF( ) Function
MySQL IF( ) Function

MySQL IF( ) Function

Text

MySQL IF() Function

‣

1. Introduction

‣

2. Basic Examples

‣

3. Using IF() with NULL values

‣

5. Using IF() with Aggregate Functions

‣

6. Key Differences Between IF() Function and IF Statement

MySQL Conditional Expressions: IF() vs CASE WHENvs IFNULL()

MySQL provides multiple ways to handle conditional logic inside queries. The three most common are:

  1. IF() function
  2. CASE WHEN expression
  3. IFNULL() function
‣

1. IF() Function

‣

2. CASE WHEN Expression

‣

3. IFNULL() Function

4. Side-by-Side Comparison

Feature
IF()
CASE WHEN
IFNULL()
Conditions
Handles 1 condition (TRUE/FALSE)
Handles multiple conditions
Handles only NULL check
Syntax
Short & simple
Longer but flexible
Very short
Readability
Good for simple checks
Best for complex logic
Clean for NULL defaults
Return type
Any (string/number)
Any (string/number)
Same as expression
Use case
Quick if-else
Multiple branches
NULL replacement

5. Practical Scenarios

  • Use IF():

    • When checking a simple TRUE/FALSE condition.

    SELECT IF(amount > 10000, 'High Value', 'Normal') AS Value_Category
FROM payments;
  • Use CASE WHEN:

    • When checking multiple conditions.

    SELECT
    CASE
        WHEN amount > 20000 THEN 'Very High'
        WHEN amount BETWEEN 10000 AND 20000 THEN 'High'
        ELSE 'Normal'
    END AS Value_Category
FROM payments;
  • Use IFNULL():

    • When you just want to replace NULL with a default.

    SELECT customerName, IFNULL(phone, 'No Phone') AS phone
FROM customers;

✅ In summary:

  • Use IF() for binary conditions.
  • Use CASE WHEN for multiple conditional branches.
  • Use IFNULL() for handling NULL values.