SQL Server - SQL CASE statement: What is it and what are the best ways to use it?

The SQL CASE statement is a control flow statement that allows you to perform conditional logic in SQL queries. It evaluates a set of conditions and returns a result based on the first condition that is true.

The following syntax shows the basic construction of a CASE Expression:

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

The SQL CASE statement can be used in a variety of ways, including:

Creating calculated fields: You can use CASE statements to create calculated fields in SQL queries. For example, you could use a CASE statement to group data into categories or to calculate a new value based on existing data.

Filtering data: You can use CASE statements in the WHERE clause of a SQL query to filter data based on conditions. For example, you could use a CASE statement to filter data based on a range of values.

Sorting data: You can use CASE statements in the ORDER BY clause of a SQL query to sort data based on conditions. For example, you could use a CASE statement to sort data by a calculated field.

Data standardization using SQL CASE statements

One common use of SQL CASE statements is for data standardization. In many cases, data in a database may not be standardized or consistent, which can make it difficult to analyze or work with. Using CASE statements to standardize data can help to ensure that it is consistent and uniform, making it easier to analyze and work with.

Here's an example of how to use a CASE statement to standardize data:

Here's an example of how you can create a table in T-SQL and populate it with sample data:

CREATE TABLE Employee (
   Name VARCHAR(50) NOT NULL,
   Age INT NOT NULL,
   Dept VARCHAR(4) NOT NULL,
   Gender VARCHAR(2) NOT NULL,
   PRIMARY KEY (Name)
);

INSERT INTO Employee (Name, Age, Dept, Gender)
VALUES ('John Smith', 35, 'Acct', 'M'),
       ('Jane Doe', 27, 'HR', 'F'),
       ('Mark Johnson', 42, 'Mktg', 'M'),
       ('Sarah Lee', 29, 'Ops', 'F'),
       ('Alex Kim', 25, 'Acct', 'M'),
       ('Lena Chen', 31, 'Mktg', 'F'),
       ('Sam Lee', 45, 'Ops', 'M'),
       ('Taylor Smith', 24, 'HR', 'F'),
       ('Jamie Lee', 38, 'Ops', 'NB'),
       ('Avery Chen', 33, 'Mktg', 'NB');

To convert the department abbreviations in the "Dept" column to their spelled out long form, we can use a T-SQL CASE statement along with the UPDATE statement. Here's an example query to achieve this:

UPDATE Employee
SET Dept = CASE 
              WHEN Dept = 'Acct' THEN 'Accounting'
              WHEN Dept = 'HR' THEN 'Human Resources'
              WHEN Dept = 'Mktg' THEN 'Marketing'
              WHEN Dept = 'Ops' THEN 'Operations'
              ELSE Dept -- leave any other values as they are
           END;

Searched CASE Statement In SQL (Structured Query Language)

Here's an example of a T-SQL Searched CASE Statement to categorize employees based on their age using multiple conditions.

SELECT Name, Age, Gender, 
       CASE 
           WHEN Age < 25 THEN 'Under 25'
           WHEN Age >= 25 AND Age < 35 THEN '25-34'
           WHEN Age >= 35 AND Age < 45 THEN '35-44'
           WHEN Age >= 45 THEN '45 and over'
           ELSE 'Unknown'
       END AS Age_Category
FROM Employee;

The Searched CASE Expression

In SQL, a "Searched CASE Expression" and a "Searched CASE Statement" both perform conditional logic based on the data in the query, but they are used in different contexts and have slightly different syntax.

The following statement shows a "Searched CASE Expression"

The searched CASE expression evaluates the Boolean expression in each WHEN clause in the specified order and returns the resultThis will add a new column "AgeGroup" in the result set that is based on the value in the "Age" column of the "Employee" table.

DECLARE @age INT
SET @age = 18

SELECT Name, Age
FROM Employee
WHERE 
    CASE
        WHEN @age >= 18 THEN 
            CASE
                WHEN Age >= 18 THEN 1
                ELSE 0
            END
        ELSE 
            CASE
                WHEN Age < 18 THEN 1
                ELSE 0
            END
    END = 1

In this SELECT statement example, the CASE statement categorizes employees into age groups based on their age, with the "Junior" group consisting of employees under 25, the "Mid-Level" group consisting of employees between 25 and 34, the "Senior" group consisting of employees between 35 and 44, and the "Executive" group consisting of employees 45 and older.

You cannot control the flow of executions of the statements, functions or procedures using CASE expressions You should always use an ELSE block The ELSE clause is used to categorize any employees whose age is not covered by the previous WHEN conditions We can also Order By sending or descending order.

Case Statement in SQL with Group by clause

In the following query SQL, a CASE statement can be used with the GROUP BY clause to group and aggregate data based on conditional expressions.

The following Transact SQL query Shows a CASE statement used with the GROUP BY clause:

SELECT
  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,
  COUNT(*) AS count
FROM scores
GROUP BY 
  CASE 
    WHEN score >= 90 THEN 'A'
    WHEN score >= 80 THEN 'B'
    WHEN score >= 70 THEN 'C'
    WHEN score >= 60 THEN 'D'
    ELSE 'F'
  END;

In this example, The SQL statement used a CASE statement to calculate the grade of each student based on their score. We then use the GROUP BY clause to group the data by grade, and we count the number of students in each grade.

