SQL Practice Online

How to Use CASE statement in SQL: Explained with Examples

By Cristian G. Guasch • Updated: 06/28/23 • 19 min read

When working with SQL, one might often need to run complex queries that involve multiple conditional statements. This is where the SQL CASE expression comes into play. Essentially a versatile and powerful tool, the CASE expression enables users to perform conditional logic within SQL queries, making it tremendously helpful for dealing with diverse data manipulation scenarios.

Chat with SQL Databases using AI

AskYourDatabase allows you to chat with your SQL & NoSQL databases to gain insights, visualize data, design table schemas, and data anlysis. Compatible with MySQL, PostgreSQL, MongoDB, and SQL Server.

Start Free Trial

The SQL CASE expression operates similar to a switch statement found in programming languages like C# or Java, allowing users to execute specific actions depending on predefined conditions. By utilizing this feature, users can gain improved control over how data is presented, analyzed, and manipulated within SQL, ultimately enhancing the overall efficiency and readability of database queries.

Through mastering the use of SQL CASE expressions, users can effectively streamline their database operations, optimize query performance, and achieve more accurate results. Whether it’s conditional aggregation or creating dynamic pivot tables, the power of CASE expressions is undeniable when it comes to handling complex data manipulation tasks.

Understanding SQL Case Statements

SQL case statements are a versatile and powerful tool in the database programmer’s arsenal. They provide a method for performing conditional expressions within SQL queries and stored procedures. With case statements, users can evaluate conditions and return a value or perform different actions based on the result of the evaluation.

A common use of case statements is to return a specific value depending on a column’s value in the result set. Consider the following scenarios:

  • Display user status based on their account age.
  • Find customers’ geographic regions based on their address data.
  • Calculate discounts or sales taxes based on product categories or pricing tiers.

These scenarios require conditional logic, which can be easily implemented using case statements. The two main variants of SQL case statements are the simple case and the searched case. Let’s explore each of these in more detail.

Simple Case Statements

A simple case statement evaluates a single expression against multiple conditions and returns a matching value. Here’s the general syntax for a simple case statement:

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

With this syntax, if the expression matches value1, the result would be result1. If expression matches value2, the result would be result2, and so on. If there’s no match, the default_result is returned.

Searched Case Statements

Searched case statements, on the other hand, evaluate multiple Boolean conditions and return a matching result. Here’s the general syntax for a searched case statement:

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

With this syntax, if condition1 is true, the result would be result1. If condition2 is true, the result would be result2, and so on. If there’s no condition met, the default_result is returned.

To sum up, understanding and utilizing SQL case statements are crucial for effectively handling conditional logic within SQL queries. With their ability to evaluate single expressions and multiple conditions, simple case and searched case statements offer flexibility and power for various scenarios. By mastering these concepts, users can streamline their SQL code and optimize their database-related tasks.

Types of Case Expressions

Diving into the world of SQL case expressions, one discovers that there are two main types: the SIMPLE case expression and the SEARCHED case expression. Each has its unique function and application in SQL queries, but both play a crucial role in transforming and manipulating data.

SIMPLE Case Expression

The SIMPLE case expression can be thought of as a more sophisticated version of the traditional IF-THEN-ELSE logic. When working with data in SQL, it’s often necessary to evaluate multiple conditions and return a value based on the first condition that is met. The SIMPLE case expression does precisely that. Here’s the general syntax for a SIMPLE case expression:

CASE input_expression
    WHEN expression1 THEN result1
    WHEN expression2 THEN result2
    ...
    ELSE result_n
END

Some key features of the SIMPLE case expression include:

  • Comparing an input_expression to a specified set of expressions (expression1, expression2, etc.).
  • Returning the corresponding result (result1, result2, etc.) when a match is found.
  • Using an ELSE clause for situations where no match occurs, providing a default value (result_n).
  • Ensuring a clean, readable syntax that improves code clarity and reduces the likelihood of errors.

SEARCHED Case Expression

When more complex conditions are needed or when multiple columns must be evaluated, the SEARCHED case expression comes into play. It offers greater flexibility than the SIMPLE case expression by allowing for more intricate boolean expressions. The general syntax for a SEARCHED case expression is as follows:

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

