1. sql
  2. /references
  3. /case

CASE Expression in SQL


The CASE statement in SQL allows you to specify a series of conditions and return a different result depending on which condition is true. Here's the basic syntax:

  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ELSE result_default


Here's an example of how you might use the CASE statement in a SELECT statement:

SELECT product_name, price,
    WHEN price > 100 THEN 'expensive'
    WHEN price > 50 THEN 'moderate'
    ELSE 'cheap'
  END AS price_category
FROM products;

This would select the product name, price, and a calculated column called price_category, which would be 'expensive' for products with a price greater than 100, 'moderate' for products with a price greater than 50, and 'cheap' for all other products.

Best Practices

There are several best practices to keep in mind when using the CASE expression in SQL:

  • Use the simplest expression that can accomplish the task: The CASE expression should be as simple as possible, as it can become difficult to understand and maintain if it becomes too complex.

  • Avoid using multiple expressions in the same CASE statement: You can use multiple conditions in the same CASE statement but it is best to use separate CASE statements for readability and maintainability.

  • Use the ELSE clause: It's a good practice to use the ELSE clause to return a value when none of the conditions in the CASE statement are met.

  • Use parentheses to group conditions: Parentheses can help to clearly define the conditions that are being evaluated in a CASE statement and make the statement more readable.

  • Test the conditions in order: Make sure that the most selective conditions are tested first in the CASE statement to improve performance.

  • Avoid using case statements in SELECT queries for filtering or join conditions: Since it executes each case statement for each row, it could decrease performance. Consider using a WHERE clause for filtering or joining on a specific value.

  • A CASE statement in SQL Server evaluates an expression and returns a value based on the defined conditions.

  • If none of the conditions are satisfied, we can use an optional ELSE statement to return the default value.

  • The SQL CASE Statement consists of at least one pair of WHEN and THEN statements.

  • Every CASE statement must end with the END keyword.

  • The ELSE argument is optional.

  • CASE can be used in any statement or clause that allows a valid expression.