Menu
MySQL

Control Flow Functions

Control flow functions in MySQL help you execute different actions based on certain conditions. These functions allow you to add conditional logic directly into your SQL queries, similar to conditional statements in programming languages.

 

 

1. IF()

The IF() function returns a value based on a condition. If the condition is true, it returns one value; if false, it returns another.

Syntax:

SELECT IF(condition, true_value, false_value);
 

Example:

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

Result:

SalaryCategory
High Salary
Low Salary

 

 

2. IFNULL()

The IFNULL() function returns the first argument if it is not NULL; otherwise, it returns the second argument.

Syntax:

SELECT IFNULL(expression, default_value);
 

Example:

SELECT IFNULL(bonus, 0) AS BonusAmount FROM employees;

 

If the bonus column has a NULL value, it will return 0 instead of NULL.

 

 

3. NULLIF()

The NULLIF() function returns NULL if two expressions are equal; otherwise, it returns the first expression.

Syntax:

SELECT NULLIF(expression1, expression2);
 

Example:

SELECT NULLIF(5000, 5000) AS Result;
 

Result:

Result
NULL

If the two values are equal, it returns NULL. Otherwise, it returns the first value.

 

 

4. COALESCE()

The COALESCE() function returns the first non-NULL value from the list of arguments. If all arguments are NULL, it returns NULL.

Syntax:

SELECT COALESCE(expression1, expression2, ...);

 

Example:

SELECT COALESCE(bonus, salary, 0) AS Value FROM employees;
 

This will return the bonus if it’s not NULL, otherwise the salary, and if both are NULL, it will return 0.

 

 

Summary of Control Flow Functions

FunctionDescription
IF()Returns one value if a condition is true, another if false.
IFNULL()Returns the first argument if not NULL, otherwise the second argument.
NULLIF()Returns NULL if two expressions are equal, otherwise the first expression.
CASEEvaluates conditions and returns the result for the first true condition.
COALESCE()Returns the first non-NULL value in the list.
GREATEST()Returns the largest value from a list of arguments.
LEAST()Returns the smallest value from a list of arguments.
ELT()Returns the element at the specified index in a list.
FIELD()Returns the index of a value in a list, or 0 if not found.
ISNULL()Checks if a value is NULL and returns 1 if true, 0 if false.

 

 

MySQL