Using CASE Statements in SQL SELECT Queries

1. Overview

CASE statements are a way to add if-then logic to SQL SELECT queries. They test conditions and return different values based on the results. This makes queries more flexible and helps transform and group data in the SELECT clause.

In this tutorial, we’ll explain CASE statements and how to use them. In particular, we’ll cover the types of CASE statements, explain their format, and give examples using this sample database.

2. Understanding CASE Statements

As already mentioned, CASE statements add if-then logic to SQL queries.

2.1. What Are CASE Statements?

Let’s imagine we have a list of students with their grades. We want to label each student as Pass or Fail based on their grade. This is where CASE statements come in useful. They let us evaluate the value of a condition and return a corresponding result.

Specifically, CASE statements are control structures that provide a way to add conditional logic to SQL queries. They work like a series of if-then-else statements in other programming languages. Moreover, CASE statements compare an expression to one or more possible values or evaluate one or more Boolean conditions. As a result, the comparison returns the corresponding result.

2.2. Why Use CASE Statements?

CASE statements are useful for various reasons:

• Transforming data: We can use CASE statements to categorize or group values based on certain conditions. For example, we can group students into categories like Honors, Satisfactory, or Needs Improvement based on their GPA.
• Handling NULL values: CASE statements can test for NULL and replace it with more meaningful values. This is quite helpful when we want to display a default value instead of NULL.
• Creating new columns: CASE statements let us generate new columns in the result set based on the values of other columns. Thus, this saves us from having to modify the original data.

In all, when we have a query with many conditions, CASE statements can make it easier to read and understand by breaking down the logic into separate WHEN-THEN clauses.

3. Types of CASE Statements

There are two main types of CASE statements in SQL:

• simple CASE
• searched CASE

They differ in how they compare values or evaluate conditions.

3.1. Simple CASE

To begin with, a simple CASE statement compares an expression to one or more possible values. It has a simple format:

CASE expression
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  ...
  [ELSE default_result]
END

Let’s understand how it works:

1. expression is evaluated
2. result of the expression is compared to each value in the WHEN clauses in order
3. if a match is found, the corresponding result is returned
4. if no match is found and an ELSE clause is specified, the default_result is returned
5. if no match is found and no ELSE clause is specified, NULL is returned

The key to using simple CASE statements effectively is understanding how to compare an expression to fixed values. The expression in a simple CASE statement can be a column name, a function, or any valid SQL expression. The values in the WHEN clauses are the fixed values against which we want to compare.

For instance, we have the columns id, name, and is_active in the Columns table. The is_active column indicates whether a course is currently being offered. We can use a simple CASE statement to transform the is_active values into more descriptive labels:

SELECT id, name,
  CASE is_active
    WHEN 'Yes' THEN 'Active'
    WHEN 'No' THEN 'Inactive'
  END AS status
FROM Course;
+-------+---------------------------------------------+----------+
| id    | name                                        | status   |
+-------+---------------------------------------------+----------+
| CE111 | Introduction to Structural Engineering      | Active   |
| CE121 | Geotechnical Engineering-I                  | Active   |
...
| CE441 | Mechanics of Fluids-III                     | Active   |
| CE511 | Advanced Structural Engineering             | Inactive |
| CS111 | Introduction to Operating Systems           | Active   |
| CS112 | Introduction to Real Time Operating Systems | Active   |
| CS113 | Introduction to Computer Architecture       | Active   |
| CS121 | Introduction to Databases                   | Active   |
| CS122 | Relational Databases                        | Inactive |
...
| CS421 | Advanced Database Concepts                  | Active   |
| CS422 | Advanced Relational Databases               | Active   |
| CS441 | Advanced Machine Learning                   | Active   |
| CS442 | Advances in Deep Learning                   | Inactive |
| CS443 | Advanced Reinforcement Learning             | Active   |
| CS511 | Distributed Systems                         | Active   |
| CS531 | Advanced Data Structures                    | Active   |
| CS532 | Advanced Algorithms                         | Active   |
| CS535 | Non-Planar Graphs                           | Inactive |
...
+-------+---------------------------------------------+----------+
81 rows in set (0.00 sec)

In this example, we compare the is_active column to the fixed values Yes and No. When is_active is Yes, the result is Active. When is_active is No, the result is Inactive.

3.2. Searched CASE

In contrast, a searched CASE statement evaluates one or more Boolean conditions rather than directly matching a single expression. It also has a fairly simple format:

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

Let’s explain the code further:

1. each condition in the WHEN clauses is evaluated in order
2. if a condition is true, the corresponding result is returned
3. if no conditions are true and an ELSE clause is specified, the default_result is returned
4. if no conditions are true and no ELSE clause is specified, NULL is returned

The main difference between simple and searched CASE statements lies in how they handle conditions. While simple CASE statements compare an expression to fixed values, searched CASE statements evaluate one or more Boolean conditions. Further, each WHEN clause in a searched CASE statement contains a condition that evaluates to either true or false. The conditions can be as complex as needed, using any combination of comparison operators, logical operators, and SQL functions.

For example, let’s suppose we want to categorize students based on their GPA as mentioned earlier. For this, we can use a searched CASE statement:

SELECT name,
  CASE
    WHEN gpa >= 3.5 THEN 'Honors'
    WHEN gpa >= 2.5 THEN 'Satisfactory'
    ELSE 'Needs Improvement'
  END AS performance
FROM Student;

