What is a CASE Statement in SQL?

A CASE statement in SQL is a conditional statement used to evaluate a given expression and perform different actions based on the result of that evaluation. It allows you to control the flow of your SQL query and provides you with the flexibility to handle complex scenarios. A CASE statement can be used in an SQL statement’s SELECT, WHERE, and HAVING clauses.

The basic syntax of a CASE statement is as follows:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE resultN
END

Here, the condition is evaluated, and if it’s true, the corresponding result is returned. If none of the conditions are met, the ELSE statement is executed.

When to Use CASE Statements in SQL?

CASE statements can be used in various scenarios. They are particularly useful when you want to perform conditional logic in your SQL queries. Here are some examples of when to use a CASE statement in SQL:

• Data Transformation: Suppose you have a column in your table that contains data in different formats, and you want to convert it into a consistent format. You can use a CASE statement to achieve this. For example, suppose you have a column named gender that contains the values M and F. You can use the following SQL code to convert these values into Male and Female.

SELECT
    CASE gender
        WHEN 'M' THEN 'Male'
        WHEN 'F' THEN 'Female'
    END AS gender_text
FROM 
    Customers;

• Filtering Data: You can use CASE statements to filter data based on a specific condition. For example, suppose you have a table of sales data, and you want to filter out all the records where the sales amount is less than 1000. You can use the following SQL code to achieve this:

SELECT
    *
FROM 
    sales
WHERE
    CASE
        WHEN sales_amount < 1000 THEN 0
        ELSE 1
    END = 1;

• Grouping Data: You can use CASE statements to group data based on specific criteria. For example, suppose you have a table of sales data, and you want to group the sales by their amount into different categories. You can use the following SQL code to achieve this:

SELECT
    CASE
        WHEN sales_amount < 1000 THEN 'Low'
        WHEN sales_amount >= 1000 AND sales_amount < 5000 THEN 'Medium'
        ELSE 'High'
    END AS sales_category,
    COUNT(*) AS number_of_sales
FROM 
    sales
GROUP BY 
    CASE
        WHEN sales_amount < 1000 THEN 'Low'
        WHEN sales_amount >= 1000 AND sales_amount < 5000 THEN 'Medium'
        ELSE 'High'
    END;

How to Use CASE Statements in SQL

Now that you know when to use a CASE statement in SQL, let’s take a look at how to use it. There are two types of CASE statements in SQL: simple CASE and searched CASE.

Simple CASE

A simple CASE statement evaluates a single expression and compares it to a set of values. The syntax for a simple CASE statement is as follows:

CASE expression

    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    ELSE resultN
END

Here, expression is the value that you want to evaluate, and value1, value2, etc. are the values that you want to compare it to. If expression matches any of the values, the corresponding result is returned. If none

Conclusion

In conclusion, the CASE statement in SQL is a powerful tool that allows you to handle complex scenarios and perform conditional logic in your SQL queries. With its ability to transform data, filter data, and group data, you can easily manipulate and analyse your data to get the insights you need. Whether you’re a beginner or an advanced SQL user, understanding how to use the CASE statement can take your SQL skills to the next level. By following the guidelines and examples provided in this guide, you’ll be well on your way to mastering the CASE statement and becoming a more efficient and effective SQL programmer.

Learn coding basics for free

If you’re new to code and don’t no what SQL is, then check out this blog. Alternatively, if you want to learn some coding basics, try our free 5 Day Coding Challenge. In this short course, you will learn the basics of HTML, CSS & JavaScript. After just one hour a day over five days, you will build your first web page. Register now through the form below.