Example of usage of case expressions in MySQL database

Preface

In MySQL,CASEAn expression is a function for conditional judgment, which can return different results according to different conditions.CASEExpressions are usually used in SELECT query statements, and can flexibly perform conditional judgment and data conversion in SQL.CASEThere are two basic syntax forms:

Simple CASE expression（Simple CASE）
Search for CASE expressions（Searched CASE）

The usage of these two CASE expressions and their application scenarios will be explained in detail below.

1. Simple CASE expression (Simple CASE)

A simple CASE expression returns different results by conditionally matching the values of a column. The syntax format is as follows:

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

column_name: Used to match column names.
value1, value2, ...: Different values of columns.
result1, result2, ...: The result returned when the column's values match.
ELSE: Optional default result, which returns if no value is matched.

Example: Show student ratings based on grade level

Suppose there is a student score sheetstudents, contains the following fields:id(Student ID),name(Student's name),score(score).

SELECT name,
       score,
       CASE score
           WHEN 100 THEN 'excellent'
           WHEN 80 THEN 'good'
           WHEN 60 THEN 'Pass'
           ELSE 'Failed'
       END AS grade
FROM students;

explain:

The query will be based onscoreThe value of is to give each student the corresponding level (excellent、good、PassorFailed）。

2. Search CASE expression (Searched CASE)

Searching for CASE expressions is more flexible, allowing matching of different conditional expressions rather than simply comparing with the values of a column. The syntax format is as follows:

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

condition1, condition2, ...: Can be any boolean expression (for example:score > 90）。
result1, result2, ...: The result returned when the condition is met.
ELSE: Optional default result.

Example: Determine the grade based on the student's grade interval

SELECT name,
       score,
       CASE 
           WHEN score &gt;= 90 THEN 'excellent'
           WHEN score &gt;= 80 THEN 'good'
           WHEN score &gt;= 60 THEN 'Pass'
           ELSE 'Failed'
       END AS grade
FROM students;

explain:

HereCASEExpression passedWHENThe following conditions are judged to determine the student’s grade level. For example,score >= 90It means that students with grades greater than or equal to 90 are "excellent".
This method allows for more flexible conditional judgment, and the condition can be any Boolean expression.

3. Common applications of CASE expressions

3.1 Using CASE in SELECT query

useCASEDifferent values can be calculated based on different conditions in the query results.

SELECT id,
       name,
       CASE
           WHEN age &lt; 18 THEN 'Minor'
           WHEN age BETWEEN 18 AND 60 THEN 'aldult'
           ELSE 'elderly'
       END AS age_group
FROM employees;

In this example,CASEUsed to accordingageFields determine the age group and mark the corresponding age group for each person (such as "minor", "adult", "elderly").

3.2 Using CASE in UPDATE statements

You can alsoUPDATEUsed in statementsCASETo update different values according to different conditions.

UPDATE employees SET salary = CASE WHEN position = 'Manager' THEN salary * 1.1 WHEN position = 'Developer' THEN salary * 1.05 ELSE salary END;

This query adjusts salary based on employee positions, manager salary increases by 10%, developer salary increases by 5%, and other positions remain unchanged.

3.3 Use CASE in ORDER BY

You canORDER BYUsed in clausesCASETo sort according to specific conditions.

SELECT id,
       name,
       CASE
           WHEN age &lt; 18 THEN 'Minor'
           WHEN age BETWEEN 18 AND 60 THEN 'aldult'
           ELSE 'elderly'
       END AS age_group
FROM employees;

This query will be sorted according to the different ranges of students’ grades, ranking students with grades above 90 at the top, and so on.

4. Things to note about CASE expressions

ELSE is optional: If notELSEclause, and no conditional match,CASEWill returnNULL。
The order of conditions is important：CASEExpressions check each in orderWHENconditions, therefore, the earlier the conditions are met, the more matched they will be.
Return type consistency：CASEAll in the expressionTHENResults andELSEThe default result should have the same data type. If the data types are inconsistent, MySQL attempts to implicitly convert the types, but may result in errors or inaccurate data.

5. Performance precautions

AlthoughCASEExpressions are powerful and flexible, but they may affect performance in some complex queries. Especially whenCASEWhen expressions are calculated multiple times on large tables, they may cause degradation in query performance. If the query is very complex, consider using views or step-by-step processing to improve query efficiency.

Summarize

CASEExpressions are a very useful conditional judgment tool in MySQL, which can make SQL queries more flexible and dynamic. passCASE, can easily classify, convert, sort or update data according to different conditions, and is one of the common and powerful features in SQL queries.

This is all about this article about the usage of case expressions in MySQL database. For more related content on MySQL case usage, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!