How to use Case Statement in SQL

If you’re looking for ways to sort data and implement conditional logic in your SQL queries, the CASE statement is a powerful tool to consider. In this article, we will explore the ins and outs of using the CASE statement in SQL and provide in-depth examples to help you master this function.

What is a CASE Statement?

A CASE statement is a type of SQL statement used to implement conditional logic in a SELECT statement. It is similar to an IF-ELSE statement in other programming languages and provides a way to sort data based on specific criteria or create custom columns based on conditional logic.

The basic syntax of a CASE statement includes the following components:

SELECT 
   CASE 
       WHEN condition1 THEN result1
       WHEN condition2 THEN result2
       ...
       ELSE default result
   END AS column_alias;

The CASE keyword begins the statement, followed by one or more WHEN conditions that evaluate to TRUE or FALSE. If a condition is true, then the corresponding result is displayed. If none of the conditions are true, the default result is used.

Understanding the Different Parts of a Case Statement

To better understand the syntax of a CASE statement, let’s look at each of the components in detail.

SELECT

The SELECT keyword is the command used to retrieve data from a database table. It is followed by the list of columns you want to select from the table. In a CASE statement, you’ll include the CASE statement as one of those columns.

CASE

The CASE keyword is used to begin the CASE statement. It is followed by one or more WHEN conditions and an optional ELSE condition.

WHEN

The WHEN keyword is used to define the condition that needs to be met. It is followed by the condition that needs to be evaluated for the row that is currently being processed. If the condition is true, then the corresponding result is displayed.

THEN

The THEN keyword is used to specify the output when the condition is met.

ELSE

The ELSE keyword is optional and is used to specify the output when none of the conditions are met.

END

The END keyword marks the end of the CASE statement.

AS

The AS keyword is used to rename the column that is created by the CASE statement.

column_alias

The column_alias is the name given to the column that is created by the CASE statement.

How to Use a Simple CASE Statement

The simplest type of CASE statement is a simple CASE statement, which is used to evaluate a single expression and compare it to a set of possible values. Here is an example:

SELECT column_name_1, column_name_2,
       CASE grade
            WHEN 'A' THEN 'Excellent'
            WHEN 'B' THEN 'Good'
            WHEN 'C' THEN 'Average'
            WHEN 'D' THEN 'Below Average'
            ELSE 'Fail'
       END AS 'Grade Result'
FROM table_name;

In this example, the CASE statement evaluates the “grade” column and compares it to the set of values (“A”, “B”, “C”, “D”). If a match is found, the corresponding “Grade Result” is displayed in the output. If there is no match, the ELSE condition is used.

How to Use a Searched CASE Statement

A searched CASE statement is used when multiple conditions need to be checked. The following example will help you understand its usage:

SELECT column_name_1, column_name_2,
       CASE 
            WHEN age >= 18 THEN 'Adult'
            WHEN age > 12 THEN 'Teenager'
            ELSE 'Child'
       END AS 'Age Group'
FROM table_name;

In this example, a searched CASE statement is used to look for multiple conditions based on the value of the “age” column. The first WHEN condition checks if the age is greater than or equal to 18. If it evaluates to true, the output is “Adult.” If not, the second condition checks if age is greater than 12. If it is, the output is “Teenager.” If neither condition is true, the ELSE condition is used and “Child” is displayed.

How to Use a Nested CASE Statement

If you want to include another CASE statement inside a CASE statement, you can use a nested CASE statement. Here’s an example:

SELECT column_name_1, column_name_2,
       CASE 
            WHEN grade = 'A' THEN 'Excellent'
            WHEN grade = 'B' THEN 'Good'
            WHEN grade = 'C' THEN 
                CASE 
                    WHEN attendance > 90 THEN 'Average - Good attendance'
                    ELSE 'Average - Poor attendance'
                END
            ELSE 'Fail'
       END AS 'Result'
FROM table_name;

In this example, a nested CASE statement is used inside the third condition of the outer CASE statement. If the grade is ‘C,’ a second CASE statement is used to check the attendance. If attendance is greater than 90, the output is ‘Average – Good attendance.’ Otherwise, it will be ‘Average – Poor attendance.’

Conclusion

In conclusion, the CASE statement is an essential tool for sorting and filtering data in SQL. Although there are different types of CASE statements, they all have the same basic structure. Using the CASE statement can help you write more efficient and powerful SQL queries. With practice, you can master the CASE statement and use it to create complex and advanced queries that meet your specific needs.

Frequently Asked Questions

📕 Related articles about MySQL

• How to use NOT Operator in SQL
• Understanding mysqld – The MySQL Server
• MySQL UPDATE Statement: How to Update Your Data in a Database
• How to Use Alter Table – Drop Statement in SQL
• How to Use MySQL Delete Query in SQL
• How to use AND and OR operators in SQL