A few highlights of the SEARCHED case expression include:

  • Evaluating a set of conditions (condition1, condition2, etc.) that can involve multiple columns and boolean operators.
  • Returning the corresponding result (result1, result2, etc.) when a condition evaluates to true.
  • Using an ELSE clause to provide a default value (result_n) when no conditions are met.
  • Adding versatility to SQL case statements by handling more intricate scenarios and multi-column evaluations.

To sum up, both SIMPLE and SEARCHED case expressions offer powerful tools in manipulating and transforming data when using SQL case. The SIMPLE case is ideal for situations where an input expression needs to be compared against a list of values, while the SEARCHED case provides increased flexibility for complex conditions and multi-column evaluations. By mastering these case expressions, one can unlock new possibilities in data retrieval and transformation.

Chat with SQL Databases using AI

AskYourDatabase allows you to chat with your SQL & NoSQL databases to gain insights, visualize data, design table schemas, and data anlysis. Compatible with MySQL, PostgreSQL, MongoDB, and SQL Server.

Start Free Trial

Simple Case Syntax and Examples

When working with SQL case statements, it’s essential to understand the basic syntax and how to construct simple examples. Case statements in SQL are versatile tools used to perform conditional logic on data, offering a clean and easy-to-read way to work with data. In this section, several examples of simple case syntax will be demonstrated, assisting in building a foundational understanding of how to leverage this powerful SQL construct.

Simple case syntax can be broken down into the following key components:

  • The CASE keyword: this initiates the case expression.
  • Column or expression: this is the data upon which the case statement operates.
  • The WHEN keyword: defines a condition to test against the column or expression.
  • The THEN keyword: specifies the result to return if the condition is met.
  • The ELSE keyword (optional): provides a default result if no conditions are met, is optional but good practice to include.
  • The END keyword: completes the case expression.

Here’s a simple example using the CASE statement. Imagine a table named Employee with columns Age, Name, and EmployeeStatus. To create an analysis that determines employee categories based on age, the SQL code might look like this:

SELECT Name, Age, EmployeeStatus,
       CASE
            WHEN Age < 30 THEN 'Young'
            WHEN Age >= 30 AND Age < 55 THEN 'Adult'
            ELSE 'Senior'
       END AS AgeCategory
FROM Employee;

In this example, the CASE statement is used to assign an employee to one of three age categories: Young, Adult, or Senior. The statement evaluates each row’s Age column value and, depending on the range it falls into, assigns the appropriate category.

Another example involves a table named Orders with columns OrderID, Quantity, and TotalPrice. To apply a discount based on the quantity purchased, use this SQL code:

SELECT OrderID, Quantity, TotalPrice,
       CASE
            WHEN Quantity >= 10 THEN TotalPrice * 0.9
            ELSE TotalPrice
       END AS DiscountedPrice
FROM Orders;

In this case, order quantities of 10 or more receive a 10% discount on the TotalPrice. By utilizing the SQL case statement, this discount is efficiently applied to the relevant rows.

In summary, the SQL case statement enables users to flexibly handle conditional logic when querying data in a simple, readable format. Understanding the basic syntax and working with examples helps build a strong foundation in effectively implementing case statements within SQL projects.

Searched Case Syntax and Examples

Utilizing SQL case expressions allows users to perform conditional operations within their SQL queries. The Searched Case expression, in particular, is a versatile tool that arises when a simple case expression isn’t adequate. This section delves into the Searched Case syntax and showcases several practical examples to help users utilize this functionality.

The general syntax of a Searched Case expression is as follows:

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

Users must keep in mind that conditions are evaluated sequentially – the system stops once it encounters the first condition that evaluates to true. If none of the conditions are met, the result specified in the ELSE clause is returned. The ELSE clause, however, is optional – when omitted, the expression returns NULL if no conditions are met.

Here’s a sample query utilizing Searched Case with the ELSE clause:

SELECT ProductID, ProductName,
  CASE
    WHEN Price < 10 THEN 'Cheap'
    WHEN Price >= 10 AND Price < 20 THEN 'Moderate'
    ELSE 'Expensive'
  END AS PriceRange
FROM Products;

In this example, the query retrieves products and categorizes their PriceRange as ‘Cheap’, ‘Moderate’, or ‘Expensive’. The Searched Case evaluates each product’s price and assigns the respective category to it.

