MySQL CASE Statement with Multiple Conditions: A Complete Guide

The MySQL CASE statement allows you to return different values based on whether specific conditions are true. It is especially useful when you need to evaluate multiple columns or multiple conditions and create a calculated column in a query.

For example, you can use CASE with the AND operator when a result should be returned only when multiple conditions are satisfied:

SELECT id, team, position,CASEWHEN team ='Mavs'AND position ='Guard'THEN101WHEN team ='Mavs'AND position ='Forward'THEN102WHEN team ='Spurs'AND position ='Guard'THEN103WHEN team ='Spurs'AND position ='Forward'THEN104ENDAS team_pos_IDFROM athletes;

In this example, MySQL checks both the team and position columns and assigns a different ID depending on their combination.

What Is the MySQL CASE Statement?

The CASE statement is a conditional expression that works similarly to IF...ELSE logic in programming languages.

The basic syntax is:

CASEWHEN condition1 THEN result1WHEN condition2 THEN result2WHEN condition3 THEN result3ELSE default_resultEND

MySQL evaluates the WHEN conditions from top to bottom. When it finds the first condition that evaluates to true, it returns the corresponding THEN value.

You can use multiple conditions within a single WHEN clause with operators such as:

• AND
• OR
• =
• <>
• >
• <
• >=
• <=
• IN
• LIKE
• BETWEEN

This makes CASE useful for data transformation, categorization, reporting, and business rules.

Example: MySQL CASE Statement with Multiple Conditions

Suppose we have a table named athletes containing information about basketball players.

We can create the table using:

CREATETABLE athletes (    id INTPRIMARYKEY,    team TEXT NOTNULL,    position TEXT NOTNULL,    points INTNOTNULL);

Next, insert some sample data:

INSERTINTO athletes VALUES(1, 'Mavs', 'Guard', 15),(2, 'Mavs', 'Guard', 22),(3, 'Mavs', 'Forward', 36),(4, 'Spurs', 'Guard', 18),(5, 'Spurs', 'Forward', 40),(6, 'Spurs', 'Forward', 25);

We can view the table with:

SELECT*FROM athletes;

The output is:

+----+-------+----------+--------+| id | team  | position | points |+----+-------+----------+--------+|  1 | Mavs  | Guard    |     15 ||  2 | Mavs  | Guard    |     22 ||  3 | Mavs  | Forward  |     36 ||  4 | Spurs | Guard    |     18 ||  5 | Spurs | Forward  |     40 ||  6 | Spurs | Forward  |     25 |+----+-------+----------+--------+

Suppose we want to create a new column named team_pos_ID based on the combination of the team and position columns.

We can use:

SELECT    id,    team,    position,CASEWHEN team ='Mavs'AND position ='Guard'THEN101WHEN team ='Mavs'AND position ='Forward'THEN102WHEN team ='Spurs'AND position ='Guard'THEN103WHEN team ='Spurs'AND position ='Forward'THEN104ENDAS team_pos_IDFROM athletes;

The output is:

+----+-------+----------+-------------+| id | team  | position | team_pos_ID |+----+-------+----------+-------------+|  1 | Mavs  | Guard    |         101 ||  2 | Mavs  | Guard    |         101 ||  3 | Mavs  | Forward  |         102 ||  4 | Spurs | Guard    |         103 ||  5 | Spurs | Forward  |         104 ||  6 | Spurs | Forward  |         104 |+----+-------+----------+-------------+

The query evaluates both columns for every row.

For example, the first row contains:

team = Mavsposition = Guard

Therefore:

team ='Mavs'AND position ='Guard'

is true, so MySQL returns 101.

Similarly, a row containing:

team = Spursposition = Forward

satisfies:

team ='Spurs'AND position ='Forward'

and therefore returns 104.

Using AND with CASE

The AND operator is particularly useful when all conditions must be true.

For example:

CASEWHEN team ='Mavs'AND position ='Guard'THEN101END

The result 101 is returned only when both conditions are satisfied.

Consider these examples:

This makes AND useful for building rules based on combinations of columns.

Using OR with CASE

You can also use OR when you want the condition to be true if at least one condition is satisfied.

For example:

SELECT    id,    team,    position,CASEWHEN team ='Mavs'OR position ='Guard'THEN'Match'ELSE'No Match'ENDASresultFROM athletes;

Here, a row is classified as Match if either:

team = 'Mavs'

or:

position = 'Guard'

is true.

For example:

Mavs + Forward → MatchSpurs + Guard   → MatchSpurs + Forward → No Match

Using ELSE with CASE

It is often a good idea to include an ELSE clause.

