SQL Case Statements - Nimbus Intelligence

We have already seen how nesting aggregated functions and SQL join types work in the previous articles for the basics of SQL. Following, we will understand how case statements work in SQL, and see some examples and good practices.

What are case statements?

A case statement or function is a conditional statement used in SQL to assign values to a column based on specific conditions. It is similar to the “if-then-else” statement in other programming languages. With a case statement, you can define a set of conditions and the corresponding values that should be assigned to a specific column when those conditions are met.

The syntax for a case statement is as follows:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
...

    ELSE resultN

END

The case statement evaluates each condition in the order in which they are listed and assigns the corresponding result to the column. If none of the conditions are met, the else result is assigned to the column.

Techniques for Handling Case Statements in SQL

There are several techniques for handling case statements in SQL, including:

Simple case statement
Searched case statement
Nested case statement

Let’s discuss each of these techniques in more detail.

Simple Case Statement

A simple case statement is used when you want to compare a single expression 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

For example, consider the following SQL query:

SELECT product_name,
       CASE category_id
            WHEN 1 THEN 'Electronics'
            WHEN 2 THEN 'Clothing'
            ELSE 'Other'
       END AS category
FROM products;

This query returns the product name and category of all products in the database. If a product belongs to category 1, the category is “Electronics”. If it belongs to category 2, the category is “Clothing”. Otherwise, the category is “Other”.

Searched Case Statement

A searched case statement is used when you want to compare multiple expressions to a set of values. The syntax for a searched case statement is as follows:

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

For example, consider the following SQL query:

SELECT product_name,
       CASE
            WHEN price < 50 THEN 'Inexpensive'
            WHEN price >= 50 AND price < 100 THEN 'Moderately priced'
            ELSE 'Expensive'
       END AS price_range
FROM products;

This query returns the product name and price range of all products in the database. If a product is priced less than $50, the price range is “Inexpensive”. If it is priced between $50 and $100, the price range is “Moderately priced”. Otherwise, the price range is “Expensive”.

Nested Case Statement

A nested case statement is used when you want to create more complex conditions by combining multiple case statements. The syntax for a nested case statement is as follows:

CASE
    WHEN condition1 THEN
        CASE
            WHEN nested_condition1 THEN result1
            WHEN nested_condition2 THEN result2
            ...
            ELSE resultN
        END
    WHEN condition2 THEN
        CASE
            WHEN nested_condition1 THEN result

WHEN nested_condition2 THEN result2
...
ELSE resultN
    END
...
ELSE resultN

END

For example, consider the following SQL query:

SELECT product_name,
  CASE
    WHEN category_id = 1 THEN
      CASE
        WHEN price < 50 THEN 'Electronics - Inexpensive'
      ELSE 'Electronics - Expensive'
      END
    WHEN category_id = 2 THEN
      CASE
        WHEN price < 50 THEN 'Clothing - Inexpensive'
      ELSE 'Clothing - Expensive'
      END
    ELSE 'Other'
  END AS category_price
FROM products;

This query returns the product name and category price of all products in the database. If a product belongs to category 1 and is priced less than $50, the category price is “Electronics – Inexpensive”. While, If it belongs to category 1 and is priced 50$ or more, the category price is “Electronics – Expensive”. When it belongs to category 2 and is priced less than $50, the category price is “Clothing – Inexpensive”. Last but not least, If it belongs to category 2 and is priced 50$ or more, the category price is “Clothing – Expensive”. Otherwise, the category price is “Other”.

Best Practices for Using Case Statements

Here are some best practices for using case statements in SQL:

1. Use case statements sparingly: While case statements are powerful, overusing them can make SQL code difficult to read and maintain. Use them only when necessary.

2. Use simple case statements whenever possible: Simple case statements are easier to read and write than searched or nested case statements. Use them whenever possible.

3. Keep case statements short: Long case statements can be difficult to read and understand. Try to keep your case statements as short and simple as possible.

4. Use descriptive column aliases: When using case statements in a SELECT statement, use descriptive column aliases to make the output easier to read and understand.

5. Test your case statements: Before using a case statement in production code, test it thoroughly to ensure that it works as expected.

Real-World Scenario

Let’s consider a real-world scenario where case statements can be useful. Suppose we have a database of employees that includes information such as employee name, hire date, and salary. We want to create a report that shows the employee’s name, hire date, and whether the employee has been with the company for less than one year, between one and three years, or more than three years.

We can use a case statement to create this report as follows:

SELECT 
    employee_name,
    hire_date,
  CASE
    WHEN DATEDIFF(CURDATE(), hire_date) < 365 THEN 'Less than 1 year'
    WHEN DATEDIFF(CURDATE(), hire_date) BETWEEN 365 AND 1095 THEN '1-3 years'
    ELSE 'More than 3 years'
  END AS tenure
FROM employees;

This query returns the employee name, hire date, and tenure of all employees in the database. If an employee has been with the company for less than one year, the tenure is “Less than 1 year”. If they have been with the company between one and three years, the tenure is “1-3 years”. Otherwise, the tenure is “More than 3 years”.

Conclusion

In conclusion, case statements are a powerful tool in SQL that allows you to assign values to columns based on specific conditions. There are several techniques for handling case statements in SQL, including simple case statements, searched case statements, and nested case statements. When using case statements, it’s important to follow best practices to keep your code readable and optimized.

Auteur

Dimitris Lampriadis

Dimitris holds a Master's degree as an Urban Technologist - Geospatial Data Analyst from MaCT/UPC in Barcelona. There he discovered a passion for geospatial data analysis and Urban Analytics, but most importantly how data-driven strategies are crucial for making efficient decisions, and how to translate raw data into handy and easy-to-read insights. With his sights set on becoming an Analytics Engineer, Dimitris sought out a company that would help him grow his skills and knowledge. That's when he found Nimbus Intelligence. Nimbus provided the necessary space to combine his existing knowledge and skills, with a suite of powerful tools, like; Snowflake, Tableau, Alteryx, and dbt. He's also managed to develop valuable business skills that enable him to solve complex problems and deliver tangible results. He enjoys giving answers to modern business problems and aims to bring knowledge, to every team he is part of, that binds his understanding of spatial relationships and Analytics Engineering. He is always looking for ways to share his expertise and collaborate with others.

View all posts