+-----------------+-------------------+
| name            | performance       |
+-----------------+-------------------+
| John Liu        | Honors            |
| Rita Ora        | Honors            |
| Philip Lose     | Honors            |
| Samantha Prabhu | Honors            |
| Vikas Jain      | Satisfactory      |
...
| Vikram Kohli    | Satisfactory      |
| Peter Liu       | Needs Improvement |
| Ritu Raj        | Needs Improvement |
...
| Agatha Christi  | Honors            |
| Julia Roberts   | Satisfactory      |
| Pollards Grey   | Honors            |
| Potu Singh      | Needs Improvement |
+-----------------+-------------------+
25 rows in set (0.00 sec)

In this example, we compare each student’s gpa to the conditions in the WHEN clauses. If the GPA is 3.5 or higher, the result is Honors. If the GPA is between 2.5 and 3.5, the result is Satisfactory. Finally, if the GPA is below 2.5, the result is Needs Improvement.

It’s important to note that the code examples provided run in MySQL, PostgreSQL, and SQL Server, as the syntax for CASE statements is consistent across these database systems.

4. Other Use Cases

Now that we’ve covered the fundamentals of simple and searched CASE statements, let’s explore some scenarios that involve combining CASE statements with other SQL features to perform complex calculations, aggregate data, and handle more scenarios.

4.1. Performing Calculations Within CASE Statements

CASE statements aren’t limited to returning simple values. We can also perform calculations within CASE statements to derive new values based on conditions.

For instance, let’s say we want to calculate a bonus for each student based on their GPA. We can use a CASE statement to determine the bonus percentage and then multiply it by a base amount:

SELECT name,
  CASE
    WHEN gpa >= 3.5 THEN 0.1 * 1000
    WHEN gpa >= 3.0 THEN 0.05 * 1000
    ELSE 0
  END AS bonus
FROM Student;
+-----------------+-------+
| name            | bonus |
+-----------------+-------+
| John Liu        | 100.0 |
| Rita Ora        | 100.0 |
| Philip Lose     | 100.0 |
| Samantha Prabhu | 100.0 |
| Vikas Jain      | 50.00 |
...
| Pollards Grey   | 100.0 |
| Potu Singh      |     0 |
+-----------------+-------+
25 rows in set (0.00 sec)

In this example, students with a GPA of 3.5 or higher receive a bonus of 10% of 1000. Also, students with a GPA between 3.0 and 3.5 receive a bonus of 5% of 1000. Finally, students with a GPA below 3.0 receive no bonus.

4.2. Combining CASE With Aggregate Functions

CASE statements can be particularly powerful when combined with aggregate functions like COUNT, SUM, AVG, and MAX.

Let’s consider an example where we want to count the number of students enrolled each year. To achieve this, we can use a CASE statement within the COUNT aggregate function:

SELECT
  CASE
    WHEN enrollment_date BETWEEN '2020-01-01' AND '2020-12-31' THEN '2020'
    WHEN enrollment_date BETWEEN '2021-01-01' AND '2021-12-31' THEN '2021'
    WHEN enrollment_date BETWEEN '2022-01-01' AND '2022-12-31' THEN '2022'
  END AS enrollment_year,
  COUNT(*) AS student_count
FROM Student
GROUP BY enrollment_year;
+-----------------+---------------+
| enrollment_year | student_count |
+-----------------+---------------+
| 2020            |            10 |
| 2021            |            10 |
| 2022            |             5 |
+-----------------+---------------+
3 rows in set (0.01 sec)

In this example, the CASE statement categorizes students based on their enrollment date. Then, the COUNT(*) function counts the number of students in each enrollment year category. The result set displays the enrollment year and the corresponding student count.

4.3. CASE Statements and Subqueries

We can couple CASE statements with subqueries to create even more dynamic queries.

For example, let’s say we want to assign a rank to each student based on their GPA within their respective departments. We can use a CASE statement along with a subquery to accomplish this:

SELECT s.name, s.enrollment_date,
  CASE
    WHEN s.gpa >= (
      SELECT AVG(gpa)
      FROM Student
      WHERE YEAR(enrollment_date) = YEAR(s.enrollment_date)
    ) THEN 'Above Average'
    ELSE 'Below Average'
  END AS gpa_rank
FROM Student s;
+-----------------+-----------------+---------------+
| name            | enrollment_date | gpa_rank      |
+-----------------+-----------------+---------------+
| John Liu        | 2020-01-15      | Above Average |
| Rita Ora        | 2020-01-15      | Above Average |
| Philip Lose     | 2020-01-15      | Below Average |
| Samantha Prabhu | 2020-01-15      | Above Average |
| Vikas Jain      | 2020-01-15      | Below Average |
| Jia Grey        | 2020-01-15      | Above Average |
| Rose Rit        | 2020-01-15      | Below Average |
| Phellum Luis    | 2020-01-15      | Above Average |
| Albert Decosta  | 2020-01-15      | Above Average |
| Vikram Kohli    | 2020-01-15      | Below Average |
| Peter Liu       | 2021-01-15      | Below Average |
| Ritu Raj        | 2021-01-15      | Below Average |
...
| Potu Singh      | 2022-01-15      | Below Average |
+-----------------+-----------------+---------------+
25 rows in set (0.00 sec)

In this example, the subquery calculates the average GPA for each enrollment year. Then, the CASE statement compares each student’s GPA to the average GPA of their enrollment year. If a student’s GPA is greater than or equal to the enrollment year’s average, we assign a rank of Above Average. Otherwise, we assign a rank of Below Average.

5. Conclusion

In this article, we explored the role of CASE statements in SQL for adding conditional logic to queries. We covered both simple and searched CASE statements, learning how to compare expressions to fixed values and evaluate complex conditions.

Additionally, we saw ways to perform calculations within CASE statements and combine them with aggregate functions. Finally, we examined using CASE with subqueries to create more dynamic queries.