Searched Case expressions can also be nested, allowing users to implement more complex conditional logic. Here’s an example of nested Searched Case:

SELECT CustomerID, Country,
  CASE
    WHEN Country IN ('USA', 'Canada') THEN 'North America'
    WHEN Country IN ('UK', 'France', 'Germany') THEN 'Europe'
    ELSE 'Other'
  END AS Continent
FROM Customers;

This query retrieves customers and categorizes them by the Continent they reside in, using a nested Searched Case expression which includes sets of countries to map to specific continents.

In summary, Searched Case expressions provide a powerful way to incorporate conditional logic into SQL queries by evaluating conditions sequentially and assigning results accordingly. Many developers find them particularly valuable when simple case expressions are insufficient. With practical examples and a keen understanding of the syntax, users can easily apply Searched Case expressions to make their queries more dynamic and adapt to changing conditions.

Nesting Case Expressions

When working with SQL case statements, sometimes nesting case expressions becomes necessary for more complex query conditions. In this section, we’ll discuss how to nest SQL case expressions properly and explore some examples to help visualize how they work in practice.

When nesting case expressions, it’s essential to remember that additional case statements should be placed inside the existing case statement to achieve the desired outcome. Each inner case expression needs its own WHEN and THEN clauses, and every nested case must end with an END keyword.

Let’s take a look at an example of using nested case expressions in SQL:

SELECT 
  customer_name,
  CASE 
    WHEN age < 18 THEN 'Underage'
    ELSE
      CASE 
        WHEN age BETWEEN 18 AND 64 THEN 'Adult'
        ELSE 'Senior'
      END
  END as age_group
FROM customers;

In this example, we’re categorizing customers into three age groups – Underage, Adult, and Senior. The outer case expression checks if a customer is underage, while the inner case expression handles categorizing adult and senior customers.

Using nested case expressions can also help to handle NULL values. For instance:

SELECT 
  product_name,
  CASE 
    WHEN price IS NULL THEN 'N/A'
    ELSE
      CASE
        WHEN price < 50 THEN 'Budget'
        WHEN price BETWEEN 50 AND 100 THEN 'Mid-range'
        ELSE 'High-end'
      END
  END as price_range
FROM products;

In this example, the outer case expression deals with null prices, and the inner one assigns price ranges for the products with known prices.

Here are some general tips for using nested case expressions:

  • Keep nested case expressions as simple as possible.
  • Make sure every case has its own WHEN, THEN, and END keywords.
  • Use nested case expressions to handle NULL values appropriately.
  • Test your nested case statements thoroughly to avoid logical errors in your SQL query.

In summary, mastering nested case expressions in SQL opens up new possibilities for handling complex query conditions and improving the accuracy of your data retrieval. Keep practicing with various scenarios and become proficient in using nested case statements in your day-to-day work with SQL. Just remember to maintain simplicity and thoroughly test your nested case expressions.

Using Case with Aggregate Functions

Using case with aggregate functions in SQL can be a powerful way to manipulate and analyze data. By combining case statements with aggregate functions, one can derive valuable insights from their database. This section will cover the basics of using the SQL case expression with aggregate functions like COUNT, SUM, AVG, MIN, and MAX.

To implement the SQL case statement within an aggregate function, one has to include the case expression within the function call itself. The following example demonstrates how to do this:

SELECT
  COUNT(CASE WHEN column_name = 'value' THEN 1 END) AS count_of_value
FROM
  table_name;

Here is a breakdown of using some commonly used aggregate functions with case expressions:

  • COUNT: This function can be used to count the number of rows that match specific conditions using case: SELECT COUNT(CASE WHEN column_name = 'value1' THEN 1 END) AS count_of_value1, COUNT(CASE WHEN column_name = 'value2' THEN 1 END) AS count_of_value2 FROM table_name;
  • SUM: The sum function calculates the total of a numeric column based on certain conditions. An example use of case with SUM is: SELECT SUM(CASE WHEN column_name = 'value1' THEN amount_column ELSE 0 END) AS sum_of_value1_amount, SUM(CASE WHEN column_name = 'value2' THEN amount_column ELSE 0 END) AS sum_of_value2_amount FROM table_name;
  • AVG: To calculate the average of a numeric column subject to specific conditions, the SQL case expression can be employed: SELECT AVG(CASE WHEN column_name = 'value1' THEN numeric_column END) AS avg_for_value1, AVG(CASE WHEN column_name = 'value2' THEN numeric_column END) AS avg_for_value2 FROM table_name;
  • MIN and MAX: The minimum and maximum values in a column can also be determined using case: SELECT MIN(CASE WHEN column_name = 'value1' THEN numeric_column END) AS min_for_value1, MAX(CASE WHEN column_name = 'value1' THEN numeric_column END) AS max_for_value1 FROM table_name;

