MySQL Control Flow Functions and Expressions

Control flow functions and expressions in MySQL are used to add logic and conditions to SQL queries. They allow developers to execute different operations based on conditions, making queries dynamic and flexible.

Key Control Flow Functions

IF(): Returns a value based on a condition.
IFNULL(): Returns a value if the first argument is NULL.
CASE: Evaluates a series of conditions and returns a value for the first true condition.
NULLIF(): Returns NULL if two expressions are equal.
COALESCE(): Returns the first non-NULL value in a list.

Detailed Explanation and Syntax

1. `IF()` Function

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

Syntax:

IF(condition, value_if_true, value_if_false)

Example:

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

2. `IFNULL()` Function

Returns a replacement value if the first argument is NULL.

Syntax:

IFNULL(expression, replacement_value)

Example:

SELECT name, IFNULL(bonus, 0) AS adjusted_bonus
FROM employees;

3. `CASE` Statement

Evaluate multiple conditions and return the value corresponding to the first true condition.

Syntax:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
END

Example:

SELECT name,
       CASE
           WHEN salary > 7000 THEN 'High Salary'
           WHEN salary BETWEEN 4000 AND 7000 THEN 'Medium Salary'
           ELSE 'Low Salary'
       END AS salary_category
FROM employees;

4. `NULLIF()` Function

Returns NULL if two expressions are equal; otherwise, returns the first expression.

Syntax:

NULLIF(expression1, expression2)

Example:

SELECT NULLIF(salary, 5000) AS adjusted_salary
FROM employees;

5. `COALESCE()` Function

Returns the first non-NULL value from a list of expressions.

Syntax:

COALESCE(expression1, expression2, ..., expressionN)

Example:

SELECT name,
       COALESCE(phone_number, email, 'No Contact Info') AS contact_info
FROM employees;

Using Control Flow Functions with Aggregates

Control flow functions can be used alongside aggregate functions for more complex queries.

Example:

SELECT department_id,
       COUNT(IF(salary > 5000, 1, NULL)) AS high_salary_count,
       COUNT(IF(salary <= 5000, 1, NULL)) AS low_salary_count
FROM employees
GROUP BY department_id;

Practical Use Cases

Conditional Labels: Use IF() or CASE to categorize data into meaningful groups.
Handling NULL Values: Use IFNULL() or COALESCE() to provide default values for NULL.
Data Filtering: Use CASE or NULLIF() for custom filtering logic.
Dynamic Query Results: Use control flow functions to modify query results dynamically based on business logic.

Conclusion

MySQL control flow functions and expressions bring conditional logic into SQL queries, enabling dynamic and flexible data handling. By mastering functions like IF(), CASE, and COALESCE(), you can write more efficient and meaningful queries tailored to specific needs.

Souy Soeng

MySQL Control Flow Functions and Expressions