SQL CASE WHEN Explained: Syntax & Examples

1 year ago
Benjamin Taylor
2 minutes

In SQL, the CASE WHEN statement is a conditional expression used to return different results based on specified conditions. The basic syntax is as follows:

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

condition1 and condition2 are the criteria to be evaluated, while result1 and result2 are the outcomes returned when their corresponding criteria are met. The ELSE clause is optional and is used to specify the default result when none of the conditions are satisfied.

The CASE WHEN statement can be nested and can return different results based on different conditions. For example:

CASE WHEN condition1 THEN result1
     WHEN condition2 THEN 
        CASE WHEN condition3 THEN result3
             ELSE result4
        END
     ELSE result2
END

When using the CASE WHEN statement, there are several points to keep in mind:

  1. The conditional expression can be a simple comparison operator or a complex logical expression.
  2. The result can be a constant, an expression, a column name, etc.
  3. The CASE WHEN statement can be used in columns, WHERE clauses, ORDER BY clauses, etc. within SELECT statements.
  4. The CASE WHEN statement evaluates conditions in the order they are written, returning the corresponding result once a condition is met without evaluating any subsequent conditions.

The CASE WHEN statement is a commonly used conditional expression in SQL, allowing different results to be returned based on different conditions, making SQL queries more flexible and targeted.

bannerAds