CASE

Text

return the corresponding result in THEN branch if the condition in the WHEN

Introduction to MySQL CASE expression

The CASE expression in MySQL is a powerful tool that allows you to implement conditional logic (similar to IF-ELSE) directly in your SQL queries.

This is not the same as the CASE statement, which is used inside stored programs (like stored procedures or triggers). Here, we focus on the CASE expression, which is used within SELECT, WHERE, ORDER BY, and other clauses.

Why Use CASE?

You use the CASE expression when you want to:

Transform values based on conditions
Categorize or group data conditionally
Apply conditional sorting
Return different outputs based on specific conditions

Forms of CASE Expression

The CASE expression has two forms: simple CASE and searched CASE.

‣

1. Simple CASE Expression

‣

2. Searched CASE Expression

‣

Where Can You Use CASE?

Summary – Best Use of Each CASE Form

Simple CASE: Use when comparing a single value against different fixed options (e.g., status codes).
Searched CASE: Use when conditions are logical expressions (e.g., numeric ranges, NULL checks).
Use CASE in SELECT for labeling, WHERE for conditional filtering, and ORDER BY for custom sorting.

MySQL CASE expression examples

‣

1. Using CASE expression in the SELECT clause example

‣

2. Using CASE expression in the ORDER BY clause example

‣

3. Using CASE expression with an aggregate function example

Best Practices

Always include an ELSE clause to avoid unexpected NULLs.
For logic involving multiple conditions, prefer searched CASE.
Use CASE sparingly in WHERE for better performance—split queries if needed.

Summary Table

Feature	Simple CASE	Searched CASE
Comparison Type	Equality (`=`)	Logical condition
Use of NULL	Cannot compare with `NULL`	Can use `IS NULL`, `IS NOT NULL`
Flexibility	Limited	More flexible
Example Usage	Categorizing static values	Evaluating ranges or complex logic
Return Value if No Match	ELSE result or NULL	ELSE result or NULL