The CASE statement is used twice in the query - once in the SELECT clause to calculate the grade of each student, and again in the GROUP BY clause to group the data by grade. By using the same CASE statement in both clauses, we ensure that the grouping is based on the same criteria as the calculation.

Using a CASE statement with the GROUP BY clause allows you to group and aggregate data based on conditional expressions, which can be useful in a variety of scenarios. For example, you might use this approach to group data by age range, income bracket, or other criteria that depend on conditional expressions.

The CASE statement and comparison operator

Here's an example of a more complex CASE statement with comparison operators, using the Employee table:

SELECT Name, Age, Gender,
   CASE
      WHEN Dept = 'Acct' AND Age < 30 THEN 'Junior Accountant'
      WHEN Dept = 'Acct' AND Age >= 30 THEN 'Senior Accountant'
      WHEN Dept = 'HR' AND Gender = 'F' THEN 'Female HR Employee'
      WHEN Dept = 'HR' AND Gender = 'M' THEN 'Male HR Employee'
      WHEN Dept = 'Mktg' AND Age < 35 THEN 'Junior Marketing Specialist'
      WHEN Dept = 'Mktg' AND Age >= 35 THEN 'Senior Marketing Specialist'
      WHEN Dept = 'Ops' AND Gender = 'NB' THEN 'Non-binary Operations Staff'
      ELSE 'Other'
   END AS Job_Title
FROM Employee;

In this example, the CASE statement categorizes employees based on their department, age, and gender, using comparison operators to make the distinctions. The WHEN conditions use logical operators to combine multiple criteria for categorization. The ELSE clause provides a default category for any employees whose attributes are not covered by the previous WHEN conditions.

CASE statement in SQL and aggregate functions

Here's an example of a CASE statement with an aggregate function using the Employee table:

SELECT Dept,
    COUNT(*) AS Total_Employees,
    SUM(CASE WHEN Gender = 'M' THEN 1 ELSE 0 END) AS Male_Employees,
    SUM(CASE WHEN Gender = 'F' THEN 1 ELSE 0 END) AS Female_Employees,
    SUM(CASE WHEN Gender = 'NB' THEN 1 ELSE 0 END) AS Nonbinary_Employees
FROM Employee
GROUP BY Dept;

In this example, the CASE statement categorizes employees based on their department, age, and gender, using comparison operators to make the distinctions. The WHEN conditions use logical operators to combine multiple criteria for categorization. The ELSE clause provides a default category for any employees whose attributes are not covered by the previous WHEN conditions.

CASE statement in SQL and aggregate functions

Here's an example of a CASE statement with an aggregate function using the Employee table:

SELECT Dept,
    COUNT(*) AS Total_Employees,
    SUM(CASE WHEN Gender = 'M' THEN 1 ELSE 0 END) AS Male_Employees,
    SUM(CASE WHEN Gender = 'F' THEN 1 ELSE 0 END) AS Female_Employees,
    SUM(CASE WHEN Gender = 'NB' THEN 1 ELSE 0 END) AS Nonbinary_Employees
FROM Employee
GROUP BY Dept;

In this example, the CASE statements use the conditional WHEN clause to count employees by gender. The SUM function counts the number of employees that meet the WHEN condition and adds them up to get the total number of male, female, and nonbinary employees. The COUNT function returns the total number of employees in each department. The GROUP BY clause groups the results by department, so that the aggregate functions operate on each department separately.

The IIF function

The IIF function and the CASE statement are both conditional expressions that allow you to execute different code paths based on a condition. The difference between them is mostly in their syntax and usage.

The IIF function is a shorthand version of the CASE statement that has a simpler syntax. It takes three arguments: a Boolean expression that evaluates to either true or false, a value to return if the expression is true, and a value to return if the expression is false. Here is an example of using the IIF function:

SELECT IIF(Gender = 'M', 'Male', 'Female') AS GenderText
FROM Employee

This code will return a column called GenderText, which will have the value 'Male' if the Gender column is 'M', and 'Female' otherwise.

The CASE statement is a more flexible conditional expression that can handle more complex conditions and multiple code paths. It takes multiple WHEN clauses followed by a THEN expression, and ends with an ELSE expression to handle any remaining cases. Here is an example of using the CASE statement:

SELECT Name,
    CASE
        WHEN Age < 30 THEN 'Young'
        WHEN Age >= 30 AND Age < 40 THEN 'Middle-aged'
        ELSE 'Old'
    END AS AgeGroup
FROM Employee

This code will return a column called AgeGroup, which will have the value 'Young' if the Age column is less than 30, 'Middle-aged' if the Age column is between 30 and 40, and 'Old' otherwise.

You can use either the IIF function or the CASE statement depending on your needs. If you have a simple condition and only need to return one of two values, the IIF function can be a more concise way to write your code. If you have more complex conditions or multiple code paths, the CASE statement is more appropriate.

Using CASE statements with the ORDER BY clause

SQL query uses ORDER BY clause to sort data in ascending order. Use CASE statements as well as order by clause. Suppose in product tables we retrieve productnames or price lists. List prices. The results are sorted in a logical order: In the query there are two Query CASE Scripts. The below query outputs are used in a way that allows you to confirm whether the sorting is ascending or descending. In another way Suppose the data is sorted by the condition below. We are checking the order in which the data sorting occurs by clause ORDER BY statement.

Additional Resources

End Note

If you would like to quit finding the case corresponding values, dealing with Null values in case statements and wrangling simple CASE expression compares, call me for 30 minutes or more of consulting.--Mike B