For example:

SELECT    id,    team,    position,CASEWHEN team ='Mavs'AND position ='Guard'THEN101WHEN team ='Mavs'AND position ='Forward'THEN102WHEN team ='Spurs'AND position ='Guard'THEN103WHEN team ='Spurs'AND position ='Forward'THEN104ELSE0ENDAS team_pos_IDFROM athletes;

The ELSE 0 ensures that rows that do not satisfy any of the conditions receive 0.

Without an ELSE clause, MySQL returns NULL when none of the WHEN conditions is true.

For example, if the table contained:

team = Lakersposition = Guard

none of the four conditions would match.

With:

ELSE0

the result would be:

0

Without ELSE, the result would be:

NULL

CASE with Numeric Conditions

CASE is not limited to text comparisons. You can also use numerical conditions.

Suppose we want to classify athletes based on their team and points:

SELECT    id,    team,    points,CASEWHEN team ='Mavs'AND points >=30THEN'High'WHEN team ='Mavs'AND points <30THEN'Low'ELSE'Other'ENDAS performanceFROM athletes;

Here, the query evaluates both team and points.

For example:

Mavs + 36 points → HighMavs + 22 points → LowSpurs + 40 points → Other

This type of logic is useful for creating business classifications.

CASE with More Than Two Conditions

You can include as many WHEN clauses as required.

For example:

SELECT    id,    points,CASEWHEN points >=40THEN'Excellent'WHEN points >=30THEN'Very Good'WHEN points >=20THEN'Good'WHEN points >=10THEN'Average'ELSE'Low'ENDAS performanceFROM athletes;

MySQL checks the conditions in order.

For example, if points is 40, the first condition is true:

points >=40

so MySQL returns:

Excellent

and does not continue checking the remaining conditions.

The Order of WHEN Conditions Matters

The order of WHEN clauses can significantly affect the result.

Consider:

CASEWHEN points >=20THEN'Good'WHEN points >=40THEN'Excellent'ELSE'Low'END

This is problematic because a value of 40 satisfies:

points >=20

first.

Therefore, MySQL returns:

Good

instead of:

Excellent

A better approach is to check the more restrictive condition first:

CASEWHEN points >=40THEN'Excellent'WHEN points >=20THEN'Good'ELSE'Low'END

Now a score of 40 is classified as Excellent.

CASE with IN

You can combine CASE with IN when a condition should match one of several values.

For example:

SELECT    id,    team,CASEWHEN team IN ('Mavs', 'Spurs') THEN'West'ELSE'Other'ENDAS conferenceFROM athletes;

This avoids writing multiple OR conditions:

team ='Mavs'OR team ='Spurs'

The IN version is usually easier to read.

CASE with LIKE

You can also use LIKE inside a CASE expression.

For example:

SELECT    id,    team,CASEWHEN team LIKE'M%'THEN'Starts with M'ELSE'Other'ENDAS team_categoryFROM athletes;

This classifies teams based on their names.

You can also combine multiple conditions:

SELECT    id,    team,    position,CASEWHEN team LIKE'M%'AND position ='Guard'THEN'M Team Guard'ELSE'Other'ENDAS categoryFROM athletes;

CASE with BETWEEN

BETWEEN can be useful when classifying numeric ranges.

For example:

SELECT    id,    points,CASEWHEN points BETWEEN30AND40THEN'High'WHEN points BETWEEN20AND29THEN'Medium'ELSE'Low'ENDAS points_categoryFROM athletes;

This is useful for creating categories from continuous or numerical data.

CASE in an ORDER BY Clause

You can also use CASE to customize the ordering of query results.

For example:

SELECT    id,    team,    position,    pointsFROM athletesORDERBYCASEWHEN team ='Mavs'THEN1WHEN team ='Spurs'THEN2ELSE3END;

This allows you to create a custom sort order rather than simply sorting alphabetically.

CASE in a WHERE Clause

Although CASE is most commonly used to create calculated columns, conditional expressions can also be used in filtering logic.

However, for straightforward filtering, regular WHERE conditions are generally easier to read.

For example, instead of unnecessarily using:

WHERECASEWHEN team ='Mavs'THEN1ELSE0END=1

you would normally write:

WHERE team ='Mavs'

Use CASE where conditional output or classification is actually needed.

CASE vs IF in MySQL

MySQL also provides an IF() function:

IF(condition, value_if_true, value_if_false)

For example:

SELECT    team,IF(position ='Guard', 'Backcourt', 'Frontcourt') AS areaFROM athletes;

For simple two-way conditions, IF() can be convenient.

