Case in MySQL: 11 Practical Examples

Informatec Digital » Databases » Case in MySQL: 11 Practical Examples

The CASE function in MySQL is a powerful tool that allows you to perform conditional operations within your queries. With CASE, you can evaluate different conditions and return specific results depending on whether or not those conditions are met. We are going to explain this function with practical examples that will help you master the use of CASE in MySQL and improve your skills in database management.

What is the CASE function in MySQL?

The CASE function in MySQL is a conditional expression that allows you to evaluate different conditions and return specific results depending on whether those conditions are met. It's a very useful tool for performing logical operations within your queries and obtaining customized results based on specific criteria.

CASE works similarly to a series of IF-THEN-ELSE statements, where you can specify multiple conditions and the values ​​to return when those conditions are met. If none of the conditions are met, you can define a default value using the ELSE clause.

Basic CASE syntax in MySQL

The basic syntax of the CASE function in MySQL is as follows:

CASE
    WHEN condición1 THEN resultado1
    WHEN condición2 THEN resultado2
    ...
    WHEN condiciónN THEN resultadoN
    ELSE resultado_predeterminado
END

Here is an explanation of each part of the syntax:

• WHEN: Specifies the condition to be evaluated.
• THEN: Indicates the result that will be returned if the corresponding condition is met.
• ELSE: (Optional) Specifies the result to return if none of the above conditions are met.
• END: Marks the end of the CASE expression.

Now that you know the basic syntax, let's explore some practical examples!

Example 1: Classify students according to their average

Suppose you have a table called “students” with the following columns: “id”, “name”, and “average”. You want to rank the students based on their average using the CASE function. You can do this as follows:

SELECT nombre,
       CASE
           WHEN promedio >= 90 THEN 'Sobresaliente'
           WHEN promedio >= 80 THEN 'Notable'
           WHEN promedio >= 70 THEN 'Bien'
           WHEN promedio >= 60 THEN 'Suficiente'
           ELSE 'Insuficiente'
       END AS clasificacion
FROM estudiantes;

In this example, we use CASE to evaluate each student's GPA and assign a corresponding rating. If the GPA is greater than or equal to 90, it is classified as "Outstanding." If it is between 80 and 89, it is classified as "Notable," and so on. If the GPA is less than 60, it is classified as "Insufficient."

Example 2: Assigning product categories

Imagine you have a table called “products” with columns “id”, “name” and “price”. You want to assign a category to each product based on its price using the CASE function. You can do this as follows:

SELECT nombre,
       CASE
           WHEN precio > 1000 THEN 'Premium'
           WHEN precio > 500 THEN 'Gama alta'
           WHEN precio > 100 THEN 'Gama media'
           ELSE 'Económico'
       END AS categoria
FROM productos;

In this example, we use CASE to evaluate the price of each product and assign a corresponding category. If the price is greater than 1000, it is classified as “Premium.” If it is between 500 and 1000, it is classified as “High-end,” and so on. If the price is less than or equal to 100, it is classified as “Economy.”

Example 3: Calculate discounts based on quantity purchased

Suppose you have a table called “sales” with columns “id”, “product”, and “quantity”. You want to calculate the discount applied to each sale based on the quantity purchased using the CASE function. You can do this as follows:

SELECT producto,
       CASE
           WHEN cantidad >= 100 THEN 0.20
           WHEN cantidad >= 50 THEN 0.15
           WHEN cantidad >= 20 THEN 0.10
           ELSE 0
       END AS descuento
FROM ventas;

In this example, we use CASE to evaluate the quantity purchased for each product and calculate the corresponding discount. If the quantity is greater than or equal to 100, a 20% discount is applied. If it is between 50 and 99, a 15% discount is applied, and so on. If the quantity is less than 20, no discount is applied.

Example 4: Converting numeric values ​​to ranges

Imagine you have a table called “employees” with columns “id”, “name”, and “age”. You want to convert the ages of the employees to ranges using the CASE function. You can do this as follows:

SELECT nombre,
       CASE
           WHEN edad >= 60 THEN 'Senior'
           WHEN edad >= 40 THEN 'Mediana edad'
           WHEN edad >= 20 THEN 'Joven'
           ELSE 'Menor de edad'
       END AS rango_edad
FROM empleados;

In this example, we use CASE to evaluate the age of each employee and assign a corresponding rank. If the age is greater than or equal to 60, it is classified as “Senior.” If it is between 40 and 59, it is classified as “Middle-aged,” and so on. If the age is less than 20, it is classified as “Minor.”

Example 5: Assigning labels based on multiple conditions

Suppose you have a table called “orders” with columns “id”, “customer”, “total”, and “status”. You want to assign labels to each order based on its total and status using the CASE function with multiple conditions. You can do this as follows:

