MySQL provides several functions for implementing conditional logic in queries, including CASE, IF, IFNULL, and ELT functions.

Sample table structure:

+----+-----------+-----+-------+--------+
| id | name      | sex | level | weight |
+----+-----------+-----+-------+--------+
| 1  | xiaohong  | 1   | 1     | 50     |
| 2  | xiaoming  | 0   | 0     | 90     |
| 3  | xiaohuang | 1   | 2     | 80     |
| 4  | xiaoming  | 0   | 3     | NULL   |
+----+-----------+-----+-------+--------+

Field descriptions:

• sex: 0-woman, 1-man, other-unknown
• level: 1-normal, 2-vip, 3-vvip

CASE Function

Syntax 1: Value-based CASE

CASE column_name
WHEN value1 THEN result1
WHEN value2 THEN result2
WHEN value3 THEN result3
...
ELSE default_result
END

Example:

SELECT name, 
       CASE sex 
       WHEN 0 THEN 'woman' 
       WHEN 1 THEN 'man' 
       ELSE 'unknown' 
       END AS gender 
FROM test;

Syntax 2: Condition-based CASE

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

Example:

SELECT name,
       CASE 
       WHEN sex = 0 THEN 'woman'
       WHEN sex = 1 THEN 'man'
       ELSE 'unknown'
       END AS gender
FROM test;

Output:

+-----------+-------+
| name      | gender|
+-----------+-------+
| xiaohong  | man   |
| xiaoming  | woman |
| xiaohuang | man   |
| xiaoming  | woman |
+-----------+-------+

IF Function

Syntax: IF(condition, true_value, false_value)

Returns true_value if condition evaluates to TRUE (non-zero and not NULL), otherwise returns false_value.

Example:

SELECT name, IF(weight > 85, 'overweight', 'normal') AS weight_status
FROM test;

Output:

+-----------+---------------+
| name      | weight_status |
+-----------+---------------+
| xiaohong  | normal        |
| xiaoming  | overweight    |
| xiaohuang | normal        |
| xiaoming  | normal        |
+-----------+---------------+

When weight is NULL, NULL > 85 returns NULL, so the function returns the false_value 'normal'.

IFNULL Function

Syntax: IFNULL(value, replacement)

Replaces NULL values with the specified replacement value.

Example:

SELECT name, IFNULL(weight, 0) AS adjusted_weight
FROM test;

Output:

+-----------+----------------+
| name      | adjusted_weight|
+-----------+----------------+
| xiaohong  | 50             |
| xiaoming  | 90             |
| xiaohuang | 80             |
| xiaoming  | 0              |
+-----------+----------------+

Example with calculation:

SELECT name, IFNULL(weight, 0) + 100 AS calculated_weight
FROM test;

Output:

+-----------+------------------+
| name      | calculated_weight|
+-----------+------------------+
| xiaohong  | 150              |
| xiaoming  | 190              |
| xiaohuang | 180              |
| xiaoming  | 100              |
+-----------+------------------+

ELT Function

Syntax: ELT(index, str1, str2, str3, ...)

Returns str1 if index = 1, str2 if index = 2, and so on. Returns NULL if index is less than 1 or greater than the number of arguments.

Example:

SELECT name, ELT(level, 'normal', 'vip', 'vvip') AS customer_level
FROM test;

Output:

+-----------+---------------+
| name      | customer_level|
+-----------+---------------+
| xiaohong  | normal        |
| xiaoming  | NULL          |
| xiaohuang | vip           |
| xiaoming  | vvip          |
+-----------+---------------+

Note: MySQL's IF function can be used both as an expression and as a flow control statement in stored procedures. This article covers only the expression usage.