However, CASE is usually better when there are several conditions:

SELECT    team,    position,CASEWHEN team ='Mavs'AND position ='Guard'THEN101WHEN team ='Mavs'AND position ='Forward'THEN102WHEN team ='Spurs'AND position ='Guard'THEN103WHEN team ='Spurs'AND position ='Forward'THEN104ELSE0ENDAS team_pos_IDFROM athletes;

For complex business rules, CASE is generally easier to maintain.

A Practical Business Example

The same technique can be applied to real-world business data.

Suppose you have a sales table containing:

customer_typeregionsales_amount

You could classify customers using multiple conditions:

SELECT    customer_type,    region,    sales_amount,CASEWHEN customer_type ='Enterprise'AND sales_amount >=100000THEN'Enterprise - High Value'WHEN customer_type ='Enterprise'AND sales_amount <100000THEN'Enterprise - Standard'WHEN customer_type ='SMB'AND sales_amount >=50000THEN'SMB - High Value'ELSE'Standard'ENDAS customer_segmentFROM sales;

This approach is commonly useful in:

• Sales reporting
• Customer segmentation
• Financial analysis
• Business intelligence dashboards
• Marketing analytics
• Data transformation
• Revenue classification
• Risk scoring
• Operational reporting

Complete MySQL Example

The following script can be copied and executed directly in MySQL:

CREATETABLE athletes (    id INTPRIMARYKEY,    team VARCHAR(50) NOTNULL,    position VARCHAR(50) NOTNULL,    points INTNOTNULL);INSERTINTO athletes VALUES(1, 'Mavs', 'Guard', 15),(2, 'Mavs', 'Guard', 22),(3, 'Mavs', 'Forward', 36),(4, 'Spurs', 'Guard', 18),(5, 'Spurs', 'Forward', 40),(6, 'Spurs', 'Forward', 25);SELECT    id,    team,    position,CASEWHEN team ='Mavs'AND position ='Guard'THEN101WHEN team ='Mavs'AND position ='Forward'THEN102WHEN team ='Spurs'AND position ='Guard'THEN103WHEN team ='Spurs'AND position ='Forward'THEN104ELSE0ENDAS team_pos_IDFROM athletes;

The resulting team_pos_ID values are:

+----+-------+----------+-------------+| id | team  | position | team_pos_ID |+----+-------+----------+-------------+|  1 | Mavs  | Guard    |         101 ||  2 | Mavs  | Guard    |         101 ||  3 | Mavs  | Forward  |         102 ||  4 | Spurs | Guard    |         103 ||  5 | Spurs | Forward  |         104 ||  6 | Spurs | Forward  |         104 |+----+-------+----------+-------------+

Common Mistakes When Using CASE with Multiple Conditions

Forgetting the END Keyword

Every CASE expression must end with:

END

For example:

CASEWHEN team ='Mavs'THEN101ELSE0END

Forgetting ELSE

ELSE is optional, but adding it can make the result more predictable.

Without ELSE, unmatched rows return NULL.

Putting Conditions in the Wrong Order

MySQL evaluates WHEN clauses sequentially. Put more specific conditions before broader conditions when necessary.

Using OR When AND Is Required

These two expressions mean very different things.

AND requires both conditions:

team ='Mavs'AND position ='Guard'

OR requires at least one condition:

team ='Mavs'OR position ='Guard'

Choosing the wrong operator can produce incorrect classifications.

Conclusion

The MySQL CASE statement is a powerful way to implement conditional logic directly inside SQL queries. When multiple columns need to be evaluated together, you can combine CASE with AND to ensure that several conditions are satisfied before returning a result.

For example:

SELECT    id,    team,    position,CASEWHEN team ='Mavs'AND position ='Guard'THEN101WHEN team ='Mavs'AND position ='Forward'THEN102WHEN team ='Spurs'AND position ='Guard'THEN103WHEN team ='Spurs'AND position ='Forward'THEN104ELSE0ENDAS team_pos_IDFROM athletes;

You can also combine CASE with OR, IN, LIKE, BETWEEN, comparison operators, and numeric conditions. This makes it useful for everything from simple data categorization to sophisticated business intelligence, analytics, reporting, and data transformation workflows.

The key points to remember are:

• Use CASE to return different values based on conditions.
• Use AND when all specified conditions must be true.
• Use OR when at least one condition must be true.
• MySQL evaluates WHEN clauses from top to bottom.
• Use ELSE when you need a value for unmatched rows.
• Use AS to give the calculated column a meaningful name.
• For multiple business rules, CASE is often clearer and more maintainable than deeply nested IF() expressions.