CASE statement

In this topic, we are going to study another quite useful SQL tool – the CASE statement. It is used for conditional logic and branching within queries. It helps us to apply different actions or expressions based on specified conditions. The CASE statement is particularly beneficial for transforming data, creating calculated fields, and controlling query output based on various criteria.

CASE statement syntax

Let us explore the basic syntax of the CASE statement in SQL. The structure looks as follows:

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

The above syntax demonstrates how the CASE statement allows you to perform conditional logic and determine different outcomes based on the specified conditions.

• The CASE keyword marks the beginning of CASE statement;
• Each WHEN clause specifies a condition that is checked;
• If the condition in a WHEN clause evaluates to true, the corresponding result is returned;
• You can have multiple WHEN clauses to handle different conditions;
• The ELSE clause provides a default result if none of the conditions are met;
• The END keyword marks the end of the CASE statement.

Let's explore below few examples where CASE statement can be applied.

Simple CASE statement usage

A simple CASE statement compares an expression to a set of values and returns a result when a match is found. It's often used for categorizing or grouping data.

Suppose we have a table named Employees with columns Name and DepartmentID. We want to categorize employees' departments using their DepartmentID values. The goal is to display the department name for each employee, with a fallback to 'Other' for any unrecognized department IDs.

The SQL will be as follows:

SELECT 
   Name, 
   CASE DepartmentID 
     WHEN 1 THEN 'Sales' 
     WHEN 2 THEN 'Marketing' 
     WHEN 3 THEN 'Finance'
     ELSE 'Other' 
   END AS Department 
FROM Employees;

In this query:

• The CASE DepartmentID part marks the beginning of the Simple CASE statement. It indicates that we want to compare the DepartmentID column's values;
• Each WHEN clause specifies a value to compare the DepartmentID against (e.g., 1, 2, 3) and the corresponding result to return when a match is found ('Sales', 'Marketing', 'Finance');
• The ELSE 'Other' part specifies the default result to return when no matches are found between DepartmentID and the specified values;
• The END AS Department indicates the end of the CASE statement and assigns an alias 'Department' to the result.

When the query is executed:

• If DepartmentID is 1, the result for that row will be 'Sales';
• If DepartmentID is 2, the result for that row will be 'Marketing';
• If DepartmentID is 3, the result for that row will be 'Finance';
• For any other DepartmentID values, the result will be 'Other'.

Searched CASE statement usage

A searched CASE statement evaluates multiple conditions and returns a result when the first matching condition is found. It's more versatile and allows complex conditions.

Suppose we have a table ExamScores with columns StudentName and Score, and we want to assign letter grades based on scores.

Our query will be as follows:

SELECT
    StudentName,
    Score,
    CASE
        WHEN Score >= 90 THEN 'A'
        WHEN Score >= 80 THEN 'B'
        WHEN Score >= 70 THEN 'C'
        WHEN Score >= 60 THEN 'D'
        ELSE 'F'
    END AS Grade
FROM
    ExamScores;

The query calculates the letter grade for each student based on their scores. The output will look like this:

We can not only categorize our table data but also can perform more elaborate calculations for the new field with the help of CASE statement. You can find more applications by exploring the link.

Benefits of CASE statement

Based on the examples we studied, we can conclude that CASE statement can significantly benefit our work with SQL. The main advantages provided are as follows:

• Conditional Transformations: CASE statements allow us to transform data based on specific conditions, making our query results more meaningful and insightful.
• Custom Calculations: We can use CASE to perform custom calculations or derive new fields based on existing data.
• Dynamic Control: CASE provides dynamic control over query output, allowing us to tailor results based on different scenarios.
• Readability: Properly structured CASE statements improve query readability.

Conclusion

In the topic above we learned a CASE statement – a versatile and essential tool in SQL, offering a way to perform conditional logic, data transformation, and custom calculations within our queries. By understanding the CASE statement logic effectively, we can enhance our SQL querying skills and create more informative and tailored database reports and outputs. So let's start practicing right away!