In summary, the versatility of the SQL case expression with aggregate functions allows for more efficient data analysis and manipulation. By understanding the basics of this technique, users can effectively leverage their databases to gain valuable insights.

Case in SQL Order By Clause

Using CASE in SQL is a powerful technique to manipulate and control data dynamically. It’s especially useful when working with the ORDER BY clause, allowing for greater flexibility in the sorting of results. Let’s dive into how to use the SQL CASE statement in the ORDER BY clause.

To start, the SQL CASE expression lets users construct conditional statements that allow the result set to change based on specified conditions. For example, the structure of a simple CASE expression is as follows:

CASE input_expression
    WHEN when_expression1 THEN result_expression1
    WHEN when_expression2 THEN result_expression2
    ...
    ELSE else_result_expression
END

To put this into practice within the ORDER BY clause, consider the following example. A user wants to sort a list of products by their categories: Electronics should come first, followed by Clothing, but all other categories can be sorted alphabetically. Here’s how this can be achieved using CASE in the ORDER BY clause:

SELECT product_name, category
FROM products
ORDER BY
    CASE
        WHEN category = 'Electronics' THEN 1
        WHEN category = 'Clothing' THEN 2
        ELSE 3
    END,
    category;

The CASE expression assigns different numeric values to the categories we want to sort, and then orders them accordingly. After sorting by the numeric values, the query sorts alphabetically for the other categories.

Another application of CASE in the ORDER BY clause comes in handy when sorting results based on multiple columns with varying sorting criteria. For instance, a user may want to sort employee data by salary, but in the event of a tie, sort by the employees’ hire dates. Here’s an example of how to accomplish this:

SELECT employee_name, salary, hire_date
FROM employees
ORDER BY
    salary DESC,
    CASE
        WHEN salary = (SELECT MIN(salary) FROM employees) THEN hire_date DESC
        ELSE hire_date ASC
    END;

In this example, employees with the lowest salary will be sorted in descending order by hire date, while all other employees will be sorted in ascending order by hire date.

In conclusion, using CASE expressions within the ORDER BY clause can provide valuable enhancements to SQL queries. By incorporating conditional logic, users can dynamically adjust the sorting order of data, ensuring that important data can be prioritized and displayed according to specific needs.

Handling NULL Values with Case

When working with SQL case statements, handling NULL values is essential for producing accurate results. Since NULL values indicate missing or unknown information, an SQL query must account for them to provide meaningful data. This section explains various techniques to handle NULL values with case statements and prevent any potential issues.

The first technique involves using COALESCE or NULLIF functions in conjunction with case statements. These functions allow developers to replace NULL values with default values or transform non-NULL values into NULLs. Here’s an example of using COALESCE:

SELECT employee_id, COALESCE(salary_bonus, 0) AS bonus
FROM employees

In this example, if salary_bonus is NULL, it’s replaced by 0 to ensure an accurate calculation of the bonus.

Another approach is using CASE…WHEN…THEN…ELSE…END in SQL queries to manage NULL values directly. Based on certain conditions, this structure processes NULL values and returns matching results. For instance:

SELECT employee_id,
       CASE
           WHEN salary_bonus IS NULL THEN 0
           ELSE salary_bonus
       END AS bonus
FROM employees

This query checks if the salary_bonus column contains NULL values and, if so, returns 0 as the bonus value.

