Simple CASE Expression

The simple CASE form compares an expression to a set of values for equality. Simple CASE is useful when comparing a single expression against multiple possible values.

Searched CASE Expression

The searched CASE form evaluates a list of conditions and returns a result for the first condition that is true. Searched CASE is useful when evaluating different expressions with different operators in each WHEN clause.

CASE Evaluation Order

CASE evaluates conditions in order and returns the result for the first matching condition.

Important: If multiple conditions are true, only the result for the first true condition is returned. In the example below, a video with 15000 views matches the first condition, so 'Highly Popular' is returned.

CASE in SELECT Clause

The most common use of CASE is in the SELECT clause to transform output values.

CASE in WHERE Clause

You can use CASE in a WHERE clause for complex filtering logic.

Note: While this works, complex conditions in WHERE clauses are often better expressed using normal Boolean logic with AND/OR operators.

CASE in ORDER BY Clause

CASE is powerful in ORDER BY clauses for custom sorting logic.

CASE in GROUP BY Clause

You can use CASE to create dynamic categories for grouping.

Conditional Counting

Use CASE with COUNT to count rows that meet specific conditions.

Note: We use COUNT(CASE WHEN...) rather than SUM(CASE WHEN...) because COUNT ignores NULLs, which is what we want for rows that don't match our condition.

Conditional Summing

Use CASE with SUM to calculate totals based on conditions.

Pivot Tables with CASE

Create pivot tables by combining CASE with aggregate functions.

Example 1: Dynamic Date Formatting

This example shows how to format dates differently based on how recent they are:

Example 2: User Engagement Classification

This example creates an engagement score and classification for users:

Example 3: Complex Performance Analysis

This example uses CASE expressions for detailed performance analysis:

1. Maintain Consistent Return Types

All result expressions in a CASE statement should have compatible data types. The SQL engine implicitly converts them to a common type, but explicit control is better for readability and preventing unexpected conversions.

2. Include an ELSE Clause

Always include an ELSE clause to handle unexpected cases. Without ELSE, unmatched conditions return NULL, which may not be your intended default behavior.

3. Order Conditions Appropriately

Remember that CASE evaluates conditions in order and returns the result for the first matching condition. Put more specific conditions before more general ones.

4. Keep CASE Expressions Simple

For very complex logic, consider using views or CTEs (Common Table Expressions) to break down the logic into more manageable pieces rather than creating extremely complex CASE expressions.