SELECT cliente,
       CASE
           WHEN total > 1000 AND estado = 'Entregado' THEN 'VIP'
           WHEN total > 500 AND estado = 'Entregado' THEN 'Prioritario'
           WHEN estado = 'Pendiente' THEN 'En proceso'
           ELSE 'Regular'
       END AS etiqueta
FROM pedidos;

In this example, we use CASE with multiple conditions to evaluate both the total and status of each order and assign a corresponding label. If the total is greater than 1000 and the status is “Delivered,” it is labeled “VIP.” If the total is greater than 500 and the status is “Delivered,” it is labeled “Priority.” If the status is “Pending,” it is labeled “In Process.” Otherwise, it is labeled “Regular.”

Example 6: Handling null values ​​with CASE

Imagine you have a table called “customers” with columns “id”, “name”, and “email”. Some customers may not have an email on file, which would result in null values ​​in the “email” column. You can use the CASE function to handle these null values ​​appropriately. For example:

SELECT nombre,
       CASE
           WHEN email IS NULL THEN 'Sin correo electrónico'
           ELSE email
       END AS informacion_contacto
FROM clientes;

In this example, we use CASE to evaluate whether the “email” column is null. If it is null, the text “No email” is displayed. Otherwise, the actual value of the “email” column is displayed. This allows us to gracefully handle cases where contact information is missing.

Example 7: Combining CASE with Aggregate Functions

The CASE function can also be combined with aggregate functions like SUM, AVG, COUNT, etc. Suppose you have a table called “sales” with columns “id”, “product”, “quantity”, and “price”. You want to calculate the total sales by product category using CASE and SUM. You can do so as follows:

SELECT
    SUM(CASE WHEN precio > 1000 THEN cantidad ELSE 0 END) AS ventas_premium,
    SUM(CASE WHEN precio <= 1000 THEN cantidad ELSE 0 END) AS ventas_regulares
FROM ventas;

In this example, we use CASE inside the SUM function to calculate the total sales by product category. If the price is greater than 1000, the amount is added to “premium_sales.” If the price is less than or equal to 1000, the amount is added to “regular_sales.” This allows us to get subtotals based on specific conditions.

Example 8: Using CASE in WHERE clauses

The CASE function can also be used in the WHERE clause to filter records based on specific conditions. Suppose you have a table called “employees” with columns “id”, “name”, “department”, and “salary”. You want to get the employees whose salary is above the average for their department. You can do this as follows:

SELECT nombre, departamento, salario
FROM empleados
WHERE salario > (
    SELECT AVG(CASE WHEN e.departamento = empleados.departamento THEN e.salario ELSE NULL END)
    FROM empleados e
);

In this example, we use CASE in the subquery to calculate the average salary by department. The subquery compares each employee's department to the current department and only considers the salaries of employees in the same department to calculate the average. Then, in the main query, we filter out employees whose salary is higher than the average calculated for their department.

Example 9: Generating calculated columns with CASE

The CASE function can also be used to generate calculated columns based on specific conditions. Suppose you have a table called “orders” with columns “id”, “customer”, “total”, and “date”. You want to generate an additional column called “discount” that applies different discount percentages based on the order total. You can do this as follows:

SELECT id, cliente, total,
       CASE
           WHEN total > 1000 THEN total * 0.10
           WHEN total > 500 THEN total * 0.05
           ELSE 0
       END AS descuento,
       fecha
FROM pedidos;

In this example, we use CASE to generate the calculated “discount” column. If the order total is greater than 1000, a 10% discount is applied. If the total is greater than 500, a 5% discount is applied. Otherwise, no discount is applied. This calculated column can be used for further analysis or to display additional information in the query results.

Example 10: Implementing complex logic with nested CASE statements

In some cases, you may need to implement more complex conditional logic using nested CASE statements. Suppose you have a table called “students” with columns “id”, “name”, “math_grade”, and “language_grade”. You want to assign a category to each student based on their math and language grades. You can do this as follows:

SELECT nombre,
       CASE
           WHEN nota_matematicas >= 90 AND nota_lenguaje >= 90 THEN 'Excelente'
           WHEN nota_matematicas >= 80 AND nota_lenguaje >= 80 THEN 'Notable'
           ELSE
               CASE
                   WHEN nota_matematicas >= 70 OR nota_lenguaje >= 70 THEN 'Regular'
                   ELSE 'Necesita mejorar'
               END
       END AS categoria
FROM estudiantes;

In this example, we use nested CASE to implement more complex conditional logic. First, we evaluate whether both the math and language grades are greater than or equal to 90. If so, the “Excellent” category is assigned. Next, we evaluate whether both grades are greater than or equal to 80. If so, the “Good” category is assigned. If neither of the above conditions is met, we move to the next level of nested CASE. Here, we evaluate whether at least one of the grades (math or language) is greater than or equal to 70. If so, the “Fair” category is assigned. If neither of the conditions is met, the “Needs Improvement” category is assigned.

Example 11: Optimizing queries with CASE

