CASE WHEN

SQL is a declarative language, meaning you tell it what you want, not how to get it. It doesn’t have traditional if/else loops like JavaScript or Python.

However, it does provide the CASE expression, which allows you to implement complex conditional logic directly inside your SELECT statements.

The Syntax

A CASE expression evaluates a list of conditions and returns one of multiple possible result expressions.

SELECT 
  name,
  price,
  CASE 
    WHEN price > 1000 THEN 'Expensive'
    WHEN price > 100 THEN 'Moderate'
    ELSE 'Cheap'
  END AS price_tier
FROM products;

Let’s break it down:

• It starts with CASE and ends with END.
• You provide multiple WHEN [condition] THEN [result] clauses.
• The conditions are evaluated sequentially from top to bottom. As soon as a WHEN condition is met, it returns the THEN result and stops evaluating that row.
• The ELSE clause is optional, but highly recommended as a fallback. If omitted and no conditions are met, it returns NULL.

Conditional Aggregation

CASE expressions are incredibly powerful when combined with aggregate functions. This technique, called Conditional Aggregation, allows you to pivot your data.

Imagine you want to count how many ‘Expensive’ items you have versus how many ‘Cheap’ items, but you want them in a single row side-by-side.

SELECT 
  SUM(CASE WHEN price > 1000 THEN 1 ELSE 0 END) AS expensive_count,
  SUM(CASE WHEN price <= 1000 THEN 1 ELSE 0 END) AS cheap_count
FROM products;

This acts exactly like a COUNT(WHERE...) but allows multiple different filters in the same SELECT statement!

Try using a standard CASE expression in the interactive lab to categorize our product inventory!