CASE

The CASE expression is SQL's way of expressing conditional logic. Because it is an expression rather than a statement, you can place it anywhere a value is expected—inside SELECT, WHERE, GROUP BY, ORDER BY, and aggregate functions.

General CASE expression

The general form evaluates each WHEN condition as a Boolean and returns the first matching result:

CASE
  WHEN condition_1 THEN result_1
  WHEN condition_2 THEN result_2
  ...
  ELSE else_result
END

For example, classifying films by length:

SELECT
  title,
  length,
  CASE
    WHEN length > 0   AND length <= 50  THEN 'Short'
    WHEN length > 50  AND length <= 120 THEN 'Medium'
    WHEN length > 120                   THEN 'Long'
  END AS duration
FROM film
ORDER BY title;

Output (first few rows):

        title         | length | duration
----------------------+--------+----------
 Academy Dinosaur     |     86 | Medium
 Ace Goldfinger       |     48 | Short
 African Egg          |    130 | Long

Simple CASE expression

The simple form compares one expression against several values, similar to a switch statement:

CASE expression
  WHEN value_1 THEN result_1
  WHEN value_2 THEN result_2
  ...
  ELSE else_result
END

For example, expanding MPAA rating codes into descriptions:

SELECT
  title,
  rating,
  CASE rating
    WHEN 'G'     THEN 'General Audiences'
    WHEN 'PG'    THEN 'Parental Guidance Suggested'
    WHEN 'PG-13' THEN 'Parents Strongly Cautioned'
    WHEN 'R'     THEN 'Restricted'
    WHEN 'NC-17' THEN 'Adults Only'
  END AS rating_description
FROM film
ORDER BY title;

CASE with aggregate functions

A common pattern is to use CASE inside SUM or COUNT to pivot row data into columns:

SELECT
  SUM(CASE WHEN rental_rate = 0.99 THEN 1 ELSE 0 END) AS "Economy",
  SUM(CASE WHEN rental_rate = 2.99 THEN 1 ELSE 0 END) AS "Mass",
  SUM(CASE WHEN rental_rate = 4.99 THEN 1 ELSE 0 END) AS "Premium"
FROM film;

Output:

 Economy | Mass | Premium
---------+------+---------
     341 |  323 |     336

Practical tips

• Always provide an ELSE clause to handle unexpected values; without it, unmatched rows return NULL.
• Conditions are evaluated top to bottom—put the most selective or common cases first for readability.
• Use CASE in ORDER BY to implement custom sort orders without changing the data model.
• The simple form (CASE expression WHEN value...) only supports equality—use the general form for range checks, IS NULL, and other conditions.