The CASE function can also be used to optimize queries and avoid multiple separate queries. Suppose you have a table called “sales” with columns “id”, “product”, “quantity”, and “date”. You want to get the total sales per month and the total sales per year in a single query. You can do this as follows:

SELECT
    SUM(CASE WHEN MONTH(fecha) = 1 THEN cantidad ELSE 0 END) AS ventas_enero,
    SUM(CASE WHEN MONTH(fecha) = 2 THEN cantidad ELSE 0 END) AS ventas_febrero,
    -- ... (continúa para los demás meses)
    SUM(CASE WHEN YEAR(fecha) = 2022 THEN cantidad ELSE 0 END) AS ventas_2022,
    SUM(CASE WHEN YEAR(fecha) = 2023 THEN cantidad ELSE 0 END) AS ventas_2023
FROM ventas;

In this example, we use CASE inside the SUM function to calculate sales totals by month and by year in a single query. For each month, we evaluate whether the month of the sales date matches the specific month and sum the corresponding amount. Similarly, for each year, we evaluate whether the year of the sales date matches the specific year and sum the corresponding amount. This allows us to get all the totals in a single, efficient query.

Best practices when using CASE in MySQL

• Use CASE only when necessary and avoid overusing it, as it can affect query performance if used excessively.
• Try to keep CASE expressions as simple and readable as possible. If the logic becomes too complex, consider breaking it up into multiple CASE expressions or using subqueries.
• Use CASE in combination with other MySQL clauses and functions to take full advantage of their potential, such as in WHERE, ORDER BY, GROUP BY and aggregation functions.
• Be careful when nesting multiple CASE expressions, as this can make the code difficult to read and maintain. If necessary, add explanatory comments.

Common mistakes when using CASE and how to avoid them

• Forget the ELSE clause: Be sure to include an ELSE clause to handle cases where none of the conditions are met. If not specified, NULL will be assigned by default.
• Do not terminate the CASE expression with END: Remember to always end the CASE expression with the END keyword. Otherwise, you will get a syntax error.
• Using incompatible data types: Make sure that the results returned by each WHEN condition are of the same data type. If you mix data types, you may get unexpected results or errors.
• Not considering the order of conditions: Conditions in CASE are evaluated in the order they appear. Be sure to place more specific conditions before more general ones to get the desired results.

Alternatives to CASE in MySQL

Although CASE is a powerful function, there are some alternatives you may want to consider in certain cases:

• IF Expressions: The IF function in MySQL allows you to evaluate a condition and return one value if the condition is true and another value if it is false. It is a simpler alternative for single condition cases.
• Lookup tables: In some cases, you can use separate lookup tables to store the conditions and the corresponding results. You can then join these tables with the main table to obtain the desired results.
• Stored Views or Functions: If you have complex queries that use CASE on a recurring basis, you may consider creating views or stored functions to encapsulate that logic and simplify subsequent queries.

Frequently Asked Questions about CASE in MySQL

1. Can I use CASE in combination with other MySQL functions?

Yes, you can use CASE in combination with other MySQL functions, such as aggregate functions (SUM, AVG, COUNT, etc.), date and time functions (YEAR, MONTH, DAY, etc.), string functions (CONCAT, SUBSTRING, LENGTH, etc.), and more.

2. Is there a limit to the number of WHEN conditions I can use in a CASE expression?

There is no specific limit on the number of WHEN conditions you can use in a CASE expression. However, keep in mind that a large number of conditions can affect code readability and query performance. If you have many conditions, consider simplifying the logic or splitting it into multiple CASE expressions.

3. Can I use subqueries inside a CASE expression?

Yes, you can use subqueries within a CASE expression, both in WHEN conditions and THEN results. This allows you to perform more complex calculations or comparisons based on the results of other queries.

4. How can I handle null values ​​in a CASE expression?

You can handle null values ​​in a CASE expression by using the IS NULL or IS NOT NULL condition. For example, you can use CASE WHEN column IS NULL THEN 'Null value' ELSE column END to assign a specific value when the column is null and return the actual value when it is not.

Case Conclusions in Mysql

The CASE function in MySQL is a powerful and versatile tool that allows you to perform conditional operations within your queries. With CASE, you can evaluate different conditions and return specific results based on whether or not those conditions are met. The examples presented in this article give you a solid foundation to start using CASE in your own queries and adapt it to your specific needs.

Remember to follow best practices when using CASE, such as keeping expressions simple and readable, using CASE in combination with other MySQL clauses and functions , and considering alternatives when appropriate. With practice and experimentation, you'll be able to take full advantage of CASE in MySQL and improve the efficiency and readability of your queries.

If you have any additional questions or need more examples, feel free to search for additional resources or consult the official MySQL documentation. Keep exploring and leveraging the power of CASE in your database projects!

Additional Resources:

• Official MySQL documentation on the CASE function
• Additional examples of using CASE in MySQL