Sometimes, it may be necessary to include or exclude NULL values from query results depending on specific requirements. With case statements, this becomes easy:

  • Including NULL values: To ensure a query includes NULL values when calculating results, use an appropriate condition: SELECT employee_id, position FROM employees WHERE salary_level >= 1000 OR salary_level IS NULL In this example, the query returns all employees with a salary_level equal to or greater than 1000, or where the salary_level is NULL.
  • Excluding NULL values: To exclude NULL values from query results, add an additional condition: SELECT employee_id, position FROM employees WHERE salary_level >= 1000 AND salary_level IS NOT NULL Here, the query only retrieves employees with a salary_level equal to or greater than 1000, completely ignoring records with NULL values in the salary_level.

By incorporating these techniques for handling NULL values with SQL case, developers improve the accuracy and efficiency of their queries while maintaining the integrity of their data.

Common Mistakes to Avoid

When working with SQL CASE statements, it’s crucial to avoid some common errors that can impact query performance or yield incorrect results. By recognizing these pitfalls, developers can write more accurate, efficient, and streamlined SQL queries.

One typical mistake is over-nesting the case statements. Excessive nesting can make queries difficult to read, maintain, and debug, while also increasing the risk of performance issues. Although SQL allows for a high level of nesting, it’s best to keep the code organized, streamlined, and use other optimization techniques to maintain performance.

Another common issue is forgetting to use the ELSE clause in a case statement. While it’s not always required, leaving out the ELSE clause can lead to unexpected NULL values in the query results. By including a default outcome with the ELSE clause, developers can avoid potential confusion and ensure a more reliable output.

  • E.g., instead of writing: CASE WHEN a > b THEN 'greater' END
  • It’s recommended to add an ELSE clause: CASE WHEN a > b THEN 'greater' ELSE 'not greater' END

Misusing the Searched CASE form is another common error. The searched CASE operates in a slightly different manner than the simple CASE, checking each search condition in the WHEN clause sequentially and returning the corresponding result for the first true condition. Mixing up these two forms can lead to invalid expressions or incorrect results.

Moreover, watch out for data type inconsistencies when working with CASE statements. SQL usually attempts to convert differing data types to the one with the highest precedence automatically, but this conversion can cause errors or lead to unexpected output. Ensure the data types matched, or explicitly cast them to the desired type for consistency.

Lastly, remember that the SQL CASE statement in some database management systems (DBMS) is case insensitive. Though this may not cause issues most of the time, there could be rare instances where case insensitivity affects query results. Being aware of this factor can help in maintaining uniformity across various DBMS.

In conclusion, by staying vigilant and avoiding these common SQL case mistakes, developers can create queries that are more reliable, performant, and easily maintainable.

Conclusion

Understanding how to use SQL CASE is essential for any database professional. It offers flexibility in querying and processing data, allowing for a more refined and tailored approach to managing information. The power of CASE lies in its ability to implement conditional logic directly into SQL queries, making it an incredibly versatile tool for a wide range of applications.

Efficiency is also a key benefit when utilizing SQL CASE. With its ability to handle multiple conditions in a single statement, one can eliminate the need for multiple separate queries. This streamlined approach can lead to significant time savings, especially in complex database environments.

It’s important to note that various databases may have differing syntax for SQL CASE. However, the core functionality remains consistent across platforms. Whether it’s in an Oracle, MySQL, or SQL Server database, the overall structure will involve:

  • Evaluating a series of conditions with the CASE statement
  • Specifying the corresponding actions to be taken with the WHEN and THEN clauses
  • Setting a default action in case no conditions are met, using the ELSE clause
  • Finalizing the statement with the END keyword

An example of a SQL CASE expression:

SELECT ProductName, Category,
  CASE
    WHEN Category = 'Electronics' THEN 'High-Tech'
    WHEN Category = 'Furniture' THEN 'Home'
    ELSE 'Others'
  END AS ProductCategory
FROM Products;

To sum up, mastering the use of SQL CASE can enhance one’s database management skills and greatly improve efficiency when working with data. With this knowledge, professionals can create more powerful and versatile SQL queries, allowing them to handle condition-based scenarios and complex data-processing tasks with confidence.

Chat with SQL Databases using AI

AskYourDatabase allows you to chat with your SQL & NoSQL databases to gain insights, visualize data, design table schemas, and data anlysis. Compatible with MySQL, PostgreSQL, MongoDB, and SQL Server.

Start Free Trial

Related articles