Using SQL CASE Expressions for Data Aggregation and Conditional Updates

In daily work, writing various SQL queries to analyze business data is common; using CASE expressions can make statistics more efficient and the SQL more elegant.

There are two forms of CASE: a simple case and a searched case. The simple case compares a single expression, while the searched case evaluates multiple Boolean conditions.

-- Simple CASE expression
CASE sex
    WHEN '1' THEN 'Male'
    WHEN '2' THEN 'Female'
    ELSE 'Other'
END

-- Searched CASE expression
CASE
    WHEN sex = '1' THEN 'Male'
    WHEN sex = '2' THEN 'Female'
    ELSE 'Other'
END

Each WHEN clause is exclusive; once a true condition is found, the remaining clauses are not evaluated, so conditions should be mutually exclusive.

To illustrate, consider two tables: customer and order . A customer can have multiple orders, forming a one‑to‑many relationship.

id

name

gender

district

vip_level

1

张三

Female

Hangzhou

1

2

李四

Male

Hangzhou

2

3

王五

Male

Haikou

3

4

赵六

Male

Yiwu

2

5

王五

Male

Sansha

2

Order table:

id

customer_id

1

1

2

1

3

2

4

3

5

4

To count users in Zhejiang and Hainan separately, one could write two queries:

-- Count Zhejiang users
SELECT COUNT(*) FROM customer WHERE district IN ('Hangzhou','Yiwu');

-- Count Hainan users
SELECT COUNT(*) FROM customer WHERE district IN ('Haikou','Sansha');

Using CASE, both counts can be obtained in a single query:

SELECT CASE district
    WHEN 'Yiwu' THEN 'Zhejiang'
    WHEN 'Hangzhou' THEN 'Zhejiang'
    WHEN 'Haikou' THEN 'Hainan'
    WHEN 'Sansha' THEN 'Hainan'
    ELSE 'Other'
END AS province,
COUNT(*)
FROM customer
GROUP BY province;

For swapping VIP levels 2 and 3, a naïve approach with two UPDATE statements fails because the first update changes the value that the second statement then overwrites. The correct way is to use a CASE expression in a single UPDATE:

UPDATE customer
SET vip_level = CASE
    WHEN vip_level = 3 THEN 2
    WHEN vip_level = 2 THEN 3
    ELSE vip_level
END;

The ELSE vip_level clause is crucial; without it, rows whose vip_level is neither 2 nor 3 would be set to NULL, unintentionally clearing those values.

Conclusion : CASE expressions greatly simplify SQL logic, making queries more compact and reducing the need for multiple statements, which improves readability and performance.