Using CASE WHEN, IF, and IFNULL Functions in MySQL for Conditional Logic

In MySQL, conditional functions such as CASE WHEN , IF , and IFNULL are essential for handling complex data‑analysis queries that require branching logic.

1. CASE WHEN

The CASE WHEN statement works like an if‑else construct in programming languages. It has two forms: a simple function and a conditional expression.

Simple function

CASE 字段 WHEN 预期值 THEN 结果1 ELSE 结果2 END

Example: converting a numeric gender field to Chinese characters.

SELECT name,(CASE sex WHEN 0 THEN '女' ELSE '男' END) sex FROM score;

Conditional expression

CASE
  WHEN condition THEN result1 ELSE result2
END

Example: classifying scores into levels.

SELECT name,score,(CASE
  WHEN score>=90 THEN '优秀'
  WHEN score>=80 THEN '良好'
  WHEN score>=60 THEN '及格'
  ELSE '不及格'
END) level FROM score;

Combining CASE WHEN with aggregate functions enables more advanced statistics, such as counting male/female students and pass rates.

SELECT
  SUM(CASE WHEN sex=0 THEN 1 ELSE 0 END) AS 女生人数,
  SUM(CASE WHEN sex=1 THEN 1 ELSE 0 END) AS 男生人数,
  SUM(CASE WHEN score>=60 AND sex=0 THEN 1 ELSE 0 END) AS 女生及格人数,
  SUM(CASE WHEN score>=60 AND sex=1 THEN 1 ELSE 0 END) AS 男生及格人数
FROM score;

2. IF

The IF function returns one of two values based on a boolean expression.

IF(expr, result_true, result_false)

Example: converting gender values.

SELECT name, IF(sex=1,'男','女') sex FROM students;

It can also be used with aggregation, e.g., counting male and female students.

SELECT COUNT(IF(sex=1,1,NULL)) AS 男生人数,
       COUNT(IF(sex=0,1,NULL)) AS 女生人数
FROM students;

3. IFNULL

The IFNULL function replaces NULL results with a specified value, preventing unexpected null handling in applications.

SELECT IFNULL(price,0) price FROM goods WHERE name='light';

If the WHERE condition yields no rows, the result is still NULL ; using IFNULL ensures a zero is returned instead.

SELECT IFNULL(SUM(price),0) FROM goods WHERE status=3;

Similar handling can be applied to other aggregate functions such as AVG and COUNT to guarantee non‑null outputs.

Overall, mastering these conditional functions allows developers to write more expressive and robust SQL queries for data transformation and reporting.